Passing variable in DLookup (VBA)

[rustynails]

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

Dim FileDate as Date

Dim DateString as String

Dim monthname 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 it is giving me error. If I use "Format(Month(Date()),'0') = [month_id]" it is working correctly, but apparently it does not like it when I pass a variable. What am I doing incorrectly?



Edit: I understand that there is MonthName function, but I am doing this because I have a need for month names in Spanish, so that's why I created a list of month names in a table in Spanish

[June7]

It doesn't work because the variable is within quotes. This means the literal string "FileDate" is passed, not the contents of FileDate. Need to concatenate variables.

The Format function should not be necessary.

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

Also, if user mistypes entry your code will still attempt the lookup. Should have some validation or other error handling code. Like:

If IsDate(DateString) Then
...

[rustynails]

It doesn't work because the variable is within quotes. This means the literal string "FileDate" is passed, not the contents of FileDate. Need to concatenate variables.

The Format function should not be necessary.

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

Also, if user mistypes entry your code will still attempt the lookup. Should have some validation or other error handling code. Like:

If IsDate(DateString) Then
...

Oooo got it. Thank you june7 you are awesome as always.