DLookup with multiple criteria 1

[osx99]

I have the following VB code which I am using to populate an unbound field called [Text896] which works fine

Me![Text896] = DLookup("[Benchmark Unit Rate]", "[Unit Rates]", "[Structure Type] = Forms![Structure Data Input].[Structure Type]&quot

However, I need to add further criteria so that the Dlookup is able to select unique rows from the lookup table. I have tried the following but need help with the correct syntax as this doesn't work??

Me![Text896] = DLookup("[Benchmark Unit Rate]", "[Unit Rates]", "[Structure Type] = Forms![Structure Data Input].[Structure Type] and [Package Type] = Forms![Structure Data Input].[Work Package]&quot

Thanks in advance

Osx

 

[jrbarnett]

This will work if the structure type and work package fields are numeric - if they are text you will need to put apostrophes or double quote marks around them.

Me![Text896] = DLookup("[Benchmark Unit Rate]", "[Unit Rates]", "[Structure Type] = " & Forms![Structure Data Input].[Structure Type] & " and [Package Type] = " & Forms![Structure Data Input].[Work Package])

John

 

[tech12345]

As someone fairly new to this, the main problem I had with this was not including the AND as text (ie inside quotes somewhere), as jrbarnett has obviously correctly done in the above example. Sometimes its little things like this which are not accentuated in Help files and examples, and (I hope ) worth pointing out, especially for those of us without a background in Programming.

 

[osx99]

John,

Yes, structure type and work package fields are both numeric.

I have tried your code and get the following error

run-time error '3075'
Syntax error (missing operator) in query expression '[Structure Type] = 1 and [Package Type] = '.

Have been trying variations but no luck?

 

[osx99]

Got it! jrbarnett your code is perfect!! Thanks

It was just one of the criteria was returning a null value producing the error.

Thanks again

Osx

 

[jrbarnett]

One more point, related to this which may be necessary in the future: if you are matching against date/time values, the data needs to be enclosed in hash marks, eg:

dlookup ("Fieldname", "Tablename", "field=#31-jan-2003#&quot

John