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


--
John

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)


--

Ken Snell
<MS ACCESS MVP>

"John" <(E-Mail Removed)> wrote in message
news:F714F349-42A4-4FD2-8E51-(E-Mail Removed)...
>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
>
>
> --
> John

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)

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"John" <(E-Mail Removed)> wrote in message
news:F714F349-42A4-4FD2-8E51-(E-Mail Removed)...
>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
>
>
> --
> John