Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    I can't find syntax error (Access03)

    I get the following Runtime error Invalid bracketing of name '[tblSessions.DateOfClassStart]',
    I can't find the error after multiple tries. I know it is head slapping simple, but.... Thank you. Fay

    strSQL = "INSERT INTO tblCombRequiredCerts ( Renewed, Due, LearnerID, ClassName, DateOfClassStart )" & _
    "SELECT tblCertificationTypes.Renewed, " & _
    "DateSerial(Year([tblCertificationDates.Date])+([Renewed]),Month([tblCertificationDates.Date]),Day([tblCertificationDates.Date])) " & _
    "AS Due, tblCertificationDates.LearnerID, tblCertificationDates.Type, tblCertificationDates.Date " & _
    "FROM tblCertificationTypes INNER JOIN tblCertificationDates ON tblCertificationTypes.Certifications = tblCertificationDates.Type " & _
    "WHERE ((([tblSession.DateOfClassStart])<=Now()) AND " & _
    "((DateSerial(Year([tblSession.DateOfClassStart])+([Renewed]),Month([tblSession.DateOfClassStart]),Day([tblSession.DateOfClassStart])))>=Now()))"

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: I can't find syntax error (Access03)

    I can't see what is wrong, but i can tell you how I solve problems like this.

    Put in a debug.print strSQL statement immediately after the line you have displayed. This will write the final sql to the immediate window.

    Run the code, go back to the code edtor, press CTRl +G, then copy the sql.

    Go back to Access, create new query with nothing added, switch to sql view and paste in the sql copied from the immediate window.

    Now try to view the query in the query grid, or try to run it. It is always much easier to find the problem here.

    The go back and amend the code.
    Regards
    John



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

    Re: I can't find syntax error (Access03)

    Try using [tblSession].[DateOfClassStart] and similarly [tblCertificationDates].[Date]. Replace all instances!

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I can't find syntax error (Access03)

    Thanks John haven't tried your method yet. Hans' method looked quicker. I think I did as instructed but got a Enter Parameter Value dialog box. If I click okay get runtime 3464 error message "Data type mismatch in criteria expression. If click cancel get the cancelled message.

    strSQL = "INSERT INTO tblCombRequiredCerts ( Renewed, Due, LearnerID, ClassName, DateOfClassStart )" & _
    "SELECT tblCertificationTypes.Renewed, " & _
    "DateSerial(Year([tblCertificationDates.Date])+([Renewed]),Month([tblCertificationDates.Date]),Day([tblCertificationDates.Date])) " & _
    "AS Due, tblCertificationDates.LearnerID, tblCertificationDates.Type, tblCertificationDates.Date " & _
    "FROM tblCertificationTypes INNER JOIN tblCertificationDates ON tblCertificationTypes.Certifications = tblCertificationDates.Type " & _
    "WHERE ((([tblSession].[DateOfClassStart])<=Now()) AND " & _
    "((DateSerial(Year([tblSession].[DateOfClassStart])+([Renewed]),Month([tblSession].[DateOfClassStart]),Day([tblSession].[DateOfClassStart])))>=Now()))"

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

    Re: I can't find syntax error (Access03)

    Could you attach a stripped down copy of your database?

  6. #6
    WS Lounge VIP rory's Avatar
    Join Date
    Dec 2000
    Location
    Burwash, East Sussex, United Kingdom
    Posts
    6,280
    Thanks
    3
    Thanked 191 Times in 177 Posts

    Re: I can't find syntax error (Access03)

    You don't appear to have tblSession in your FROM clause.
    Regards,
    Rory

    Microsoft MVP - Excel

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

    Re: I can't find syntax error (Access03)

    Oops - good catch!

  8. #8
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: I can't find syntax error (Access03)

    Fixed it.

Posting Permissions

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