MS Access VBA Dlookup on Yes/No field

Question

I can't for the life of me work out what is wrong with this, but I'm not an Access/VBA developer normally..

I have a database table with about 20 fields, one of which is a Yes/No field. I want to extract the Yes/No value using DLookup, however am receiving the following error:

Run-time error '3075':
Syntax error (missing operator) in query expression 'Enabled'.

The code I am using it:

MsgBox (DLookup("Enabled", "Numbers", "ID = " & Me.cbxNumber.Value & ""))

Enabled is a Yes/No field ID is a String field.

The above DLookup works absolutely fine for returning String values for other fields, so the last parameter with the search query, and the table field, should be fine. It is simply complaining about the return field ('Enabled') thinking it is a query.

The MsgBox element is also irrelevant, as I have tried assigning the result to an Integer and to a Boolean, and it's not complaining of a type mismatch which I would expect if this were the problematic part.

Any thoughts?

Mark Butler Mark Butler · Accepted Answer · 2014-09-18T12:52:33

Your code works fine for me:

Table:

Example table

Form:

Example form

Code:

Private Sub Command30_Click()

    MsgBox (DLookup("Enabled", "Numbers", "ID = " & Me.cbxNumber.Value & ""))

End Sub

The messagebox displays 0 or -1 as required. Things to check:

Is your code in the forms module? Otherwise Me.cbxNumber.Value won't return anything.

What do you get if you run

debug.print Me.cbxNumber

from the OnClick of a button on the form?

MS Access VBA Dlookup on Yes/No field

2 Answers