Multiple criteria DLookup

[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

 

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

 

[Guest]

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

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

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

 

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