A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Running & Setting Up Queries
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

#error results in unmatched records of outer join



 
 
Thread Tools Display Modes
  #1  
Old June 8th, 2006, 09:58 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default #error results in unmatched records of outer join

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
  #2  
Old June 8th, 2006, 10:13 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default #error results in unmatched records of outer join

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

*

Steve wrote:
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

  #3  
Old June 8th, 2006, 10:34 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default #error results in unmatched records of outer join

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

"strive4peace" "strive4peace2006 at yaho" wrote:

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

*

Steve wrote:
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


  #4  
Old June 8th, 2006, 11:27 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default #error results in unmatched records of outer join

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

*

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

"strive4peace" "strive4peace2006 at yaho" wrote:


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

*

Steve wrote:

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


  #5  
Old June 9th, 2006, 04:11 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default #error results in unmatched records of outer join

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

  #6  
Old June 9th, 2006, 06:44 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default #error results in unmatched records of outer join

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

*

Steve wrote:
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"));

  #7  
Old June 9th, 2006, 11:14 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default #error results in unmatched records of outer join

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

"strive4peace" "strive4peace2006 at yaho" wrote:

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

*

Steve wrote:
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"));


  #8  
Old June 10th, 2006, 12:54 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default #error results in unmatched records of outer join

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

*

Steve wrote:
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

"strive4peace" "strive4peace2006 at yaho" wrote:


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

*

Steve wrote:

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


  #9  
Old June 10th, 2006, 04:18 AM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default #error results in unmatched records of outer join

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
  #10  
Old June 10th, 2006, 04:24 PM posted to microsoft.public.access.queries
external usenet poster
 
Posts: n/a
Default #error results in unmatched records of outer join - Error handlercode

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

*

Steve wrote:
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

 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
outer join with multiple fields Arden Running & Setting Up Queries 2 May 24th, 2006 04:15 PM
table question Jessica General Discussion 5 September 22nd, 2005 12:41 AM
Relationship feature/bug/accident Peter Danes Using Forms 22 September 11th, 2005 11:15 PM
Deleting Outer Join Records Suzie Raboin Running & Setting Up Queries 4 May 9th, 2005 09:21 PM
urgent ! outer join not picking all records questy Running & Setting Up Queries 10 April 29th, 2005 11:29 PM


All times are GMT +1. The time now is 12:07 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.