I can’t find syntax error (Access03)

Topic

New Reply

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()))”

Reply | Quote

Replies

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.

Reply | Quote

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

Reply | Quote

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()))”

Reply | Quote

Could you attach a stripped down copy of your database?

Reply | Quote

You don’t appear to have tblSession in your FROM clause.

Reply | Quote

Oops – good catch!

Reply | Quote

Fixed it.

Reply | Quote