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. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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
|
|||
|
|||
#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 | |
|
|
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 |