DLookup Question - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

DLookup Question

Nathan Maturine

4 years ago

strOne = Forms!GroupInvoiceSystem!GroupPolicyNumber
strTwo = Format(Me.myInvoiceDate.Value, "dd/mm/yyyy")

thisPendingInv = DLookup("grpDueDte", "tbl_groupBilling", _
"grpPolicyNumber= " & "'" & strOne & "'" & " AND grpDueDte= #" & strTwo & "#")
thisPendingInvID = DLookup("grpBillingID", "openGrpInvoices2", _
"grpPolicyNumber=" & "'" & strOne & "'" & " AND grpDueDte= #" & strTwo & "#")

I would like some assistance...
The intent of the above code is to look in the billing table to determine if i already have an invoice for that due date for a client. If that due date already exist, I will update the record, else, I'll add a new invoice. (A client cannot have multiple invoices for the same due date).
I am looking up the Due Date to compare it with the due date being entered on the form
and i am looking up the grpBillingID (the primary key) so I can update the record if it exists.
My Questions are these...
1. What is wrong with my Dlookup code? it returns nothing and there is an invoice in the table for that client with the due date i am using
2. Is there a more efficient way to retrieve multiple pieces of data from a single table?

I've checked the spelling of the field names.

Adam Schwanz @Reply

4 years ago

First I would put brackets around tbl_groupBilling, so [tbl_groupbilling] sometimes special characters do wierd things.

For learning purposes, here's some ideas,

Are you getting any errors with it?
Have you done
msgbox strone
msgbox strtwo
to see what results they are giving?

Have you tried just using one criteria or hard coding in a value instead of variables and seeing if it works?

I think you'll find the issue trying those.

Nathan Maturine OP @Reply

4 years ago

I'm not getting any errors. I have done the message boxes to ensure the data is what it should be.
I have tried with one criteria and does work.
Here is a weird thing too...for our dates, we use dd/mm/yyyy. If i enter the due date on the form as mm/dd/yyyy it sees it as a match. In the table, it is stored as dd/mm/yyyy. I've even put an input mask on the field in the table.
I havent tried using the square brackets, so i'll try that now.

Adam Schwanz @Reply

4 years ago

As far as I know, input masks don't change how the data is stored, only whether the data is accepted by Access. I'm guessing your dates are still being stored in the mm/dd/yyyy format.

When you click on an actual field do you get a different value? For instance the image below with phone numbers, looks one way but the actual stored data is different. (Appears to be storing dashes but if you click the field it's not)

	Adam Schwanz @Reply 4 years ago

	Adam Schwanz @Reply 4 years ago
You could try to format the grpDueDate in the dlookup too

	Nathan Maturine OP @Reply 4 years ago
I inadvertently ssaid the lookup works with one criteria, but it does not. I changed the dlookup... grpBillID = DLookup("grpBillingID", "[tbl_groupBilling]", "grpDueDte =#" & strTwo & "#") MSgBox "I found ID " & grpBillID only returns the text

	Adam Schwanz @Reply 4 years ago
What if you do the other criteria? str one instead? I'd bet your problem is in that strtwo date format. You could try `Format(grpDueDate,"dd/mm/yyyy")=#"`

	Nathan Maturine OP @Reply 4 years ago
This is copy and paste from the table of the date in the field: 01/05/2022 This is the updated Dlookup Command: thisPendingInvID = DLookup("grpBillingID", "[tbl_groupBilling]", _ "grpPolicyNumber=" & "'" & strOne & "'" & " AND grpDueDte= #" & Format(strTwo, "dd/mm/yyyy") & "#") still no result.

	Adam Schwanz @Reply 4 years ago
Try `thisPendingInvID = DLOOKUP("grpBillingID","[tbl_groupBilling]","grpPolicyNumber=""" & strone & """ and Format(grpDueDate,"dd/mm/yyyy")=#" & strTwo & "#")`

	Nathan Maturine OP @Reply 4 years ago
So, using the strone variable, it returns the first occurrence of that policy in the table. Can you offer any suggestions on the strTwo variable format? strTwo = Format(Me.myInvoiceDate.Value, "dd/mm/yyyy") Can you also verify that the format of the dlookup command is valid?

	Nathan Maturine OP @Reply 4 years ago
I'm getting a syntax error with thisPendingInvID = DLOOKUP("grpBillingID","[tbl_groupBilling]","grpPolicyNumber=""" & strone & """ and Format(grpDueDate,"dd/mm/yyyy")=#" & strTwo & "#")

	Adam Schwanz @Reply 4 years ago
Hmm guess you can't format in the dlookup line, try concatenating it, if doesn't work you could try setting the format at the table level and seeing if that helps. `thisPendingInvID = DLOOKUP("grpBillingID","[tbl_groupBilling]","grpPolicyNumber=""" & strone & """ and " & Format(grpDueDate,"dd/mm/yyyy") & "=#" & strTwo & "#")`

	Adam Schwanz @Reply 4 years ago
Alternatively, I think you could just do this with your original code if the record is being stored as mm/dd/yyyy and just apperance is dd/mm/yyyy strTwo = Me.myInvoiceDate.Value

	Nathan Maturine OP @Reply 4 years ago
Here is something weird...if my due date in the table is day 13 to 31 (e.g. 13/05/2022) of the month, the dlookup works perfectly! But does not for days 1 - 12 (e.g. 01/05/2022)! Have you seen such a phenomena? if so, or if not, any ideas as to what may be wrong or more importantly how to solve this?

Adam Schwanz @Reply

4 years ago

It's because access knows that there is no month greater than 12, and then changes to your format. The default for access is mm/dd/yyyy and I believe that's decided by the country settings.

Access also supports ISO date.

About the only other thing I can think of if nothing else is working is to make a new query, bring in all the fields in tbl_groupBilling and then add a new expression, MyDate: Format(grpDueDate,"dd/mm/yyyy")

Then change your DLOOKUP to be

thisPendingInv = DLookup("grpDueDte", "NewQueryYouMade", "grpPolicyNumber= " & "'" & strOne & "'" & " AND MyDate= #" & strTwo & "#")

	Nathan Maturine OP @Reply 4 years ago
Hi Adam, it looked like it worked for a minute, but i must of made a mistake somewhere. Its after 1:30am here and i have to be back to work in about 6 hours. I'll tackle it again when i'm fully conscious. I really do appreciate your patience.

	Stefan Pinne @Reply 4 years ago
Try "dd-mm-yyyy" instead of "dd/mm/yyyy"

	Scott Axton @Reply 4 years ago
You might consider changing to the ISO Date Format Read all the way to the bottom. Another good - though old (2008) - ARTICLE by Allen Browne

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View