DLookup Data Type Mismatch

[Guest]

I am trying to run the following
Private Sub Cartage_GotFocus()

Result = DLookup("Cost", "Cartage", "ClientID = '" & Forms!Order!ClientID &
"'_ And StoreID = '" & Forms!Order!OrderDetail!StoreID & "'")

Cartage.Value = Result

End Sub

I Keep getting Data Type Mismatch. When in the DeBug window with the cursor
over the string it shows the correct value.
When I hard code
Result = DLookup("Cost", "Cartage", "StoreID = 3") or
Result = DLookup("Cost", "Cartage", "ClientID = 5") I get the right "Cost"
and I can enter the cost in manually in the form.
It seems to be when I add the & Forms! etc that it rejects.
Am not sure where to look now.
Help appreciated and thank you in advance

 

[Ken Snell [MVP]]

Your hard-coded example shows that the fields are numeric, not text. But
you're using ' delimiters for the values in the DLookup with the form
controls as the sources for the values. Get rid of the ' characters; those
are used for text field, not numeric fields.

Also, your line continuation character is inside the text string that you're
building; it should be outside of it.


Result = DLookup("Cost", "Cartage", "ClientID = " & Forms!Order!ClientID & _
" And StoreID = " & Forms!Order!OrderDetail!StoreID)

 

[Allen Browne]

If ClientID and StoreID are Number fields, drop the extra quotes. You only
use those if they are Text fields.

Since there are now no quotes, the string will be mal-formed if the controls
are null. To work around that, use Nz() to supply a value for null.

Access 2003 seems a bit inconsistent when referring to a control in the form
in the subform control of a main form. Adding the ".Form" bit can help solve
that if you are using this version.

It might be easier to create a string variable to hold the Criteria. That
way you can add:
Debug.Print strWhere
and see if the string is correctly formed if the DLookup() fails.

The brackets are optional, but can help.

So, you end up with something like this:
Dim strWhere As String
Dim Result As Variant

strWhere = "(ClientID = " & Nz(Forms!Order!ClientID,0) & _
") AND (StoreID = " & Nz(Forms!Order.OrderDetail.Form!StoreID,0) & ")"

Result = DLookup("Cost", "Cartage", strWhere)