I have the following query in access which works fine on my computer but when i package it into an accdr with access 2007 runtime i get "Error 3075: Function is not available in expressions in query expression"
Any ideas?
Any ideas?
Code:
SELECT fe.functions AS who, e.trxtimedate AS clockoff, e.trxtillid AS till, Round((e.trxtimedate-
(SELECT MAX(s.trxtimedate)
FROM
tblfunctions fs
,tblsales s
WHERE
fs.salesid = s.salesid
AND fs.functions = fe.functions
AND fs.keytype = 65529
AND s.trxtimedate < e.trxtimedate))*24,2) AS hours, areatbl.area_desc AS area, tilltbl.till_desc AS REGISTER, cashtbl.cash_first_name AS FIRSTNAME, cashtbl.cash_surname AS SURNAME, (SELECT MAX(s.trxtimedate)
FROM
tblfunctions fs
,tblsales s
WHERE
fs.salesid = s.salesid
AND fs.functions = fe.functions
AND fs.keytype = 65529
AND s.trxtimedate < e.trxtimedate) AS clockon, Format(Weekday(Format((
SELECT
MAX(s.trxtimedate)
FROM
tblfunctions fs
,tblsales s
WHERE
fs.salesid = s.salesid
AND fs.functions = fe.functions
AND fs.keytype = 65529
AND s.trxtimedate < e.trxtimedate),"dd/mm/yyyy")),"dddd") AS CLOCKONDATE, Weekday((SELECT
MAX(s.trxtimedate)
FROM
tblfunctions fs
,tblsales s
WHERE
fs.salesid = s.salesid
AND fs.functions = fe.functions
AND fs.keytype = 65529
AND s.trxtimedate < e.trxtimedate)) AS DAYNUMBER, areatbl.AREA_NUMBER INTO TBLTIMESHEETTEMP
FROM tblfunctions AS fe, tblsales AS e, areatbl, tilltbl, cashtbl
WHERE (((e.trxtimedate) Between [Forms]![frmTIMESHEET]![txtDATEFROM] And [Forms]![frmTIMESHEET]![txtDATETO])
AND (((SELECT
MAX(s.trxtimedate)
FROM
tblfunctions fs
,tblsales s
WHERE
fs.salesid = s.salesid
AND fs.functions = fe.functions
AND fs.keytype = 65529
AND s.trxtimedate < e.trxtimedate)) Between [Forms]![frmTIMESHEET]![txtDATEFROM] And [Forms]![frmTIMESHEET]![txtDATETO])
AND ((areatbl.AREA_NUMBER)=[tilltbl].[till_area_number])
AND ((fe.keytype)=65530)
AND ((fe.salesid)=[e].[salesid])
AND ((CInt([tilltbl].[till_number]))=[e].[trxtillid])
AND ((cashtbl.cash_number)=Cdbl([fe].[functions])))
ORDER BY e.trxtimedate;
