Dlookup Problem

[meadt]

Hi guys,

Every time I run the code:

strSB = DLookup("Description", "SA R Lookup", "[Binary]=" & strSubAdr) & " To " & DLookup("[Equipment]", "Serial Destinations", "[ST]=" & Word1)

I get the error: 'You canceled the previous operation'

The first Dlookup runs fine on its own, the problem seems to lie with the '[ST]=" & Word1' part.

Any ideas?

Cheers,

 

[DCrake]

Because you have spaces in your table/query name you need to encompass it in square brackets [] I think

Also if the values you are looking up are textual you need to embed single quotes.

Also you cannot user = Equals to and To in the same context. You need to use Between .. And ...

Also I would suggest you wrap Nz() around each lookup to prevent further errors if no values are found

I would create the condition as a string before passing it to the DLookup

Finally the word Binary could be an Access reserved word so I would be careful about using this as a field name.

AirCode Example
Dim stWhere As String

stWhere = "[Binary] Between '" & strBusAdr & "' And " & Nz(DLookup([Equipment],"[Serial Destinations]","[ST]='" & Word1 & "'",strSubAdr)


strSB = DLookup("Description", "[SA R Lookup]", stWhere)

David

 

[meadt]

Thanks for you help, not quite there, but a step closer. The code now looks like:

strSB = DLookup("Description", "SA R Lookup", "[Binary]=" & strSubAdr) & " " & DLookup("[Equipment]", "[Serial Destinations]", "[ST]=" & Word1 & "'")

The error its returning now is:

Syntax error in string in query expression '[ST]=F".

 

[DCrake]

strSB = DLookup("Description", "SA R Lookup", "[Binary]=" & strSubAdr) & " " & DLookup("[Equipment]", "[Serial Destinations]", "[ST]='" & Word1 & "'")

You have a single quote missing.

David

 

[meadt]

Thank You! Can't believe i missed that. Works fine now!