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.
d10u4vAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Scott McDaniel (Microsoft Access MVP - EE MVE )Infotrakker SoftwareCommented:
Try using Nz instead:

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

Or IsNull:

If DLookup("ShredConfirmationCode", "tblShreddingHistory", "ShrdConfirmationCode='" & ShredCC & "' AND IsNull(" & BagOrKg & ")")
0
MikeTooleCommented:
Your code seems to be trying to include the value of a local variable, BagOrKg as the name of a column in the criteria of your DLookup. Did you mean:
If DLookup("ShredConfirmationCode", "tblShreddingHistory", "ShrdConfirmationCode='" & ShredCC & "' AND BagOrKg Is Null") Then
...
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
d10u4vAuthor Commented:
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?
0
Determine the Perfect Price for Your IT Services

Do you wonder if your IT business is truly profitable or if you should raise your prices? Learn how to calculate your overhead burden with our free interactive tool and use it to determine the right price for your IT services. Download your free eBook now!

MikeTooleCommented:
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
0
d10u4vAuthor Commented:
I have just seen the comment with:
If DLookup("ShredConfirmationCode", "tblShreddingHistory", "ShredConfirmationCode='" & ShredCC & "' AND BagOrKg Is Null") Then

I get a datatype mismatch error.
0
d10u4vAuthor Commented:
No its not a column it is a field heading in the tblShreddingHistory table

BagOrKg is Text
ShrdConfirmationCode is Number
0
MikeTooleCommented:
You don't require quotes for number in creiteria:

If DLookup("ShredConfirmationCode", "tblShreddingHistory", "ShredConfirmationCode=" & ShredCC & " AND BagOrKg Is Null") Then
0
bluelizardCommented:
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
0
MikeTooleCommented:
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...
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.