SQL and DLookup

  • Thread starter Thread starter Junior
  • Start date Start date
J

Junior

Hi- Setting a variable value using DLookup with a query - because i can't
seem to get the Where statement correct
I've provided the Dlookup and the Query SQL below - How can i use where in
the Dlookup to eliminate the query and pull the value directly from the
table? thanks for any help.

strDataDoc = DLookup("DataPath", "QLtrPathEOD")





QLtrPathEOD SQL:



SELECT TlkpLetter.LTRID, TlkpLetter.LTRPath, TlkpLetter.DataPath

FROM TlkpLetter

WHERE (((TlkpLetter.LTRID)="S"));
 
The syntax for DLookup is

Value = DLookup("FieldToReturn","TableOrQueryName","WhereClauseCriteria")

If the criteria are not met then a Null is returned, so your code has to
take this into account or a run time error will occur.

The above statement would return the same value as the first row of the
recordset returned from the SQL statement

SELECT FieldToReturn FROM TableOrQueryName WHERE WhereClauseCriteria

Rod Scoullar
 
Try

strDataDoc = DLookup("DataPath","TlkpLetter","LTRID='S'")

or if the string "S" was in a variable named strLTRID then it would be

strDataDoc = DLookup("DataPath","TlkpLetter","LTRID='" & strLTRID & "'")

Ron W
 
Ron Thanks- I've been struggling with ' and " for over an hour now -its so
simple - must be my dyslexia-
i'll save your example for next time my brain stalls..
 
Junior

There is a 3rd argument in the DLookup formula that acts like a SQL
"Where" clause. Try this.

strDataDoc = DLookup("DataPath", "TlkpLetter", "LTRID='S'")

Not sure about the single quotes in the last part - may need to
investigate how to put strings within strings like that.
 
Back
Top