This problem has just started happening – I’ve got a project in VB that is generating SQL statements to run over an Access database. The querys run fine in VB (the program opens recordsets using the SQL) but when I try and copy the VB SQL Statements into Access I receive compile error messages over any fields that are user designed?
Here is an example of the SQL
SELECT CampUnits.CodaUpdated, CampUnits.ReportYear, CampUnits.ReportMonth, Format([CodaUpdated],”dd/mm/yy”) AS DateUpdated
FROM (((Campaign
INNER JOIN CampUnits ON Campaign.CampID = CampUnits.CampID)
INNER JOIN SMR ON CampUnits.SMRID = SMR.SMR_ID)
INNER JOIN SMRActivities ON CampUnits.UnitActivity = SMRActivities.Activity_ID)
INNER JOIN VATCodes ON SMR.VAT_Code = VATCodes.VATCode
GROUP BY CampUnits.CodaUpdated, CampUnits.ReportYear, CampUnits.ReportMonth, CampUnits.Status, CampUnits.CodaUpdated, CampUnits.POSentOn, Format([CodaUpdated],”dd/mm/yy”)
HAVING (((CampUnits.Status)=7 Or (CampUnits.Status)=8 Or (CampUnits.Status)=255) AND ((CampUnits.POSentOn) Is Not Null));
So, it’s the Format([CodaUpdated], “dd/mm/yy”) section that causes the error, but I can’t see anything wrong with this. And why does it run in VB but not in Access?! Is there a setting in Access that could start causing compile errors?
Very strange – any ideas appreciated.
