|
| 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
July 16th, 2008, 04:32 PM
posted to microsoft.public.access.queries
|
|||
|
|||
How do you substitue a value for #Error
I'm running a query that returns #Error for some rows. I tried to run
iif(iserror([field]),1,0) but it still returns #Error. I have also tried isnull, isempty, ismissing. and iif([field]="#Error",1,0). I would appreciate any ideas. 
|
|
#2
July 16th, 2008, 04:55 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
How do you substitue a value for #Error
The only real solution is to find out what's causing the error, and deal
with that. For example, if you have: [Field1] / [Field2] that will give an error if Field2 is zero. Therefore you need: IIf([Field2]=0, Null, [Field1] / [Field2]) Similarly, if you have: DLookup("CompanyName", "tblCompany", "CompanyID = " & [CompanyID]) when CompanyID is null (e.g. in the new record row), the 3rd expression becomes just: CompanyID = which is incomplete and will give an error. To handle that, use: DLookup("CompanyName", "tblCompany", "CompanyID = " & Nz([CompanyID],0)) In essence, understand what caused the error, and fix the problem. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Barry" wrote in message ... I'm running a query that returns #Error for some rows. I tried to run iif(iserror([field]),1,0) but it still returns #Error. I have also tried isnull, isempty, ismissing. and iif([field]="#Error",1,0). I would appreciate any ideas.
|
|
#3
July 16th, 2008, 05:26 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
How do you substitue a value for #Error
I'm getting the error on a text field using this:
dt: IIf(Mid([nVision_32Bit_Input]![Actual Notes],InStr([nVision_32Bit_Input]![Actual Notes],"--32Bit--"),9)="--32Bit--","--32Bit--","Generic") When the text field includes --32bit-- it returns --32bit--. all othe entries return #error vice Generic. The text field it is searching is very large. "Allen Browne" wrote: The only real solution is to find out what's causing the error, and deal with that. For example, if you have: [Field1] / [Field2] that will give an error if Field2 is zero. Therefore you need: IIf([Field2]=0, Null, [Field1] / [Field2]) Similarly, if you have: DLookup("CompanyName", "tblCompany", "CompanyID = " & [CompanyID]) when CompanyID is null (e.g. in the new record row), the 3rd expression becomes just: CompanyID = which is incomplete and will give an error. To handle that, use: DLookup("CompanyName", "tblCompany", "CompanyID = " & Nz([CompanyID],0)) In essence, understand what caused the error, and fix the problem. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Barry" wrote in message ... I'm running a query that returns #Error for some rows. I tried to run iif(iserror([field]),1,0) but it still returns #Error. I have also tried isnull, isempty, ismissing. and iif([field]="#Error",1,0). I would appreciate any ideas.
|
|
#4
July 16th, 2008, 06:27 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
How do you substitue a value for #Error
I would wonder why you aren't using one of the following
IIF([Actual Notes] Like "*--32Bit--*","--32Bit--","Generic") OR IIF(Instr(1,[Actual Notes],"--32Bit--")0,"--32Bit--","Generic") The reason your code is generating an error is that the Instr call is returning zero if the "--32Bit--" string is not present. MID does not handle 0 for the Start argument and will generate an error for any number less than 1. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Barry wrote: I'm getting the error on a text field using this: dt: IIf(Mid([nVision_32Bit_Input]![Actual Notes],InStr([nVision_32Bit_Input]![Actual Notes],"--32Bit--"),9)="--32Bit--","--32Bit--","Generic") When the text field includes --32bit-- it returns --32bit--. all othe entries return #error vice Generic. The text field it is searching is very large. "Allen Browne" wrote: The only real solution is to find out what's causing the error, and deal with that. For example, if you have: [Field1] / [Field2] that will give an error if Field2 is zero. Therefore you need: IIf([Field2]=0, Null, [Field1] / [Field2]) Similarly, if you have: DLookup("CompanyName", "tblCompany", "CompanyID = " & [CompanyID]) when CompanyID is null (e.g. in the new record row), the 3rd expression becomes just: CompanyID = which is incomplete and will give an error. To handle that, use: DLookup("CompanyName", "tblCompany", "CompanyID = " & Nz([CompanyID],0)) In essence, understand what caused the error, and fix the problem. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Barry" wrote in message ... I'm running a query that returns #Error for some rows. I tried to run iif(iserror([field]),1,0) but it still returns #Error. I have also tried isnull, isempty, ismissing. and iif([field]="#Error",1,0). I would appreciate any ideas.
|
|
#5
July 16th, 2008, 06:42 PM
posted to microsoft.public.access.queries
|
|||
|
|||
|
How do you substitue a value for #Error
Thanks, that got it.
"John Spencer" wrote: I would wonder why you aren't using one of the following IIF([Actual Notes] Like "*--32Bit--*","--32Bit--","Generic") OR IIF(Instr(1,[Actual Notes],"--32Bit--")0,"--32Bit--","Generic") The reason your code is generating an error is that the Instr call is returning zero if the "--32Bit--" string is not present. MID does not handle 0 for the Start argument and will generate an error for any number less than 1. John Spencer Access MVP 2002-2005, 2007-2008 The Hilltop Institute University of Maryland Baltimore County Barry wrote: I'm getting the error on a text field using this: dt: IIf(Mid([nVision_32Bit_Input]![Actual Notes],InStr([nVision_32Bit_Input]![Actual Notes],"--32Bit--"),9)="--32Bit--","--32Bit--","Generic") When the text field includes --32bit-- it returns --32bit--. all othe entries return #error vice Generic. The text field it is searching is very large. "Allen Browne" wrote: The only real solution is to find out what's causing the error, and deal with that. For example, if you have: [Field1] / [Field2] that will give an error if Field2 is zero. Therefore you need: IIf([Field2]=0, Null, [Field1] / [Field2]) Similarly, if you have: DLookup("CompanyName", "tblCompany", "CompanyID = " & [CompanyID]) when CompanyID is null (e.g. in the new record row), the 3rd expression becomes just: CompanyID = which is incomplete and will give an error. To handle that, use: DLookup("CompanyName", "tblCompany", "CompanyID = " & Nz([CompanyID],0)) In essence, understand what caused the error, and fix the problem. -- Allen Browne - Microsoft MVP. Perth, Western Australia Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. "Barry" wrote in message ... I'm running a query that returns #Error for some rows. I tried to run iif(iserror([field]),1,0) but it still returns #Error. I have also tried isnull, isempty, ismissing. and iif([field]="#Error",1,0). I would appreciate any ideas.
|
| Thread Tools | |
| Display Modes | |
|
|
Linear Mode
