#error results in unmatched records of outer join

[Guest]

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

 

[strive4peace]

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

*

 

[Guest]

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

 

[strive4peace]

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

*

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

:

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

*

 

[Guest]

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"));

 

[strive4peace]

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

*

 

[Guest]

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

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

*

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"));

 

[strive4peace]

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

*

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

:

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

*

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"));

 

[Guest]

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

 

[strive4peace]

Hi Steve,

Don't give up on UDFs -- you really need more checking than
you can do in an equation. Before you send the value to the
UDF, make sure it is not null. Then, in the UDF, put error
checking in before your calculation so you do not use string
manipulation on null values and use an error handler

ColumnName: IIF(
Isnull([expression]),
0,
ParseWI([expression])
)

I also noticed you used the first (optional) parameter of
Instr -- if it is 1, you do not need to specify that

'~~~~~~~~~~~~~~~~~~~~~~
dim mPos as integer
mPos = Instr(parametername," " )
if mPos > 0 then

'~~~~~~~~~~~~~~~~~~~~~~


ERROR HANDLER CODE:

put this at the top of your program
'~~~~~~~~~~~~~~~~~~~~~~

On Error GoTo Proc_Err

'initialize function
FunctionName = 0 'default value

'~~~~~~~~~~~~~~~~~~~~~~

put this at the end of the program

'~~~~~~~~~~~~~~~~~~~~~~

FunctionName = calculated value

Proc_Exit:
On Error Resume Next
'release object variables if any
Exit Function

Proc_Err:
MsgBox Err.Description, , _
"ERROR " & Err.Number _
& " ProcedureName"

'press F8 to step through code and debug
'remove next line after debugged
Stop: Resume
Resume Proc_Exit

'~~~~~~~~~~~~~~~~~~~~~~



Warm Regards,
Crystal
Microsoft Access MVP 2006

*
Have an awesome day

remote programming and training
strive4peace2006 at yahoo.com

*