=IFERROR (DLOOKUP .......) -- Does Access have something like

D

dhstein

I want to check if the DLOOKUP will be successful and take a different path
if not. Does Access have a command like that. In Excel it would be
something like =IF (ISERROR (VLOOKUP (aaaa,bbbb,cccc,dddd) ,DOGOOD,DOBAD)

In Access ?

IF ERRORRETURN = (DLOOKUP( AAAAAAA) Then ????????

do nothing

ELSE

MYCONTROL.VALUE = DLOOKUP(........)
END IF
 
J

Jeff Boyce

You're describing "how". Please describe "what".

DLOOKUP is, I believe, a spreadsheet function. What are you trying to
accomplish?

Regards

Jeff Boyce
Microsoft Office/Access MVP
 
D

dhstein

I have a dlookup function that works most of the time. Sometimes under some
unusual conditions, it gives an error - invalid use of null. So I'm trying
to see if I can test the return code - and if it is an error condition do
something else. DLOOKUP is a valid Access function. Thanks.
 
J

John W. Vinson

I want to check if the DLOOKUP will be successful and take a different path
if not. Does Access have a command like that. In Excel it would be
something like =IF (ISERROR (VLOOKUP (aaaa,bbbb,cccc,dddd) ,DOGOOD,DOBAD)

In Access ?

IF ERRORRETURN = (DLOOKUP( AAAAAAA) Then ????????

do nothing

ELSE

MYCONTROL.VALUE = DLOOKUP(........)
END IF
Click to expand...

The DLookUp function will return a NULL if there is no matching record to the
criterion:

IIF(IsNull(DLookUp("[fieldname]", "[tablename]", "<criteria>"), DoGood, DoBad)
 
D

dhstein

John,

Thanks for your reply. I'm trying to lookup a category in a table. The
category is defined by the SKU being between the FirstEntry and the
NextEntry. This works most of the time, but for some reason when I use a SKU
of "5999" which is not in the table I get a message Error 94 Invalid Use of
Null. I tried to test for a null return from the dlookup but the value is
always "False" which seems to indicate success on the dlookup. Please let me
know if you have any other suggestions.

David



Dim FoundFlag As Boolean
Dim y As Boolean

MsgBox ProductUpdateSKU
y = IIf(IsNull(DLookup("Category", "tblCategories", "FirstEntry
<= '" & ProductUpdateSKU & "'" & " AND " & "NextEntry > '" & ProductUpdateSKU
& "'")), FoundFlag = True, FoundFlag = False)
MsgBox FoundFlag
Category = DLookup("Category", "tblCategories", "FirstEntry <=
'" & ProductUpdateSKU & "'" & " AND " & "NextEntry > '" & ProductUpdateSKU &
"'")
'MsgBox Category


John W. Vinson said:
I want to check if the DLOOKUP will be successful and take a different path
if not. Does Access have a command like that. In Excel it would be
something like =IF (ISERROR (VLOOKUP (aaaa,bbbb,cccc,dddd) ,DOGOOD,DOBAD)

In Access ?

IF ERRORRETURN = (DLOOKUP( AAAAAAA) Then ????????

do nothing

ELSE

MYCONTROL.VALUE = DLOOKUP(........)
END IF
Click to expand...

The DLookUp function will return a NULL if there is no matching record to the
criterion:

IIF(IsNull(DLookUp("[fieldname]", "[tablename]", "<criteria>"), DoGood, DoBad)
Click to expand...
 
J

John Spencer

Try

Category = NZ(DLookup("Category", "tblCategories", "FirstEntry <=
'" & ProductUpdateSKU & "'" & " AND " & "NextEntry > '" &
ProductUpdateSKU & "'"),"Not Found")

OR


Dim Y as Variant

Y = DLookup("Category", "tblCategories", "FirstEntry <=
'" & ProductUpdateSKU & "'" & " AND " & "NextEntry > '" &
ProductUpdateSKU & "'")

If Not IsNull(Y) then
Category = Y
End if

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John,

Thanks for your reply. I'm trying to lookup a category in a table. The
category is defined by the SKU being between the FirstEntry and the
NextEntry. This works most of the time, but for some reason when I use a SKU
of "5999" which is not in the table I get a message Error 94 Invalid Use of
Null. I tried to test for a null return from the dlookup but the value is
always "False" which seems to indicate success on the dlookup. Please let me
know if you have any other suggestions.

David



Dim FoundFlag As Boolean
Dim y As Boolean

MsgBox ProductUpdateSKU
y = IIf(IsNull(DLookup("Category", "tblCategories", "FirstEntry
<= '" & ProductUpdateSKU & "'" & " AND " & "NextEntry > '" & ProductUpdateSKU
& "'")), FoundFlag = True, FoundFlag = False)
MsgBox FoundFlag
Category = DLookup("Category", "tblCategories", "FirstEntry <=
'" & ProductUpdateSKU & "'" & " AND " & "NextEntry > '" & ProductUpdateSKU &
"'")
'MsgBox Category


John W. Vinson said:
I want to check if the DLOOKUP will be successful and take a different path
if not. Does Access have a command like that. In Excel it would be
something like =IF (ISERROR (VLOOKUP (aaaa,bbbb,cccc,dddd) ,DOGOOD,DOBAD)

In Access ?

IF ERRORRETURN = (DLOOKUP( AAAAAAA) Then ????????

do nothing

ELSE

MYCONTROL.VALUE = DLOOKUP(........)
END IF
Click to expand...
The DLookUp function will return a NULL if there is no matching record to the
criterion:

IIF(IsNull(DLookUp("[fieldname]", "[tablename]", "<criteria>"), DoGood, DoBad)
Click to expand...
Click to expand...
 
D

dhstein

John,

That did it - thanks for the help. I would have written back sooner
except that I was getting errors when trying to post before.



John Spencer said:
Try

Category = NZ(DLookup("Category", "tblCategories", "FirstEntry <=
'" & ProductUpdateSKU & "'" & " AND " & "NextEntry > '" &
ProductUpdateSKU & "'"),"Not Found")

OR


Dim Y as Variant

Y = DLookup("Category", "tblCategories", "FirstEntry <=
'" & ProductUpdateSKU & "'" & " AND " & "NextEntry > '" &
ProductUpdateSKU & "'")

If Not IsNull(Y) then
Category = Y
End if

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================

John,

Thanks for your reply. I'm trying to lookup a category in a table. The
category is defined by the SKU being between the FirstEntry and the
NextEntry. This works most of the time, but for some reason when I use a SKU
of "5999" which is not in the table I get a message Error 94 Invalid Use of
Null. I tried to test for a null return from the dlookup but the value is
always "False" which seems to indicate success on the dlookup. Please let me
know if you have any other suggestions.

David



Dim FoundFlag As Boolean
Dim y As Boolean

MsgBox ProductUpdateSKU
y = IIf(IsNull(DLookup("Category", "tblCategories", "FirstEntry
<= '" & ProductUpdateSKU & "'" & " AND " & "NextEntry > '" & ProductUpdateSKU
& "'")), FoundFlag = True, FoundFlag = False)
MsgBox FoundFlag
Category = DLookup("Category", "tblCategories", "FirstEntry <=
'" & ProductUpdateSKU & "'" & " AND " & "NextEntry > '" & ProductUpdateSKU &
"'")
'MsgBox Category


John W. Vinson said:
On Mon, 23 Feb 2009 14:50:01 -0800, dhstein

I want to check if the DLOOKUP will be successful and take a different path
if not. Does Access have a command like that. In Excel it would be
something like =IF (ISERROR (VLOOKUP (aaaa,bbbb,cccc,dddd) ,DOGOOD,DOBAD)

In Access ?

IF ERRORRETURN = (DLOOKUP( AAAAAAA) Then ????????

do nothing

ELSE

MYCONTROL.VALUE = DLOOKUP(........)
END IF

The DLookUp function will return a NULL if there is no matching record to the
criterion:

IIF(IsNull(DLookUp("[fieldname]", "[tablename]", "<criteria>"), DoGood, DoBad)
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

count Like in Access 1
Dlookup issue with Access 2003! PLEASE HELP!!! 1
List of Direct and Indirect Reports 4
Count multiple entries in Access 2
Text To Columns in Access 18
How to use DLOOKUP with the Like * Functions 2
Access MS Access: Avoiding duplicates from an append query using DLookup? 3
RefreshTitleBar problems 2

Top