By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,397 Members | 1,370 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,397 IT Pros & Developers. It's quick & easy.

Dlookup and No Data

P: 46
I have a Dlookup Function that performs perfectly if their is data but when there isn't data I run into the runtime error. What is the best way to work around this so my users aren't getting a error.

Expand|Select|Wrap|Line Numbers
  1. [Punch ID] = Nz(DLookup("[Punch ID]", "[Copy of Employee Work Statistics1]", "Employee='" & Forms![Royal time Stamp]![Driver] & "' And [Date]=#" & Date & "#" & " And [Trip Sign Off]=0"), 0)
Mar 17 '14 #1
Share this Question
Share on Google+
3 Replies

P: 46
Problem solved using error handling
Expand|Select|Wrap|Line Numbers
  1. On Error GoTo error_nodata
  2. [Punch ID] = Nz(DLookup("[Punch ID]", "[Copy of Employee Work Statistics1]", "Employee='" & Forms![Royal Time Stamp]![Driver] & "' And [Date]=#" & Date & "#" & " And [Trip Sign Off]=0"), 0)
  3. [Type of Work] = Nz(DLookup("[Type of Work]", "[Copy of Employee Work Statistics1]", "Employee='" & Forms![Royal Time Stamp]![Driver] & "' And [Date]=#" & Date & "#" & " And [Trip Sign Off]=0"), 0)
  4. [Trip_S_on] = Nz(DLookup("[Trip Sign On]", "[Copy of Employee Work Statistics1]", "Employee='" & Forms![Royal Time Stamp]![Driver] & "' And [Date]=#" & Date & "#" & " And [Trip Sign Off]=0"), 0)
  5. error_nodata:
  6. Exit Sub
Mar 17 '14 #2

Seth Schrock
Expert 2.5K+
P: 2,951
What is the error number and description that you get?
Mar 17 '14 #3

Expert Mod 15k+
P: 31,768
Neither set of code, as posted, will ever run correctly and without error. Variables in VBA cannot take brackets ([]) and the syntax makes no sense for SQL.

It's hard to help when the question doesn't even make sense. Such a question, even with code that makes sense, would need an error message as a bare minimum.
Mar 17 '14 #4

Post your reply

Sign in to post your reply or Sign up for a free account.