Search code examples
ms-accessvbams-access-2010

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

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

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

Picture of Table.

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"
Solution

  • 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 & "#")