#error results in unmatched records of outer join

#error results in unmatched records of outer join

Post by U3Rldm » Sat, 10 Jun 2006 05:58:02


Doing a simple query where I join a master table with all records and a query
that creates a subset of data. When I look at the result set after the join,
the master table records that aren't present in the subset have #error for
that column. My environment is Access 2002 on Xp Pro with all os and office
updates current. I've been doing this for some time and this is the first
time I've seen this. Thanks in advance for you help.
Steve
 
 
 

#error results in unmatched records of outer join

Post by strive4pea » Sat, 10 Jun 2006 06:13:51

Hi Steve,

Is that column a calculation?


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*

 
 
 

#error results in unmatched records of outer join

Post by U3Rldm » Sat, 10 Jun 2006 06:34:01

Yes, it is.
InitialWI:
Val((Left([expression],Len([expression])-(Len([expression])-InStr(1,[expression]," ")+1)))/100)
 
 
 

#error results in unmatched records of outer join

Post by strive4pea » Sat, 10 Jun 2006 07:27:20

Hi Steve,

wrap field references with NZ (convert Null from nothing to
something) in case they are blank

ie:
Left
(
nz([expression]),
Len(
nz([expression])
)
-Len(
nz([expression])
)
)
...

if that doesn't work, then tell me the SQL of your query and
I will give you another method


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
 
 
 

#error results in unmatched records of outer join

Post by U3Rldm » Sun, 11 Jun 2006 00:11:02

Thanks so much for you help. I'm having a tough time getting into this
website and am not getting notifications of replies. I tried to implement
your suggestion and I'm still getting the #error in the second query. Anyway
I'm including the sql for the queries I'm having the issues with.

First query:
SELECT dbo_AC_ECONOMIC.PROPNUM,
(Val((Left(nz([Expression]),Len(nz([Expression]))-(Len(nz([Expression]))-InStr(1,nz([Expression])," ")+1)))/100)) AS [Initial WI]
FROM dbo_AC_ECONOMIC
WHERE (((dbo_AC_ECONOMIC.KEYWORD)="net") AND ((dbo_AC_ECONOMIC.SECTION)=7)
AND ((dbo_AC_ECONOMIC.QUALIFIER)=[forms]![frmsaverun].[txtqual]));
Second query:
SELECT dbo_AC_PROPERTY.PROPNUM, dbo_AC_PROPERTY.ENTITY, [WC Initial
WI].[Initial WI], dbo_AC_PROPERTY.RUN
FROM dbo_AC_PROPERTY LEFT JOIN [WC Initial WI] ON dbo_AC_PROPERTY.PROPNUM =
[WC Initial WI].PROPNUM
WHERE (((dbo_AC_PROPERTY.RUN)="y"));
 
 
 

#error results in unmatched records of outer join

Post by strive4pea » Sun, 11 Jun 2006 02:44:59

Hi Steve,

What does [Expression] represent?

surely this is not a fieldname...

Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
 
 
 

#error results in unmatched records of outer join

Post by U3Rldm » Sun, 11 Jun 2006 07:14:02

Yes, its the fieldname for a column in a table for a commercial product
called Aries. Are you thinking that's my problem?
Thanks Steve
 
 
 

#error results in unmatched records of outer join

Post by strive4pea » Sun, 11 Jun 2006 08:54:33

Hi Steve,

aah, Aries ... I thought WI might be Working Interest ...
guess it is! I have pulled data from Aries before.

I do not know if the fieldname is causing the problem --
just kinda odd to call something that name ;)

lets break apart your equation:

Val(
(
Left([expression],
Len([expression])-
(
Len([expression])-InStr(1,[expression]," ")
+1)
))/100)


what if [expression] is null?
--> Len([expression]) will be an error
--> InStr(1,[expression]," ") will be an error
--> Left([expression],#) will be an error

what if it doesn't have a space in it?

definitely, if there is no record to even get the field
from, there will be a problem...

Why not make a UDF (User-Defined function) to calculate WI?

What is an example if what [Expression] looks like? Also,
what is the name of a field in the table that will always be
filled out (so we can test if there is a record)? I will
help you with a UDF.


Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day ;)

remote programming and training
strive4peace2006 at yahoo.com

*
 
 
 

#error results in unmatched records of outer join

Post by U3Rldm » Sun, 11 Jun 2006 12:18:02

Actually the field "expression" in the ac_economics table is alway populated.
In my first query I extract the first portion of the string from
"expression" and make it numeric. That step works fine. It's when I try to
combine it back to the master table "ac_property" with an outer join that I'm
getting the #error for records that are in "ac_property" but not in
"ac_economics". I even tried the nz function in the last query where the
outer join is and still get the #error. I tried using a udf to parse out the
value and convert it but still had the same results. Thanks again for your
help.
Steve