Syntax Error

[aziz rasul]

Why do I get the error

Syntax error (missing operator) in query expression 'nn:ss')'.

in the following part of a select query

Format(tblGroupUnassignedWaitingList!dateonwaitinglist & ' ' & tblGroupUnassignedWaitingList!timeonwaitinglist,'dd/mm/yy hh:nn:ss')

Sometimes when I run the query it works and other times I get the error.

 

[vbaInet]

Why do I get the error

Syntax error (missing operator) in query expression 'nn:ss')'.

I think you mean can someone help you with the error?

Format(tblGroupUnassignedWaitingList!dateonwaitinglist & " " & tblGroupUnassignedWaitingList!timeonwaitinglist,'dd/mm/yy hh:nn:ss')

Is that table on the only table you're using in your query? Is the dateonwaitinglist field a date field and is the timeonwaiting field a time field?

Ultimately you should do the formatting on your form, not your query.

 

[Brianwarnock]

Well if we are going to be picky he should have a Date/time field not separate fields, however as the expression works sometime and not others I guess we need to examine the data and see if we can spot what causes it to fail, it should work even if the date or time are empty. Never tried it with both empty presumably you get 31/12/1899 00:00:00

Brian

 

[aziz rasul]

See query in attachment.

 

[Brianwarnock]

I don't know how this DB is set up but if I try to do anything it just locks up on me. I cannot examine the query. The only thing I discivered was that a table used for a combo in a table lookup is missing.

Brian

 

[vbaInet]

The database has no relationships and hence is suffering from lack of normalization.

 

[aziz rasul]

I have attached the db again with relationships.

 

[vbaInet]

Have a look at the attached. I don't know what you were looking to achieve in the query. Again, boils down to you reading up on normalization.

You may also want to re-read what the CDate and Format functions do.

 

[aziz rasul]

The db I posted is part of a much larger booking db which is too large to attach. The links in the query are there to establish the booking of an individual and has worked in the past. But over the past few weeks on the clients PC it sometimes works and other times it doesn't. So there is something subtle going on which I'm struggling to work out, hence this thread.

 

[vbaInet]

Not quite subtle

My advise:
1. Return raw data in your queries and perform any formatting on the control that is displaying the result. It helps your query run faster. For example, instead of doing this:

CDate(Format(tblGroupUnassignedWaitingList!dateonwaitinglist & ' ' & tblGroupUnassignedWaitingList!timeonwaitinglist,'dd/mm/yy hh:nn:ss'))

You do this:

Expr1: tblGroupUnassignedWaitingList!dateonwaitinglist & " " & tblGroupUnassignedWaitingList!timeonwaitinglist

Then on the control that will display this value you do this:

Format(CDate([B][COLOR=Red][Expr1][/COLOR][/B]), "dd/mm/yy hh:nn:ss")

Notice that CDate comes first to cast the value from a type String to a type Date, before formatting.
2. You may need to revise the design of the query for more efficiency.
3. Avoid using names like "Name" even if it's an alias. That is a reserved name.

Anyway, here's a version that should work:

SELECT Format(CDate([tblGroupUnassignedWaitingList]![dateonwaitinglist] & " " & [tblGroupUnassignedWaitingList]![timeonwaitinglist]),"dd/mm/yy hh:nn:ss") AS OnWaOnWaitingList, Format(CDate([tblGroupUnassignedWaitingList]![dateonwaitinglist]),"dd/mm/yy") AS OnWaitingListDate, Format([tblGroupUnassignedWaitingList]![timeonwaitinglist],"hh:nn:ss") AS OnWaitingListTime, tblGroupUnassignedWaitingList.enquirerID, tblGroupUnassignedWaitingList.GeneralEnquiryID, [Surname] & ', ' & [firstname] AS [B][COLOR=Red]Name1[/COLOR][/B], 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, tblEnquirers.postcode, tblEnquirers.telno, tblEnquirers.Disability
FROM (tblEnquirers LEFT JOIN tblAreas ON tblEnquirers.areaID = tblAreas.areaID) INNER JOIN (tblGUWListCookwell INNER JOIN tblGroupUnassignedWaitingList ON (tblGUWListCookwell.GeneralEnquiryID = tblGroupUnassignedWaitingList.GeneralEnquiryID) AND (tblGUWListCookwell.timeonwaitinglist = tblGroupUnassignedWaitingList.timeonwaitinglist) AND (tblGUWListCookwell.dateonwaitinglist = tblGroupUnassignedWaitingList.dateonwaitinglist) AND (tblGUWListCookwell.enquirerID = tblGroupUnassignedWaitingList.enquirerID) AND (tblGUWListCookwell.groupprojectID = tblGroupUnassignedWaitingList.groupprojectID)) ON tblEnquirers.enquirerID = tblGroupUnassignedWaitingList.enquirerID
GROUP BY Format(CDate([tblGroupUnassignedWaitingList]![dateonwaitinglist]),"dd/mm/yy"), tblGroupUnassignedWaitingList.enquirerID, tblGroupUnassignedWaitingList.GeneralEnquiryID, [Surname] & ', ' & [firstname], IIf(IsNull([housenumber]),'',[housenumber] & ' ') & IIf(IsNull([streetname]),'',[streetname] & ', ') & IIf(IsNull(tblAreas!areaname),'',tblAreas!areaname & ', ') & IIf(IsNull([towncity]),'',[towncity] & ', ') & IIf(IsNull([County]),'',[County]), tblEnquirers.DOB, tblEnquirers.Gender, tblEnquirers.postcode, tblEnquirers.telno, tblEnquirers.Disability, Format(CDate([tblGroupUnassignedWaitingList]![dateonwaitinglist] & " " & [tblGroupUnassignedWaitingList]![timeonwaitinglist]),"dd/mm/yy hh:nn:ss"), Format([tblGroupUnassignedWaitingList]![timeonwaitinglist],"hh:nn:ss"), Format(CDate([tblGroupUnassignedWaitingList]![dateonwaitinglist] & " " & [tblGroupUnassignedWaitingList]![timeonwaitinglist]),"dd/mm/yy hh:nn:ss"), tblGroupUnassignedWaitingList.groupprojectID
HAVING (((tblGroupUnassignedWaitingList.groupprojectID)=2))
ORDER BY Format([tblGroupUnassignedWaitingList]![timeonwaitinglist],"hh:nn:ss");

I renamed Name to Name1 (highlighted in red). Up to you to keep it or go back to the reserved name "Name".

If you do rename then be careful because you would need amend all the controls and objects dependent on or using that field.

 

[aziz rasul]

I will have a look during the weekend and come back to you vbaInet if I still have problems. Thanks in the meantime.