DLookUp 4

[BobChesh]

Is it possible to set two criteria in the selection part of a DLookup function

I am trying to get a single value for a query with several records. To get the single value I need to set the [startdate] AND [SLTUnique], as below.

slastart = DLookup("starttime", "SLAshiftquery", "startday = " & currentday And "SLTUnique =" & callSLT)

The original Dlookup worked i.e. slastart = DLookup("starttime", "SLAshiftquery", "startday = " & currentday), but gave many records.

I get a TypeMismatch error, although I have set the SLTUnique to Integer (because is it one)


Any ideas please, as my brain is beginning to hurt!!

Bob C

 

[RickSpr]

You just need to put the AND inside the quotes:

slastart = DLookup("starttime", "SLAshiftquery", "startday = " & currentday [red]& " And [/red]SLTUnique =" & callSLT)

The And is in the quotes because the string you're building gets put into a SQL query's WHERE clause. It's SQL that interprets the criteria, not VBA.


Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[PJStephenson]

slastart = DLookup("starttime", "SLAshiftquery", "startday = " & currentday[RED] And "SLTUnique[/RED] =" & callSLT)
Should be
slastart = DLookup("starttime", "SLAshiftquery", "startday = " & currentday [RED]& " And SLTUnique[/RED] =" & callSLT)
also remember that strings need to be inside inverted commas and dates inside hashes.
HTH
Peter