CODEWITHSUNDEEP
ms-accessms-access-2007vba
rustynails
rustynails

Reputation: 129

How to pass date variable in DLookup in VBA?

Very simple question. I am asking the user to input date. I am using the month of that date to look up month name (5 for may, 6 for june, etc). I have a ref table called months with month_id and month_name (1-enero, 2-febrero, etc) in Spanish. I am writing this in VBA:

Dim FileDate as Date
Dim DateString as String

DateString = InputBox("Enter the file date in MM/DD/YYYY format", "Title")
FileDate = DateValue(DateString)
monthname = DLookup("[month_name]", "[months]", "Format(Month(FileDate),'0') = [month_id]")

But this is giving me errors. If I use Date() instead of FileDate, it is working correctly. Am I passing the variable incorrectly?

Upvotes: 2

Views: 3912

Answers (1)

AndASM
AndASM

Reputation: 10418

A literal date in Microsoft Access SQL is in the format #dd/mm/yyyy#. See microsoft's documentation here.

There is also the problem that you are sending the string "Format(Month(FileDate),'0')" not the value of the variable FileDate. Access doesn't know what "FileDate" is, only VBA knows that. So you should concatenate the value from FileDate to your string.

If you have a variable variable = "foo" and you want to pass it in a string to something like DLookup you would write "String " & variable not "String variable". This is so DLookup gets "String foo" not "String variable".

So when passing the criteria as a string such as you do with Dlookup you should use string concatenation to surround the value of your variable with two # symbols.

monthname = DLookup("[month_name]", "[months]", "#" & FileDate & "# = [month_id]")

Edit

Of course, I missread your original question. Because you are only using the month as the criteria you want something like:

monthname = DLookup("[month_name]", "[months]", Month(FileDate) & " = [month_id]")

Upvotes: 1

Related Questions