Get a value from a table field into a variable using VBA code in Microsoft Access 2010

Question

In MS Access 2010, I use a table having three fields:

1. ID(Auto Number)
2. BatchEndDate(Text)
3. IsImported(Yes/No).

I am using the following VBA Code to read a value from field 3 with reference to field 2. This code works well but when I change the data type of field 2 from Text to Date/Time, it doesn't work and an error message is displayed showing

Data type mismatch in criteria expression

Please suggest me the changes in code to resolve the issue.

Dim selectedBatchDate As Variant
selectedBatchDate = Me.cmboBatchDate

Dim importCheck As Variant
importCheck = DLookup("[Is Imported]", "BatchEndDate", "[Batch End Date] = '" & selectedBatchDate & "'")
    MsgBox ("Import Status is " & importCheck), vbInformation, "Import Status"

Answer 1

If you change the datatype of the field you also have to change the datatype of the parameter you use in your DLookup:

Try the code below, the #specifies that the parameter is a date value.

importCheck = DLookup("[Is Imported]", "BatchEndDate", "[Batch End Date] = #" & selectedBatchDate & "#")

Answer 2

You system defaults to the dd/mm/yyyy format for dates, and as you need string expressions for these, you have to use Format to apply a format of yyyy/mm/dd:

Dim selectedBatchDate As String
selectedBatchDate = Format(Me.cmboBatchDate, "yyyy\/mm\/dd")

Dim importCheck As Variant
importCheck = DLookup("[Is Imported]", "BatchEndDate", "[Batch End Date] = #" & selectedBatchDate & "#")