UtterAccess Discussion Forums > Help with Dlookup in VBA

UtterAccess Discussion Forums > Microsoft® Access > Access Queries

tbonehwd

Mar 17 2006, 11:39 AM

I had this working just fine in Access query designer and I am trying to us it in VBA and keep getting a complile error. I think it has to do with the double quotes but I am not sure what to do. I simply changed the view in the query designer to sql view to get this code any suggestions?

CODE

sqlSTR="SELECT Spring2006.ITM_NUM, CDSITM_M.ITM_DSC1, DLookUp("[CMP_NME]","CDSADR_M","[CTM_NBR]='000000604974' and [ADR_CDE]='STANDARD' and [ADR_FLG]='0'" ) As Company " & _
", IIf(IsNull([UNITS_ORD]),0,[UNITS_ORD]) AS Ordered " & _
"FROM (Spring2006 LEFT JOIN qryAMWHOLE ON Spring2006.ITM_NUM = qryAMWHOLE.ITM_NUM) INNER JOIN CDSITM_M ON Spring2006.ITM_NUM = CDSITM_M.ITM_NUM " & _
"ORDER BY Spring2006.ITM_NUM;"

jinky44

Mar 17 2006, 12:14 PM

Untested, but I suspect the dlookup clause should be:

DLookUp(""[CMP_NME]"", ""CDSADR_M"",""[CTM_NBR]='000000604974' and [ADR_CDE]='STANDARD' and [ADR_FLG]='0'"" )

???

Jinky

tbonehwd

Mar 17 2006, 01:33 PM

Thank you that did it... Thanks for your help!

jinky44

Mar 17 2006, 02:16 PM

You are very welcome. Glad to have been of assistance.

And thanks for the feedback. It's always nice to know how things turned out.

Jinky.

This is a "lo-fi" version of our main content. To view the full version with more information, formatting and images, please click here.