Posted on 2007-03-26
Last Modified: 2013-11-27

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

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.
Question by:d10u4v
LVL 84
ID: 18791971
Try using Nz instead:

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

Or IsNull:

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

Accepted Solution

MikeToole earned 500 total points
ID: 18792015
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

Author Comment

ID: 18792025

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?
Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

LVL 27

Expert Comment

ID: 18792088
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

Author Comment

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

I get a datatype mismatch error.

Author Comment

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

BagOrKg is Text
ShrdConfirmationCode is Number
LVL 27

Expert Comment

ID: 18792136
You don't require quotes for number in creiteria:

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

Expert Comment

ID: 18792329
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.)

LVL 27

Expert Comment

ID: 18792496
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...

Featured Post

Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I originally created this report in Crystal Reports 2008 where there is an option to underlay sections. I initially came across the problem in Access Reports where I was unable to run my border lines down through the entire page as I was using the P…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.

830 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question