Results 1 to 3 of 3
  1. #1
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Compile Errors (MS Access 97)

    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.

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: Compile Errors (MS Access 97)

    This scents of the notorious "missing reference" problem.

    In any module, Select Tools/References... If any entry reads MISSING: ..., unselect it. Next, try to compile everything again.

  3. #3
    2 Star Lounger
    Join Date
    Nov 2001
    Location
    London, UK, England
    Posts
    116
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Compile Errors (MS Access 97)

    I opened a temporary module in the database and noticed that the..

    Option Compare Database
    Option Explicit

    ...statements were duplicated - Removed & it runs fine. Thanks, didn't know this could affect queries...

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •