I am not sure why I am getting this error message.
I did use the datevalue function to extract the date part from the field in an odbc linked table
Given below are two different version of the queries that tried. Could someone please point out what I am doing wrong.
SELECT LOCATION.NAME, ABSHEARINGCASE.HEARINGCASEID, CONCERNROLE_1.CONCERNROLENAME, ACTIVITY.STARTDATETIME, DateValue([ACTIVITY].[STARTDATETIME]) AS Expr1, ABSHEARINGCASE.HEARINGDATE, ABSHEARINGSCHEDULE.TELEPHONEIND, ABSHEARINGSCHEDULE.CLAIMANTREPONTELEPHONE
FROM ((((ABSHEARINGCASE LEFT JOIN LOCATION ON ABSHEARINGCASE.HEARINGLOCATIONCODE = LOCATION.LOCATIONID) LEFT JOIN ACTIVITY ON (ABSHEARINGCASE.HEARINGDATE = ACTIVITY.STARTDATETIME) AND (ABSHEARINGCASE.CASEID = ACTIVITY.CASEID)) LEFT JOIN (ABSHEARINGSCHEDULE RIGHT JOIN ABSACTIVITYHEARINGSCHEDULELINK ON ABSHEARINGSCHEDULE.ABSHEARINGSCHEDULEID = ABSACTIVITYHEARINGSCHEDULELINK.ABSHEARINGSCHEDULEID) ON ACTIVITY.ACTIVITYID = ABSACTIVITYHEARINGSCHEDULELINK.ACTIVITYID) LEFT JOIN (CASEHEADER LEFT JOIN CONCERNCASEROLE ON CASEHEADER.CASEID = CONCERNCASEROLE.CASEID) ON ABSHEARINGCASE.CASEID = CASEHEADER.CASEID) LEFT JOIN CONCERNROLE AS CONCERNROLE_1 ON CONCERNCASEROLE.CONCERNROLEID = CONCERNROLE_1.CONCERNROLEID
GROUP BY LOCATION.NAME, ABSHEARINGCASE.HEARINGCASEID, CONCERNROLE_1.CONCERNROLENAME, ACTIVITY.STARTDATETIME, ABSHEARINGCASE.HEARINGDATE, ABSHEARINGSCHEDULE.TELEPHONEIND, ABSHEARINGSCHEDULE.CLAIMANTREPONTELEPHONE, CONCERNCASEROLE.TYPECODE
HAVING (((DateValue([ACTIVITY].[STARTDATETIME]))=#1/27/2012#) AND ((CONCERNCASEROLE.TYPECODE)="CLRP"));
SELECT LOCATION.NAME, ABSHEARINGCASE.HEARINGCASEID, CONCERNROLE_1.CONCERNROLENAME, ACTIVITY.STARTDATETIME, DateValue([ACTIVITY].[STARTDATETIME]) AS Expr1, ABSHEARINGCASE.HEARINGDATE, ABSHEARINGSCHEDULE.TELEPHONEIND, ABSHEARINGSCHEDULE.CLAIMANTREPONTELEPHONE
FROM ((((ABSHEARINGCASE LEFT JOIN LOCATION ON ABSHEARINGCASE.HEARINGLOCATIONCODE = LOCATION.LOCATIONID) LEFT JOIN ACTIVITY ON (ABSHEARINGCASE.HEARINGDATE = ACTIVITY.STARTDATETIME) AND (ABSHEARINGCASE.CASEID = ACTIVITY.CASEID)) LEFT JOIN (ABSHEARINGSCHEDULE RIGHT JOIN ABSACTIVITYHEARINGSCHEDULELINK ON ABSHEARINGSCHEDULE.ABSHEARINGSCHEDULEID = ABSACTIVITYHEARINGSCHEDULELINK.ABSHEARINGSCHEDULEID) ON ACTIVITY.ACTIVITYID = ABSACTIVITYHEARINGSCHEDULELINK.ACTIVITYID) LEFT JOIN (CASEHEADER LEFT JOIN CONCERNCASEROLE ON CASEHEADER.CASEID = CONCERNCASEROLE.CASEID) ON ABSHEARINGCASE.CASEID = CASEHEADER.CASEID) LEFT JOIN CONCERNROLE AS CONCERNROLE_1 ON CONCERNCASEROLE.CONCERNROLEID = CONCERNROLE_1.CONCERNROLEID
GROUP BY LOCATION.NAME, ABSHEARINGCASE.HEARINGCASEID, CONCERNROLE_1.CONCERNROLENAME, ACTIVITY.STARTDATETIME, ABSHEARINGCASE.HEARINGDATE, ABSHEARINGSCHEDULE.TELEPHONEIND, ABSHEARINGSCHEDULE.CLAIMANTREPONTELEPHONE, CONCERNCASEROLE.TYPECODE
HAVING (((DateValue([ACTIVITY].[STARTDATETIME]))=DateValue('1/27/2012')) AND ((CONCERNCASEROLE.TYPECODE)="CLRP"));
I did use the datevalue function to extract the date part from the field in an odbc linked table
Given below are two different version of the queries that tried. Could someone please point out what I am doing wrong.
SELECT LOCATION.NAME, ABSHEARINGCASE.HEARINGCASEID, CONCERNROLE_1.CONCERNROLENAME, ACTIVITY.STARTDATETIME, DateValue([ACTIVITY].[STARTDATETIME]) AS Expr1, ABSHEARINGCASE.HEARINGDATE, ABSHEARINGSCHEDULE.TELEPHONEIND, ABSHEARINGSCHEDULE.CLAIMANTREPONTELEPHONE
FROM ((((ABSHEARINGCASE LEFT JOIN LOCATION ON ABSHEARINGCASE.HEARINGLOCATIONCODE = LOCATION.LOCATIONID) LEFT JOIN ACTIVITY ON (ABSHEARINGCASE.HEARINGDATE = ACTIVITY.STARTDATETIME) AND (ABSHEARINGCASE.CASEID = ACTIVITY.CASEID)) LEFT JOIN (ABSHEARINGSCHEDULE RIGHT JOIN ABSACTIVITYHEARINGSCHEDULELINK ON ABSHEARINGSCHEDULE.ABSHEARINGSCHEDULEID = ABSACTIVITYHEARINGSCHEDULELINK.ABSHEARINGSCHEDULEID) ON ACTIVITY.ACTIVITYID = ABSACTIVITYHEARINGSCHEDULELINK.ACTIVITYID) LEFT JOIN (CASEHEADER LEFT JOIN CONCERNCASEROLE ON CASEHEADER.CASEID = CONCERNCASEROLE.CASEID) ON ABSHEARINGCASE.CASEID = CASEHEADER.CASEID) LEFT JOIN CONCERNROLE AS CONCERNROLE_1 ON CONCERNCASEROLE.CONCERNROLEID = CONCERNROLE_1.CONCERNROLEID
GROUP BY LOCATION.NAME, ABSHEARINGCASE.HEARINGCASEID, CONCERNROLE_1.CONCERNROLENAME, ACTIVITY.STARTDATETIME, ABSHEARINGCASE.HEARINGDATE, ABSHEARINGSCHEDULE.TELEPHONEIND, ABSHEARINGSCHEDULE.CLAIMANTREPONTELEPHONE, CONCERNCASEROLE.TYPECODE
HAVING (((DateValue([ACTIVITY].[STARTDATETIME]))=#1/27/2012#) AND ((CONCERNCASEROLE.TYPECODE)="CLRP"));
SELECT LOCATION.NAME, ABSHEARINGCASE.HEARINGCASEID, CONCERNROLE_1.CONCERNROLENAME, ACTIVITY.STARTDATETIME, DateValue([ACTIVITY].[STARTDATETIME]) AS Expr1, ABSHEARINGCASE.HEARINGDATE, ABSHEARINGSCHEDULE.TELEPHONEIND, ABSHEARINGSCHEDULE.CLAIMANTREPONTELEPHONE
FROM ((((ABSHEARINGCASE LEFT JOIN LOCATION ON ABSHEARINGCASE.HEARINGLOCATIONCODE = LOCATION.LOCATIONID) LEFT JOIN ACTIVITY ON (ABSHEARINGCASE.HEARINGDATE = ACTIVITY.STARTDATETIME) AND (ABSHEARINGCASE.CASEID = ACTIVITY.CASEID)) LEFT JOIN (ABSHEARINGSCHEDULE RIGHT JOIN ABSACTIVITYHEARINGSCHEDULELINK ON ABSHEARINGSCHEDULE.ABSHEARINGSCHEDULEID = ABSACTIVITYHEARINGSCHEDULELINK.ABSHEARINGSCHEDULEID) ON ACTIVITY.ACTIVITYID = ABSACTIVITYHEARINGSCHEDULELINK.ACTIVITYID) LEFT JOIN (CASEHEADER LEFT JOIN CONCERNCASEROLE ON CASEHEADER.CASEID = CONCERNCASEROLE.CASEID) ON ABSHEARINGCASE.CASEID = CASEHEADER.CASEID) LEFT JOIN CONCERNROLE AS CONCERNROLE_1 ON CONCERNCASEROLE.CONCERNROLEID = CONCERNROLE_1.CONCERNROLEID
GROUP BY LOCATION.NAME, ABSHEARINGCASE.HEARINGCASEID, CONCERNROLE_1.CONCERNROLENAME, ACTIVITY.STARTDATETIME, ABSHEARINGCASE.HEARINGDATE, ABSHEARINGSCHEDULE.TELEPHONEIND, ABSHEARINGSCHEDULE.CLAIMANTREPONTELEPHONE, CONCERNCASEROLE.TYPECODE
HAVING (((DateValue([ACTIVITY].[STARTDATETIME]))=DateValue('1/27/2012')) AND ((CONCERNCASEROLE.TYPECODE)="CLRP"));