How do I change the SQL Statement

[aziz rasul]

I have the following code:

    Set qdf = CurrentDb.QueryDefs("qryUnassignedWaitingListCookwell")

    strSQL = "SELECT Format(tblGroupUnassignedWaitingList!dateonwaitinglist & ' ' & tblGroupUnassignedWaitingList!timeonwaitinglist,'dd/mm/yy hh:nn:ss') AS [On Waiting List], tblGroupUnassignedWaitingList.enquirerID, tblGroupUnassignedWaitingList.GeneralEnquiryID, [Surname] & ', ' & [firstname] AS Name, IIf(IsNull([housenumber]),'',[housenumber] & ' ') & IIf(IsNull([streetname]),'',[streetname] & ', ') & IIf(IsNull(tblAreas!areaname),'',tblAreas!areaname & ', ') & IIf(IsNull([towncity]),'',[towncity] & ', ') & IIf(IsNull([County]),'',[County]) AS Address, tblEnquirers.DOB, tblEnquirers.Gender, tblGUWListCookwell.timeslot, tblGUWListCookwell.areaID, tblEnquirers.postcode, tblEnquirers.telno, tblEnquirers.Disability, tblEnquirers.professional " & _
             "FROM (tblEnquirers LEFT JOIN tblAreas ON tblEnquirers.areaID = tblAreas.areaID) INNER JOIN (tblGUWListCookwell INNER JOIN tblGroupUnassignedWaitingList ON (tblGUWListCookwell.groupprojectID = tblGroupUnassignedWaitingList.groupprojectID) AND (tblGUWListCookwell.enquirerID = tblGroupUnassignedWaitingList.enquirerID) AND (tblGUWListCookwell.dateonwaitinglist = tblGroupUnassignedWaitingList.dateonwaitinglist) AND (tblGUWListCookwell.timeonwaitinglist = tblGroupUnassignedWaitingList.timeonwaitinglist) AND (tblGUWListCookwell.GeneralEnquiryID = tblGroupUnassignedWaitingList.GeneralEnquiryID)) ON tblEnquirers.enquirerID = tblGroupUnassignedWaitingList.enquirerID " & _
             "WHERE (((tblGroupUnassignedWaitingList.groupprojectID) = 2)) " & _
             "ORDER BY CDate(Format(tblGroupUnassignedWaitingList!dateonwaitinglist & ' ' & tblGroupUnassignedWaitingList!timeonwaitinglist,[COLOR="Red"][B]'[/B][/COLOR]dd/mm/yy hh:nn:ss[COLOR="Red"][B]'[/B][/COLOR]));"
    qdf.SQL = strSQL

The single quotes, given in red above (ORDER BY line) , works in code i.e. I get no errors, but when I open the query, it doesn't like it. If I change: -

'dd/mm/yy hh:nn:ss'

to

"dd/mm/yy hh:nn:ss"

in the query then it's happy. How do I change qdf.SQL to get it to work. I have tried

"""dd/mm/yy hh:nn:ss"""

but it doesn't work.

 

[Banana]

What about ""dd/mmy/yy hh:nn:ss""?

 

[aziz rasul]

Thanks Banana, that worked. Yehaaaaaaaaaa!

 

[Banana]

In SQL, whenever you double up a special character, it becomes a escape character.

Therefore, if you did this:

SELECT """";

Output:

"

This is because the first and last " delimits a string, the second " escapes the third " which get printed as a literal "

In your case, you already had a string:

"This is a string", but to get "" inside it, you just need to escape only the " inside the string and not the " delimiting the string, thus:

"This is a ""string"""

Output:

This is a "string"

Hope that helps.

 

[aziz rasul]

Thanks for that. I'll remember in future.