I have the following code:

Set qdf = CurrentDb.QueryDefs("qryUnassignedWaitingListCookwell")

strSQL = "SELECT Format(tblGroupUnassignedWaitingList!dateonwaiting list & ' ' & tblGroupUnassignedWaitingList!timeonwaitinglist,'d d/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!dateonw aitinglist & ' ' & tblGroupUnassignedWaitingList!timeonwaitinglist,'d d/mm/yy hh:nn:ss'));"
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.

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

Thanks Banana, that worked. Yehaaaaaaaaaa!

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. :)

Thanks for that. I'll remember in future.