SQL troubleshooting (Access03)

Topic

New Reply

I have the following code that is suppose to append three fields from the tblCertificationsDates to tblCombinedCerts. The strWhere compiles what classes I want selected. I run a make table SQL query before this code to capture data from another table and the strWhere works properly.

‘ SQL for append query
strSQL = “INSERT INTO tblCombinedCerts ( LearnerID, ClassName, DateOfClassStart )” & _
“SELECT LearnerID, Type, Date” & _
“FROM tblCertificationDates WHERE ” & strWhere
‘ Execute it
DoCmd.RunSQL strSQL

Which gives me a 3075 runtime error code: “Syntax error (missing operator) in query.” I am just starting to use more SQL in my work and I don’t see the error. I actually created the query in the QBE view then switched to SQL and copied that to VBA and added the line breaks. It stops at the DoCmd.

Any help and teaching is appreciated. Thank you. Fay

Reply | Quote

Replies

One problem you have is a missing space between Date and FROM when the string is concatenated. That is typically the kind of thing that gives you the missing operator error.

Date” & _
“FROM

try

Date ” & _
“FROM

Reply | Quote

Okay that worked, but….

I now get a parameter box asking for ClassName

With the above code I am inserting Type into ClassName and Date into DateOfClassStart. The date isn’t objected to but nothing is appended to first table. Here is the two queries as they stand now.

‘ SQL for make-table query
strSQL = ” SELECT tblRegistrationLearner.LearnerID, tblClasses.ClassName, ” & _
“tblRegistrationLearner.ClassNumber, tblRegistrationLearner.ClassID, ” & _
“tblRegistrationLearner.CancelledNoShow, tblRegistrationLearner.DateTimeRegistered, ” & _
“tblSession.DateOfClassStart,tblRegistrationLearner.ISDateOfClassStart, ” & _
“tblRegistrationLearner.Grade ” & _
“INTO tblCombinedCerts FROM (tblClasses INNER JOIN tblSession ON ” & _
“tblClasses.ClassID = tblSession.ClassID) INNER JOIN tblRegistrationLearner ON ” & _
“tblSession.ClassNumber = tblRegistrationLearner.ClassNumber WHERE ” & strWhere
‘ Execute it
DoCmd.RunSQL strSQL

‘ SQL for append query
strSQL = “INSERT INTO tblCombinedCerts ( LearnerID, ClassName, DateOfClassStart )” & _
“SELECT LearnerID, Type, Date ” & _
“FROM tblCertificationDates WHERE ” & strWhere
‘ Execute it
DoCmd.RunSQL strSQL

Thank you for the help. Fay

Reply | Quote

If you open tblCombinedCerts in design view after running the make table query, is ClassName a field name, or something like tblClasses_ClassName?

Reply | Quote

It is ClassName as it should be. Thanks. Fay

Reply | Quote

What if you use

strSQL = “INSERT INTO tblCombinedCerts ( LearnerID, ClassName, DateOfClassStart ) ” & _
“SELECT LearnerID, Type AS ClassName, [Date] AS DateOfClassStart ” & _
“FROM tblCertificationDates WHERE ” & strWhere

Reply | Quote

That didn’t work, same problem. I also put [ ]s around Type and that didn’t work. Thanks Fay

Reply | Quote

I’m afraid we can’t solve this without seeing a stripped down copy of the database.

Reply | Quote

I will send a stripped down version as soon as I can. I did take the code and placed it in a query removing the VBA extraneous material. It runs like it should. Hummm.

Fay

Reply | Quote

Here is the stripped down version. Thanks for your help. Fay

Reply | Quote

Aha. Your strWhere refers to ClassName, but that is not a field name in tblCertificationDates. This is easily remedied: insert the following line in the Certifications function AFTER running the make table query, but BEFORE running the append query:

strWhere = Replace(strWhere, “ClassName”, “Type”)

This will change the field name ClassName in strWhere to Type.

Reply | Quote

Okay I no longer get the parameter box. But if I select all of the departments, credentials, and the BLS* classes only one record is appended. 42 records should have been appended. The BLS renewal class was the only one appended and there is only one of those in the tblCertificationDates. So I am thinking there is an issue with the strWhere statement.

I thought it may have related to the fact DateOfClassStart wasn’t in the tblCertificationDates table so I added that and it didn’t work.

Any ideas? You got me in over my head again. Fay

Reply | Quote

The make-table query creates 55 records; the append query only one because the only Type in tblCertificationDates that corresponds to an item in the ClassPicker list box is “BLS Healthcare Provider Renewal”. There are no records with Type equal to “BLS for Healthcare Providers” or “BLS Instructor Renewal Course”.

Reply | Quote

The disconnect was that the name for the classes was slightly different. Have corrected the tblCertificationsDates and it is working correctly. Need to do a little house cleaning in the background. Thank you.

Fay

Reply | Quote

How do I set Max date in SQL?
I tried AS MaxOf

strSQL = “SELECT * ” & _
“INTO tblCombinedCertsDidDidnt ” & _
“FROM tblCombinedCerts ” & _
“AS MaxOfDateOfClassStart ” & strWhere

Thank you for your help. Fay

Reply | Quote

It depends on what exactly you want to accomplish. Will tblCombinedCertsDidDidnt only contain MaxOfDateOfClassStart or other fields too? And do you want to group on another field or not?

Added: Tip: try to create the query in design view first, then look at SQL view.

Reply | Quote

I just got it to work. We had previously worked together on a database to show max dates using a series of three queries. I tried that and used the third query as part of the query for the final report. Even though I had set the join to the second type I couldn’t get the report to show staff member names who didn’t have a current class. I had just attached a different message to this thread and then tried for the 10th time. Glorie be it worked. Why does it work now, only God knows.

Now I will ask you for the basic approach to doing the same thing with SQL if you have the time. Whew I feel like I just won the marathon. Thanks. Fay

Reply | Quote

As I already noted, it’s best to look at the queries you designed. If you switch to SQL view, you’ll see the SQL “code”. You can use this when trying to develop VBA code.

Reply | Quote

I pulled the earlier post because I got it to work.

I looked at the SQL behind the three queries. Would I have to create three different SQL statements like I had to do with the queries in VBA to get where I needed to go? Do you have any example that I could look at for the next time?

Thanks Fay

Reply | Quote

You cannot easily use code to provide the equivalent of the three queries, because they build on each other. It’s better to work with stored queries here.

Working with SQL in code is usually highly specific, it is not really possible to give a general recipe.

Reply | Quote

It is facts like the above if you don’t know ends up giving you head trauma when you try to do the impossible.

Thanks. Fay

Reply | Quote

What happened to your next post? I was composing a reply and suddenly there was nothing to reply to.

Reply | Quote