Run-time error '3464' - Data type mismatch in criteria expresssion

[SourceOfAccess]

So...I'm trying to create a field that automatically calculates a value by gathering data from multiple tables.

My code seems to be fine, but MS Access returns with an error:

Run-time error '3464' - Data type mismatch in criteria expresssion

My code is as following:

Dim AccommodationType As String
 
AccommodationType = DLookup("AccommodationType", "Accommodations", "AccommodationID = '" & Me.Accommodation.Value & "'")

For your reference:

1. The error occurs on the second line of the code (DLookup)
2. The data type for the field: "AccommodationType" is Number
3. The data type for the field: "AccommodationID" is Number
4. The data type for the field: "Me.Accommodation.Value" is Number

Any ideas?

[mbizup]

Delimiters...

Text uses quotes
Dates use hash marks (#)
Numbers - no delimiters

so your syntax should be (drop the single quotes since AccommodationID is a numeric field):

AccommodationType = DLookup("AccommodationType", "Accommodations", "AccommodationID = " & Me.Accommodation.Value) 

[zmbd]

You might want to consider building your strings first too:

So:

Dim AccommodationType As String
 
AccommodationType = DLookup("AccommodationType", "Accommodations", "AccommodationID = '" & Me.Accommodation.Value & "'")

goes to:

Dim zCondition as string
'
zCondition = "AccommodationID = '" & _
    Me.Accommodation.Value & "'"
'
'Debug.Print "zCondition = " & zCondition 'uncomment to debug
'
DLookup("AccommodationType", _
    "Accommodations", _
     zCondition)

Most Dlookup errors occur from a malformed conditional string which is difficult to troubleshoot when built witin the function.

-z