dlookup syntax error in string

[Gregvg]

This is the first time i have tried to use the dlookup function. I have the following code. The UnitRate and the TotalCostPerMile fields are formated for currency.The VehicleId and The UnitNumber are both formatted for text. I am getting a synyax error in string in query expression [DeliveryId] error. This has frustrated me for 2 days now. any help will be greatly appreciated.

Private Sub UnitNumber_Click()
    UnitRate.Value = DLookup("TotalCostPerMile", "[Vehicle List]", "[VehicleId]=" & [UnitNumber] & "'")
End Sub

 

[vbaInet]

If the data type of VehicleID is Number then use this:

Me.UnitRate.Value = DLookup("TotalCostPerMile", "[Vehicle List]", "[VehicleId] = " & Me.[UnitNumber])

For Text, use:

Me.UnitRate.Value = DLookup("TotalCostPerMile", "[Vehicle List]", "[VehicleId] = '" & Me.[UnitNumber] & "'")

In both cases, it will fail if UnitNumber is "" or Null so you may want to perform some validation before allowing the code run.

 

[Gregvg]

That was it. Thank you very much

 

[vbaInet]

You're welcome!