Problem with INSERT INTO query code

[rghollenbeck]

Hello Forum,



I'm building a new "grades" program in Access 2007 for an English instructor at a local community college. The opening form has a combo box to select an existing course (I call it a "course" to avoid confusion with the programming term "class") or create a new course. If the user selects to create a new course, a new unbound form opens with three unbound text boxes to gather the needed data: the course code, the course description, and the start date. The code for the button to append a row to the tblCourses table is as follows:

Private Sub cmdCreateCourse_Click()

Dim rcc As Integer
Dim description As String
Dim start As Date

rcc = Me!txtRCC_ID
description = Me!txtCourseName
start = Me!txtStartDate

Dim StrSQL As String
StrSQL = "INSERT INTO tblCourses ( RCC_ID, CourseDescription, StartDate) "
StrSQL = StrSQL & "VALUES (" & rcc & ", " & description & ", " & start & ")"

' the message box below is for testing purposes and is commented out
' However, when it is enabled, the message box does properly display the 
' query with the correct values in it.
' msgbox StrSQL

DoCmd.RunSQL StrSQL

End Sub

This creates a Run-time error '3075': Syntax error (missing operator) in query expression.

I cannot see anything wrong with my query. I tried it both with and without the trailing semicolon, but that made no difference.

Any ideas? Thanks.

[Robeen]

What are the data types of the three different fields into which you are inserting your data?

[rghollenbeck]

What are the data types of the three different fields into which you are inserting your data?

RCC_ID is a long integer
CourseDescription is a string
Start Date is a Date/Time field

Thank you!

[Robeen]

I will try & get this going in a test DB I have & let you know what the problem is.

It is probably how you're using your " marks.
If you do a
MsgBox StrSQL
before your DoCmd.RunSQL - you will be able to see the SQL string that you are creating and perhaps spot your error.

I will look at it as soon as I get a chance.

[rghollenbeck]

I will try & get this going in a test DB I have & let you know what the problem is.

It is probably how you're using your " marks.
If you do a
MsgBox StrSQL
before your DoCmd.RunSQL - you will be able to see the SQL string that you are creating and perhaps spot your error.

I will look at it as soon as I get a chance.

Thank you!

In the mean time, I'll experiment with using some combinations of quotation marks apostrophes. If I get it fixed before you get back to me, I'll post it here. That way, I won't be just sitting around waiting for you to solve it for me. If I want to continue to get any help, I had better do my best to solve it by myself too.

I really appreciate your help

[rghollenbeck]

I fixed it! CourseCode is a string, so I had to add apostrophes (single quotes) around the variable like this:

"VALUES (" & rcc & ", '" & description & "', " & start & ")"

Thank you!

Sometimes, just figuring out the problem enough to explain it so somebody, the answer comes to me. I just remembered that strings need to be displayed in a single quote.

[Robeen]

Are you getting a selected date put into your start field? Or are you just getting a default date?

Here's the code I used to make it work [I couched start in #s].
I also changed a couple of field names.

 
Dim rcc As Integer
Dim description As String
Dim start As Date
rcc = Me!RCC_ID
 
description = Me!CourseDescription
start = Me!StartDate
 
Dim StrSQL As String
StrSQL = "INSERT INTO tblCourses (RCC_ID, CourseDescription, StartDate) "
StrSQL = StrSQL & "VALUES (" & rcc & ", " & "'" & description & "'" & ", " & "#" & start & "#" & "); " 
 
' the message box below is for testing purposes and is commented out
' However, when it is enabled, the message box does properly display the
' query with the correct values in it.
 'MsgBox StrSQL
DoCmd.RunSQL StrSQL

[rghollenbeck]

Thanks! The pound signs is a good idea. The query DID place the date in the field, but it was the wrong date. I want the start date for a semester, so I won't know in advance what the date will be until the school calendar comes out each year. The record said 12/30/1899, instead of the date I wanted. I added do the pound signs like you said. That did correct the problem.

[Robeen]

Glad I could help!!