asked on

Dlookup

Hi,

I have this Dlookup which i am trying to run in VBA, but i keep getting a runtime error. Where am i going wrong?

ShredCC = InputBox("Enter the Shred Confirmation Code for the process you require an Invoice for.", "ShredIT - Print Invoice")

If DLookup("ShredConfirmationCode", "tblShreddingHistory", "ShrdConfirmationCode='" & ShredCC & "' AND '" & BagOrKg & "' Is Null") Then
MsgBox "This Shredding procedure does not have a billed by method associated with it!" & Chr(10) & Chr(10) & "Before you print the invoice you must set this up.?", vbOKOnly, "ShredIT - Assign DCN"
Exit Function
Else

Runtime Error 2001
You cancelled the previous operation

I am trying to show the message if the ShredConfirmationCode matches the Input box and th BagOrKg field is Null.

Scott McDaniel (EE MVE )

Try using Nz instead:

If DLookup("ShredConfirmationCode", "tblShreddingHistory", "ShrdConfirmationCode='" & ShredCC & "' AND Nz('" & BagOrKg & ","")' ='')"

Or IsNull:

If DLookup("ShredConfirmationCode", "tblShreddingHistory", "ShrdConfirmationCode='" & ShredCC & "' AND IsNull(" & BagOrKg & ")")

ASKER CERTIFIED SOLUTION

MikeToole

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

d10u4v

ASKER

Hi,

I have used:
If DLookup("ShredConfirmationCode", "tblShreddingHistory", "ShredConfirmationCode='" & ShredCC & "' AND IsNull(" & BagOrKg & ")")

It works once whn running the code from the debug window, but when it runs on its own, it doesn't. I get the following error:

RunTime Error 3075
Wrong Number of Aguments used with function in query expression
'ShredConfimrationCode='550012830' AND IsNull()'.

Any ideas?

MikeToole

If BagOrKg is a VB variable then you don't need it as part of your DLookup Criteria:

If DLookup("ShredConfirmationCode", "tblShreddingHistory", "ShredConfirmationCode='" & ShredCC & "'") AND IsNull(BagOrKg)

If BagOrKg is a column name then it needs to be a literal in the DLoolUp criteria - as in my previous post.

The run time error is because you're setting the value of BagOrKg as the variable name in the IsNull() function - So when BagOrKg is null, there is no parameter for IsNull to test

d10u4v

ASKER

I have just seen the comment with:
If DLookup("ShredConfirmationCode", "tblShreddingHistory", "ShredConfirmationCode='" & ShredCC & "' AND BagOrKg Is Null") Then

I get a datatype mismatch error.

d10u4v

ASKER

No its not a column it is a field heading in the tblShreddingHistory table

BagOrKg is Text
ShrdConfirmationCode is Number

MikeToole

You don't require quotes for number in creiteria:

If DLookup("ShredConfirmationCode", "tblShreddingHistory", "ShredConfirmationCode=" & ShredCC & " AND BagOrKg Is Null") Then

bluelizard

another thing (tackle this once you get dlookup running):

you write

"If Dlookup(......) Then ....".

Dlookup will return something, in your case a ShredConfirmationCode, which is a number. if no matching record is found in the DB, Dlookup will return NULL. to test for this, you should write

"If IsNull( Dlookup(......) ) Then ...."

instead of simply "If Dlookup..." (this would be ok if you looked up a boolean and you're sure that Dlookup will find a record.)

--bluelizard

MikeToole

In fact, if your're looking for existance then you'd be better off with dcount() :

If DCount("*", "tblShreddingHistory", "ShredConfirmationCode=" & ShredCC & " AND BagOrKg Is Null") = 0 Then

- it gets simpler as we go along...