SQL single/double quotations help???

[THEGREEK]

Hi,

The below query works fine in sql view of the query design mode, but when I try to copy that sql statement to a form so i can execute the query from code it says it's an invalid string and won't let me continue (highlights the whole statement in red)

Here it is below:

strSQLOrd = &quot;SELECT First(DCount('[GbmID]','tblGbmMaster',&quot;[ClassCodeLD] <> 'Null'&quot) AS [LD Orders], &quot; _
& &quot;First(DCount('[GbmID]','tblGbmMaster',&quot;[ClassCodeTF] <> 'Null'&quot) AS [TF Orders], &quot; _
& &quot;First(DCount('[GbmID]','tblGbmMaster',&quot;[ClassCodeTF] = 'SWNE'&quot) AS [SWNE], &quot; _
& &quot;First(DCount('[GbmID]','tblGbmMaster',&quot;[ClassCodeTF] = 'SWPO'&quot) AS [SWPO], &quot; _
& &quot;First(DCount('[GbmID]','tblGbmMaster',&quot;[ClassCodeFC] <> 'Null'&quot) AS [CC Orders], &quot; _
& &quot;First(DCount('[GbmID]','tblGbmMaster',&quot;[ClassCodeDal] <> 'Null'&quot) AS [Dal Orders] &quot; _
& &quot;FROM TblGbmMaster;&quot;

I think i'm prolly missing extra quotes or double quotes in various places so VBA thinks it's NOT a valid continuous string

I would really appreciate it if someone can help me out.

Thanks in advance,
THEGREEK

 

[RickSpr]

You're right. You have to double the quote characters that are supposed to be part of the resulting SQL statement, as opposed to the quote characters that delimit the strings VBA is supposed to concatenate. The corrected statement is:

strSQLOrd = &quot;SELECT First(DCount('[GbmID]','tblGbmMaster',&quot;&quot;[ClassCodeLD] <> 'Null'&quot;&quot) AS [LD Orders], &quot; _
& &quot;First(DCount('[GbmID]','tblGbmMaster',&quot;&quot;[ClassCodeTF] <> 'Null'&quot;&quot) AS [TF Orders], &quot; _
& &quot;First(DCount('[GbmID]','tblGbmMaster',&quot;&quot;[ClassCodeTF] = 'SWNE'&quot;&quot) AS [SWNE], &quot; _
& &quot;First(DCount('[GbmID]','tblGbmMaster',&quot;&quot;[ClassCodeTF] = 'SWPO'&quot;&quot) AS [SWPO], &quot; _
& &quot;First(DCount('[GbmID]','tblGbmMaster',&quot;&quot;[ClassCodeFC] <> 'Null'&quot;&quot) AS [CC Orders], &quot; _
& &quot;First(DCount('[GbmID]','tblGbmMaster',&quot;&quot;[ClassCodeDal] <> 'Null'&quot;&quot) AS [Dal Orders] &quot; _
& &quot;FROM TblGbmMaster;&quot;

(BTW, I'm assuming the strings 'Null' are actually testing for the string &quot;Null&quot;, as opposed to testing for null values. I make that assumption because you said the statement works in Query Design. If they're supposed to be testing for null values, you should change the third arguments to DCount to &quot;NOT IsNull([ClassCodeLD])&quot; etc., including the quotes.) Rick Sprague

 

[THEGREEK]

Thanks RickSpr

You helped a great deal

btw, the null is really checking for a null field not the value null.

is that what you condidered before typing the above?

Thanks again

 

[THEGREEK]

Hi Rick,

Sorry but i hope this isn't aksing too much, can you paste the same working statement that you did, with the null fields statements rather than the null values one.

Thanks alot,

 

[RickSpr]

Here you go:

strSQLOrd = "SELECT First(DCount('[GbmID]','tblGbmMaster',""Not IsNull([ClassCodeLD])"")) AS [LD Orders], " _
            & "First(DCount('[GbmID]','tblGbmMaster',""Not IsNull([ClassCodeTF])"")) AS [TF Orders], " _
            & "First(DCount('[GbmID]','tblGbmMaster',""Not IsNull([ClassCodeTF])"")) AS [SWNE], " _
            & "First(DCount('[GbmID]','tblGbmMaster',""Not IsNull([ClassCodeTF])"")) AS [SWPO], " _
            & "First(DCount('[GbmID]','tblGbmMaster',""Not IsNull([ClassCodeFC])"")) AS [CC Orders], " _
            & "First(DCount('[GbmID]','tblGbmMaster',""Not IsNull([ClassCodeDal])"")) AS [Dal Orders] " _
            & "FROM TblGbmMaster;"

Rick Sprague