Type Mismatch in Expression

[aziz rasul]

I have the following SQL statement from a select query in which I'm getting a mismatch error: -

SELECT qryUnassignedWaitingListCookwell.Name
FROM tblGroupUnassignedWaitingList INNER JOIN qryUnassignedWaitingListCookwell ON tblGroupUnassignedWaitingList.dateonwaitinglist = qryUnassignedWaitingListCookwell.[On Waiting List Date];

tblGroupUnassignedWaitingList.dateonwaitinglist is formatted as dd/mm/yy in design view.

The SQL of "qryUnassignedWaitingListCookwell" is: -

SELECT Format(tblGroupUnassignedWaitingList!dateonwaitinglist & ' ' & tblGroupUnassignedWaitingList!timeonwaitinglist,'dd/mm/yy hh:nn:ss') AS [On Waiting List], Format(tblGroupUnassignedWaitingList!dateonwaitinglist,"dd/mm/yy") AS [On Waiting List Date], Format(tblGroupUnassignedWaitingList!timeonwaitinglist,'hh:nn:ss') AS [On Waiting List Time], 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
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 (((tblGUWListCookwell.timeslot)='Afternoon') AND ((tblGUWListCookwell.areaID)=2) AND ((tblGroupUnassignedWaitingList.groupprojectID)=2))
ORDER BY CDate(Format(tblGroupUnassignedWaitingList!dateonwaitinglist & ' ' & tblGroupUnassignedWaitingList!timeonwaitinglist,"dd/mm/yy hh:nn:ss"));

 

[gemma-the-husky]

can you not design these in the visual window - its much easier

it MAY be that the dates are not masked with # characters, but i am not sure

if you can use the design window, you avoid all these issues

 

[Brianwarnock]

This field in your first query is infact a string, you need tp use Cdate as you did in the Order By


SELECT Format(tblGroupUnassignedWaitingList!dateonwaitinglist & ' ' & tblGroupUnassignedWaitingList!timeonwaitinglist,'dd/mm/yy hh:nn:ss') AS [On Waiting List],

Brian

 

[aziz rasul]

The query was being designed visually when I was getting this error. I tried Cdate and it now works. Thanks both.