How do you substitue a value for #Error

[Barry]

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.

 

[Allen Browne]

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.

 

[Barry]

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.

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

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[John Spencer]

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

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.

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

Reply to group, rather than allenbrowne at mvps dot org.

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.

 

[Barry]

Thanks, that got it.

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

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.

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

Reply to group, rather than allenbrowne at mvps dot org.

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.