Immediate IF in a Jet sql string

[llkhoutx]

I'm trying to execute a sql string, containing two IIF function calls, against a Jet table and cannot figure out the correct syntax.

The string built with the QBE frame is as follows:

SELECT tbTest.ClientID, tbTest.sClient AS Client,
IIf([fBillSeparately],[sFile],"") AS File,
tbTest.fBillSeparately AS [Group]
FROM tbTest
GROUP BY tbTest.ClientID, tbTest.sClient,
IIf([fBillSeparately],[sFile],""),
tbTest.fBillSeparately
ORDER BY tbTest.fBillSeparately;

The two IFF function calls, when the string is executed in VBA, are the problem.

Attached is a simple Access 2002 zipped database illustrating how I'm using the string. See Form1.

Hopefully someone can help me with the syntax.

Thank you in advance for your time and trouble.

 

[Jon K]

I don't have Access 2002, so I can't test it myself. But I think you can replace the double quotes in the two IIF() functions with single quotes like this:-

Dim SQL As String

SQL = "SELECT tbTest.ClientID, tbTest.sClient AS Client," & _
" IIf([fBillSeparately],[sFile],'') AS File," & _
" tbTest.fBillSeparately AS [Group] " & _
" FROM tbTest " & _
" GROUP BY tbTest.ClientID, tbTest.sClient, " & _
" IIf([fBillSeparately],[sFile],''), " & _
" tbTest.fBillSeparately " & _
" ORDER BY tbTest.fBillSeparately; "


If the single quotes do not work, you can replace the two double quotes in the IIF() functions with four double quotes:-
" IIf([fBillSeparately],[sFile],"""") AS File," & _

" IIf([fBillSeparately],[sFile],""""), " & _


(In code, two double quotes represent one double quote.)

Hope this helps.

-------------------------------
Just a thought. Since you already have the query, why not simply use the query?

In Access 97, if I want to create a recordset, I can do it with:-

Dim db As Database
Dim rs As Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset("queryName")

It gives me the same result as first building the SQL string in code and then using:-
Set rs = db.OpenRecordset(SQL)

 

[llkhoutx]

Thank you for the prompt reply. I've fallen into that trap before. The obvious gets you every time. Fresh eyes help.

I have to use SQL because I have optional multiple criteria that is build every time the query is run. I showed only the portion of the query giving me trouble.

Thanks again.