Hi, I have an SQL statement that

[VoodooRage]

Hi,

I have an SQL statement that contains a DLookup as follows (pasted from the query builder):
DoCmd.RunSQL = "SELECT CPUMstr.CPUSN, CPUMstr.CPUMfg, " _
& " CPUMstr.CPUModel, CPUMstr.CPUAsset, " _
& "DLookUp("[CPUSN]","Users","[CPUSN] = " _
& "'" & [CPUSN] & "'&quot AS Expr1, " _
& " CPUMstr.CPUBench FROM CPUMstr " _
& "WHERE (((DLookUp("[CPUSN]","Users","[CPUSN] = '" & [CPUSN] & "'&quot) Is Null) AND ((CPUMstr.CPUBench)=True));

The problem is with the literals in the Dlookup. I can't seem to get the SQL to work in VB because of the literals required in the Dlookup. When I try to code the syntax for the text portion of the dlookup "[CPUSN]" to ''[CPUSN]'' at the end of the Dlookup where ''[CPUSN] ='' & '' ' '' & ''[CPUSN]'' & '' ' '' & ) is where the dlookup seems to get confused.

Thanks for any help, you people are super!

The query runs fine in the Query Builder but like I said the literals in the Dlookup are throwing the VB SQL into a tither.

 

[AvGuy]

Replace the quote marks (&quot in the literal with apostrophes (') and see if that helps.

AvGuy

 

[mp9]

Hmm. Don't use DCount would be my advice. How about:

DoCmd.RunSQL = "SELECT CPUMstr.CPUSN, CPUMstr.CPUMfg, " _
             & "CPUMstr.CPUModel, CPUMstr.CPUAsset, " _
             & "Users.CPUSN As Expr1 " _
             & "FROM CPUMstr LEFT JOIN Users " _
             & "ON CPUMstr.CPUSN = Users.CPUSN " _
             & "CPUMstr.CPUBench FROM CPUMstr " _
             & "WHERE Users.CPUSN Is Null AND " _ 
             & "CPUMstr.CPUBench=True;

You might want to check that this does the same as your query carefully!!!!!

http://www22.brinkster.com/accessory/

 

[Dalain]

From what I can See the Location of your " marks are incorrrect. Try this:


DoCmd.RunSQL = "SELECT CPUMstr.CPUSN, CPUMstr.CPUMfg, CPUMstr.CPUModel, CPUMstr.CPUAsset, " & DLookup("[CPUSN]", "Users", "[CPUSN] = '" & [CPUSN] & "'&quot & " AS Expr1, CPUMstr.CPUBench FROM CPUMstr WHERE (((" & DLookup("[CPUSN]", "Users", "[CPUSN] = '" & [CPUSN] & "'&quot & &quot Is Null) AND ((CPUMstr.CPUBench)=True));"


I did not use the & _ to shorten the line.

Pierre

 

[jaccess]

hi

in response to your Dlookup syntax..try this
set a variable, in this example, quote to equal a " (quote).
then concatenate the quote variable as in the example below
this has worked fine for me using DoCmd.RunSQL

Let quote = Chr$(34)

"DLookUp(" + quote + "[from_date]" + quote + "," + quote + "qtr_to_process" + quote + &quot