Syntax ARRRGH!!!

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

Guest

I have a query that contains the following text field
SOIntColorCode is a text field with the value M-05-5B-5F

I have defined a temporary field to extract the value 5B
IntColorA: Mid([SOIntColorCode],InStr(3,[SOIntColorCode],"-")+1,2)
produces the value 5B

I am trying to use the temporary field to lookup a value in a table.
I tried
IntColor1: DLookUp("IntColor","InteriorColorCodes","IntColorID =
[IntColorA]")
I get an error saying that Access can not find IntColorA

I tried
IntColor1: DLookUp("IntColor","InteriorColorCodes","IntColorID =
Mid([SOIntColorCode],InStr(3,[SOIntColorCode],"-")+1,2)")
I get a data type mismatch error

Help
 
Use the concatenation operator & to build your string before sending it as a
parameter to the function; something like:

DLookUp ("IntColor", "InteriorColorCodes", "IntColorID=""" & Mid
([SOIntColorCode], InStr (3,[SOIntColorCode], "-") +1, 2) & """")

In the above exemple, embedding quotes " inside a string has been doubled.

S. L.
 
Thanks , that worked.
I tried similar syntax but must have missed a " or two or space or comma or
bracket. Syntax ARRGH!!

Sylvain Lafontaine said:
Use the concatenation operator & to build your string before sending it as a
parameter to the function; something like:

DLookUp ("IntColor", "InteriorColorCodes", "IntColorID=""" & Mid
([SOIntColorCode], InStr (3,[SOIntColorCode], "-") +1, 2) & """")

In the above exemple, embedding quotes " inside a string has been doubled.

S. L.

cansoft said:
I have a query that contains the following text field
SOIntColorCode is a text field with the value M-05-5B-5F

I have defined a temporary field to extract the value 5B
IntColorA: Mid([SOIntColorCode],InStr(3,[SOIntColorCode],"-")+1,2)
produces the value 5B

I am trying to use the temporary field to lookup a value in a table.
I tried
IntColor1: DLookUp("IntColor","InteriorColorCodes","IntColorID =
[IntColorA]")
I get an error saying that Access can not find IntColorA

I tried
IntColor1: DLookUp("IntColor","InteriorColorCodes","IntColorID =
Mid([SOIntColorCode],InStr(3,[SOIntColorCode],"-")+1,2)")
I get a data type mismatch error

Help
Click to expand...
Click to expand...
 
Back
Top