Multiple criteria DLookup

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Good Morning,

I am receiving a "Data type mismatch in criteria expression" error from the
following code (On the OnExit event of the field Week#):

Dim varQuantity As Variant
varQuantity = DLookup("[Quantity]", "Orders", "[EmpID]='" & [ID] &
"'And[Week#]='" & [Week#] & "'")
If (Not IsNull(varQuantity)) Then Me![Total] = varQuantity

The fields are numbers on both the table and the form. Any suggestions?

Thank you in advance as always!
Renee
 
The way you have it coded indicates that the values should be text. Try this
instead.

varQuantity = DLookup("[Quantity]", "Orders", "[EmpID]=" & [ID] & " And
[Week#]=" & [Week#])

Basically, just removing the single quotes.
 
Thank you Wayne!

Though, I have a new error now:

Run-time error 2001: You Canceled the previous operation
When I select debug, it highlights the variant DLookup statement.

Thanks for your patience,
Renee

Wayne Morgan said:
The way you have it coded indicates that the values should be text. Try this
instead.

varQuantity = DLookup("[Quantity]", "Orders", "[EmpID]=" & [ID] & " And
[Week#]=" & [Week#])

Basically, just removing the single quotes.

--
Wayne Morgan
MS Access MVP


Renee said:
Good Morning,

I am receiving a "Data type mismatch in criteria expression" error from
the
following code (On the OnExit event of the field Week#):

Dim varQuantity As Variant
varQuantity = DLookup("[Quantity]", "Orders", "[EmpID]='" & [ID] &
"'And[Week#]='" & [Week#] & "'")
If (Not IsNull(varQuantity)) Then Me![Total] = varQuantity

The fields are numbers on both the table and the form. Any suggestions?

Thank you in advance as always!
Renee
Click to expand...
Click to expand...
 
I have seen this if there is an error in the criteria, such as a name
conflict. Are the form control and field using the same name? Instead of
[ID] and [Week#] try:

Me.txtID and Me.txtWeekNo

or whatever the name of the controls are.
 
Back
Top