Solved Dlookup....Another one ;)

[TajikBoy]

Hi guys,

Me again,

Trying to Dlookup a numeric value with 2 text criteria

Me.BillingRate = DLookup("Rate", "tblBillingRates", "Category = '" & Me.Category & "'" And "Position = '" & Me.Position & "'")

With above I get type Mismatch Error 13 and for love or god, can't understand why.... tblbillingrates snapshot is below, BillingRate is a field on my form alongside category and position

Any ideas?

 

[plog]

...& "'" And "Position = '" & ...

Your AND exists outside of double quotes but you don't use & before nor after it. You've overquoted.

 

[cheekybuddha]

You'll kick yourself when you work it out!

Me.BillingRate = DLookup("Rate", "tblBillingRates", "Category = '" & Me.Category & "' And Position = '" & Me.Position & "'")

 

[cheekybuddha]

A good trick is to use a variable to build your criteria which you can output to the Immediate Window (Ctrl+G) to check if needed:

Dim strCriteria As String

strCriteria = "Category = '" & Me.Category & "' And Position = '" & Me.Position & "'"
Debug.Print strCriteria
Me.BillingRate = DLookup("Rate", "tblBillingRates", strCriteria)

 

[TajikBoy]

You'll kick yourself when you work it out!

Me.BillingRate = DLookup("Rate", "tblBillingRates", "Category = '" & Me.Category & "' And Position = '" & Me.Position & "'")

Well, instead of kicking, I banged my head on the keyboard..... same same.....

Thank you CheekyBuddha

 

[cheekybuddha]

You're welcome! (even though plog beat me to it! )