Lookup Code Trouble

[Guest]

Hi Everyone.

I’m adding a cost lookup to my db, based on the example in the Tradewinds
database. I am trying
to add a “CostPerFoot†value to the form automatically, based on the
“MaterialType†entered by the
user. Both of these fields are maintained in the “MaterialCostTableâ€. Here
is what my ‘after update’
looks like on the “Material Type†field of the entry form:


Private Sub MaterialType_AfterUpdate()
On Error GoTo Err_MaterialType_AfterUpdate

Dim strFilter As String

' Evaluate filter before it's passed to DLookup function.
strFilter = "MaterialType = " & Me!MaterialType

' Look up product's unit price and assign it to CostPerFoot control.
Me!CostPerFoot = DLookup("CostPerFoot", "MaterialCostTable", strFilter)

Exit_MaterialType_AfterUpdate:
Exit Sub

Err_MaterialType_AfterUpdate:
MsgBox Err.Description
Resume Exit_MaterialType_AfterUpdate

End Sub

When the user enters a material type (say 022-4.00.065), they get the error
“Syntax error in number in query expression ‘MaterialType = 022-4.00.065’.

What am I doing wrong?

Thanks in advance for any assistance.

Best Regards,
Karen

 

[Guest]

Looks like the material type is a sting value so you have to change the
criteria to match

strFilter = "MaterialType = '" & Me!MaterialType & "'"

 

[Guest]

Karen:

Judging by the dash in the value it looks like the MaterialType field is of
text data type rather than a number data type as in the Northwind example, so
the value will need to be wrapped in quotes characters. To include a quotes
character in a string expression already delimited by quotes you use an
adjacent pair of quotes characters, so the code should be amended like so:

strFilter = "MaterialType = """ & Me!MaterialType & """"

This would evaluate to MaterialType = "022-4.00.065" and should cure the
problem.

Ken Sheridan
Stafford, England