Multiple criteria DLookup

R

Renee

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
 
W

Wayne Morgan

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.
 
R

Renee

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
 
W

Wayne Morgan

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.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top