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:

ID(Auto Number)
BatchEndDate(Text)
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"

Wouter · Accepted Answer · 2016-02-11 09:58:44Z

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

answered Feb 11, 2016 at 9:58

Wouter

6125 silver badges14 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Gustav · Accepted Answer · 2016-02-11 10:06:47Z

0

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

answered Feb 11, 2016 at 10:06

Gustav

56.5k7 gold badges33 silver badges65 bronze badges

Collectives™ on Stack Overflow

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

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related