SQL - string

[indesisiv]

Hi there.

I am trying to use an SQL query in a report but i can't seem to get it to work. The query itself works but i can't seem to get it to work as i put it into the string.
The problem is getting the correct number of " in the format statements.

Any help please!!!

strSQL = "SELECT tblVenue.VenueID, tblVenue.VenueName, Format([VenueDate],"Long Date") AS VenueDates, tblVenue.VenueTime, tblVenue.PCT, tblVenue.SpecialistName, tblVenue.MaxClients, Count(tblClient.VenueID) AS CountOfVenueID, [MaxClients]-[CountOfVenueID] AS SpacesLeft "
strSQL = strSQL & "FROM tblVenue LEFT JOIN tblClient ON tblVenue.VenueID = tblClient.VenueID"
strSQL = strSQL & "GROUP BY tblVenue.VenueID, tblVenue.VenueName, Format([VenueDate],"Long Date"), tblVenue.VenueTime, tblVenue.PCT, tblVenue.SpecialistName, tblVenue.MaxClients "
strSQL = strSQL & "ORDER BY tblVenue.VenueName, Format([VenueDate],"Long Date");"

 

[FoFa]

Return the date fields normally and do the formating in the report instead of in the query. (Best Choice)

Try single quotes instead of double quotes around Long Date
'Long Date' instead of "Long Date"

 

[Pat Hartman]

Fofa's answer is correct but I would suggest leaving the query as a querydef since it is not dynamic. It is more efficient to use stored querydefs as RecordSources than SQL strings or tables.

 

[namliam]

Yes, change the "long date" as allready suggested or change it to ""long date""

Also you are missing a space at the end of your FROM line....

Regards

 

[indesisiv]

Thanks

Thanks All works fine now

Steve