SQL Statement Error

dynamictiger

Registered User.
Local time
Today, 13:57
Joined
Feb 3, 2002
Messages
270
I have the following SQL statement in my code.

strSQL = "SELECT tblPoolOrderTreatment.ReportType, tblPoolOrderTreatment.Parameter, " & _
"tblPoolTestParameter.TestAcronym, tblPoolOrderTreatment.TestOrder, " & _
"tblPoolOrderTreatment.TreatmentOrder, tblPoolChemicalMessage.Introduction, " & _
"tblPoolChemicalMessage.Conclusion, Nz([Introduction]) " & ' strA ' & " Nz([Conclusion]) AS Message" & _
"FROM tblPoolTestParameter INNER JOIN (tblPoolChemicalMessage " & _
"INNER JOIN tblPoolOrderTreatment " & _
"ON tblPoolChemicalMessage.MessageID = tblPoolOrderTreatment.Message) " & _
"ON (tblPoolTestParameter.TestParameterID = tblPoolChemicalMessage.TestParameter) " & _
"AND (tblPoolTestParameter.TestParameterID = tblPoolOrderTreatment.Parameter) " & _
"ORDER BY tblPoolOrderTreatment.ReportType, tblPoolOrderTreatment.TreatmentOrder;"

The line
Nz([Introduction]) " & ' strA ' & " Nz([Conclusion]) AS Message" & _

is giving me no end of problems. I have tried every conceivable mixture. I cna get it to work as a query but not as an SQL statement no matter what I try.

This particular iteration is changing to a lovely red colour, which whilst pretty is not what we want.
 
Nz([Introduction]) " & ' strA ' & " Nz([Conclusion]) AS Message" & _

s/b

Nz([Introduction]) '" & strA & "' Nz([Conclusion]) AS Message" & _

ie single quotes within the string not outside it

HTH
 
Since you are using the Nz function in a query, I believe that you must have the second argument filled in, so:

Nz([Introduction],''{single quotes}) " & strA & " Nz([Conclusion],''{single quotes}) AS Message" & _

and drop the single quotes by your ampersands (&)

Try Harry's first ...
 
I dropped the NZ functions to test this solution and that did not seem to fix the problem (I also dropped the single quotes by the ampersands).

I am passing this SQL to another function that builds a tempoary query using ADOX. This code is reliable and is used throughout my application in this case though it is failing, so I still think there is an error in the SQL...somewhere.
 
What is strA? Also when using Nz in SQL it usualy looks like:

..., Nz([MyFieldName],Val If Null) AS [A new fieldname]

Unless strA contains "AS [A new fieldname]" then it will fail as you are putting two Nz's together with no obvious link

HTH
 

Users who are viewing this thread

Back
Top Bottom