IIF/IsError sql Combo still returning #Error

April15Hater · Nov 8, 2008

Hi,

I'm trying to get the following code to return <blank> rather than #Error in a query, and I just can't get it:

Code:

IIf(IsError(DLookUp("StateAbbreviation","tblStates","StateID = " & [AccountingStateID]))=True,"",DLookUp("StateAbbreviation","tblStates","StateID = " & [AccountingStateID])) AS AccountingState

Is my syntax flawed maybe?

Thanks,

Joe

marleymanner1 · Nov 9, 2008

Joe,

When you lookup something with the DLOOKUP() function and it doesn't return anything, I (think) the result is NULL, not an error. If that is truly the case, I think your code might need to be changed. Instead of this:

Code:

IIf(IsError(DLookUp("StateAbbreviation","tblStates",
   "StateID = " & [AccountingStateID]))=True,"",
      DLookUp("StateAbbreviation","tblStates",
   "StateID = " & [AccountingStateID])) AS AccountingState

Maybe try this:

Code:

IIf(IsNull(DLookUp("StateAbbreviation","tblStates",
   "StateID = " & [AccountingStateID])),"",
      DLookUp("StateAbbreviation","tblStates",
   "StateID = " & [AccountingStateID])) AS AccountingState

April15Hater · Nov 9, 2008

I tried and it still errors out. The [AccountingStateID] field is blank sometimes, which is what I think is causing it to error out. I've never had luck with IIFs for some reason.

marleymanner1 · Nov 9, 2008

My guess then would be that you will probably have to nest even more logic to cover the blank spots. It can surely be done, as I have done stuff more complicated than this. I probably can't help you that unless I were to see the whole picture though. Sorry about that. Good luck! =)

RECrerar · Nov 18, 2008

You've probably got this sorted now, but if not the way that I have done this is my queries (substituting with your field names) is as follows

Code:

IIf([AccountingStateID] & "" = "", "",  
DLookUp ("StateAbbreviation","tblStates",
   "StateID = " & [AccountingStateID])) AS AccountingState

Hope that helps, Robyn

April15Hater · Nov 19, 2008

I think what I ended up doing was making the 2 letter code the primary ID and just updating the linked tables accordingly. But thanks for the input!

IIF/IsError sql Combo still returning #Error

April15Hater

Accountant

marleymanner1

Guest

April15Hater

Accountant

marleymanner1

Guest

RECrerar

Registered User.

April15Hater

Accountant

Similar threads

Users who are viewing this thread