Problem with SQL statement using CurrentDb.Execute

[matt753]

Can anyone tell me whats wrong with this SQL statement?

CurrentDb.Execute("INSERT INTO Joblist2Data (ID, DayUsed, Employee, Priority, Item, Task, Comments, Complete) VALUES (" & Null & ", #" & 5 / 27 / 10 & "#, " & cmbTechnician.Value & ", " & Null & ", " & Null & ", " & txtOrderID.Value & ", " & comments & ", " & False & ");")

I have the Err.Description going out to a messagebox and it says "Error in INSERT INTO statement"

The first field is an autonumber, should I be putting a Null into it?

[colintis]

you don't need to include ID in your insert values like this

CurrentDb.Execute("INSERT INTO Joblist2Data (DayUsed, Employee, Priority, Item, Task, Comments, Complete) VALUES ( #" & 5 / 27 / 10 & "#, " & cmbTechnician.Value & ", " & Null & ", " & Null & ", " & txtOrderID.Value & ", " & comments & ", " & False & ");")

As in your code it is setting ID to a null instead of letting it make the auto numbers.

[patjones]

By assigning the SQL to a string first, you can use Debug.Print strSQL to view the whole thing in the Immediate Window to see how it came together after the concatenations. You don't need to put the Null value or date outside the string.

That being said, you could try this, but...

Dim strSQL As String
strSQL = "INSERT INTO Joblist2Data (ID, DayUsed, Employee, Priority, Item, Task, Comments, Complete) VALUES (Null, #5/27/10#, " & cmbTechnician.Value & ", Null, Null, " & txtOrderID.Value & ", " & comments & ", 0)"
CurrentDb.Execute(strSQL)

...I think the problem stems from trying to insert a Null into the ID field. When I tried it in my test table, it worked fine when I put a number in, but errored out on the Null.

And actually, you should make every effort to put a sensible value in for the record ID. I personally never use AutoNumbers, and don't encourage their use for ID fields (or anything else really) because, in my opinion, people should strive to tie the ID to something real.

If your situation bars that possibility however, I would at least make the field a Number in table design view, and then assign it some actual number in the SQL above, using a scheme that makes sense to you.

If you really feel like you want to keep the AutoNumber, perhaps someone else has some insight for you...

* * * * *
EDIT
* * * * *

As colintis says, letting Access pick the AutoNumber will work, which you can do by removing the ID field as such:

strSQL = "INSERT INTO Joblist2Data (DayUsed, Employee, Priority, Item, Task, Comments, Complete) VALUES (#5/27/10#, " & cmbTechnician.Value & ", Null, Null, " & txtOrderID.Value & ", " & comments & ", 0)"

However, my opinion on the use of the AutoNumber still stands. Also, I'm not sure what the data types on Priority and Item are, but you might need to watch out for problems with the Null values there as well.

Pat

[NeoPa]

Colintis has hit the nail on the head. You cannot set values into AutoNumber fields (for fairly obvious reasons). When creating such records you must leave those fields out of the list (just as he said).

PS. That wasn't a comment on Pat's post as that was not visible to me when I posted.

[matt753]

Thanks guys,

That makes sense about the autonumber, I just didnt think to not put the field in the list at all.

I tried the Debug.Print strSQL but all I got was a blank textbox that shows up

Trying it again with this code:

CurrentDb.Execute ("INSERT INTO Joblist2Data (DayUsed, Employee, Priority, Item, Task, Comments, Complete) VALUES ( " & "#" & 5 / 28 / 2010 & "# ," & cmbTechnician.Value & ", " & 1 & ", " & 1 & ", " & txtOrderID.Value & ", " & Comments & ", " & 0 & ");")

I dont get the error with INSERT INTO statement, but I get the following: "Syntax error in date in query expression '#8.88415067519 545E-05#'."

I also tried this for the date, which is how I want it to be in the end inputting the current date, I just hardcoded it cause I was getting those errors:

Format(Date, "mm/dd/yy")

Looks like the error is just coming from the date portion, any ideas whats causing it?

[patjones]

Thanks guys,

That makes sense about the autonumber, I just didnt think to not put the field in the list at all.

I tried the Debug.Print strSQL but all I got was a blank textbox that shows up

Trying it again with this code:

CurrentDb.Execute ("INSERT INTO Joblist2Data (DayUsed, Employee, Priority, Item, Task, Comments, Complete) VALUES ( " & "#" & 5 / 28 / 2010 & "# ," & cmbTechnician.Value & ", " & 1 & ", " & 1 & ", " & txtOrderID.Value & ", " & Comments & ", " & 0 & ");")

I dont get the error with INSERT INTO statement, but I get the following: "Syntax error in date in query expression '#8.88415067519 545E-05#'."

I also tried this for the date, which is how I want it to be in the end inputting the current date, I just hardcoded it cause I was getting those errors:

Format(Date, "mm/dd/yy")

Looks like the error is just coming from the date portion, any ideas whats causing it?

You need to put the date inside the string. When you put it outside the string VBA is interpreting it literally...which means that it is probably doing the division operations indicated by the "/" symbol to give you the strange number you see in the error message.

Pat

[patjones]

Alternatively, you could bring the #'s outside the string to surround the date. Either way, you have to make VBA see it as a date and not some other kind of expression.

Pat

[matt753]

Ok, so this is what it looks like now:

CurrentDb.Execute ("INSERT INTO Joblist2Data (DayUsed, Employee, Priority, Item, Task, Comments, Complete) VALUES ( " & #5/28/2010# & "," & cmbTechnician.Value & ", " & 1 & ", " & 1 & ", " & txtOrderID.Value & ", " & Comments & ", " & 0 & ");")

But I get an error saying to few parameters. Expected 1

Or

If I put in Nulls for the two "1's" for priority and task, I get the error in INSERT INTO statement. Both fields are text

[patjones]

When you are inserting actual numbers, you don't need to put them outside the string:

CurrentDb.Execute ("INSERT INTO Joblist2Data (DayUsed, Employee, Priority, Item, Task, Comments, Complete) VALUES ( " & #5/28/2010# & ", " & cmbTechnician.Value & ", 1, 1, " & txtOrderID.Value & ", " & Comments & ", 0)")

However, these are text fields, so you would need to enclose the numbers in ' ' symbols. So the above line won't work. If you want nulls, they need to go inside the string as such:

CurrentDb.Execute ("INSERT INTO Joblist2Data (DayUsed, Employee, Priority, Item, Task, Comments, Complete) VALUES ( " & #5/28/2010# & ", " & cmbTechnician.Value & ", Null, Null, " & txtOrderID.Value & ", " & Comments & ", 0)")

Pat

[patjones]

Ok, so this is what it looks like now:

CurrentDb.Execute ("INSERT INTO Joblist2Data (DayUsed, Employee, Priority, Item, Task, Comments, Complete) VALUES ( " & #5/28/2010# & "," & cmbTechnician.Value & ", " & 1 & ", " & 1 & ", " & txtOrderID.Value & ", " & Comments & ", " & 0 & ");")

But I get an error saying to few parameters. Expected 1

Or

If I put in Nulls for the two "1's" for priority and task, I get the error in INSERT INTO statement. Both fields are text

See my edit to my last post.

[matt753]

Oh ok I understand now.

I was getting an error with the Comments part, its a string variable thats entered in a messagebox right before that execute happens. It doesnt show up anymore, but I can only input numbers as comments otherwise it doesnt work.

Also, the date seems to always use 12/30/1899 for some reason. Even when hardcoded in for any date.

[patjones]

Well, try bringing the date back inside the string; also, the comments need to be enclosed in ' ' ...

CurrentDb.Execute ("INSERT INTO Joblist2Data (DayUsed, Employee, Priority, Item, Task, Comments, Complete) VALUES ( #5/28/2010#, " & cmbTechnician.Value & ", Null, Null, " & txtOrderID.Value & ", '" & Comments & "', 0)")

Sorry I didn't notice that you didn't have the comments enclosed in apostrophes previously. The thing with bringing the date inside the string will work - but if you ever want to insert a date from say a text box, or even the current date - which is Date() in VBA, you'll need to put it outside the string the way you have done with cmbTechnician.V alue.

Generally, the rule is that numbers can be inserted as is, text values need to be wrapped in ' ', and date values need to be wrapped in # #. If what you are inserting is a Null, or hardcoded value (i.e. just a straight number or text value or date), it doesn't need to be outside the string. But if it's coming from your form, or is a variable from somewhere else in your code, it does need to be outside the string, as you have done with cmbTechnician and txtOrderID.

I hope this helps, and sorry if it's been a little roundabout.

Pat

[matt753]

Thanks I got it all working 100%. The syntax was a little tricky but that last post explained everything very well.

Here the final code I used, for anyone who might refer to this in the future:

CurrentDb.Execute ("INSERT INTO Joblist2Data (DayUsed, Employee, Priority, Item, Task, Comments, Complete) VALUES ( " & "#" & Format(Date, "mm/dd/yy") & "#, '" & Employee & "', Null, Null, '" & strTask & "', '" & Comments & "', 0)")

Thanks again

[NeoPa]

Pat's post is very helpful, but there are a few minor omissions from it that will generally not cause any problems, but they can in certain circumstances. For the full info on these issues see Literal DateTimes and Their Delimiters (#) and Quotes (') and Double-Quotes (") - Where and When to use them.

Literal dates should always be formatted as #m/d/yy#. This is a SQL standard and does not depend on locality. Also, SQL has access to a Date() function too, so this can also be used within a string, or outside of it as a resolved literal as Pat suggested.

All that said, it is very important, if you are working in SQL from within your VBA code, that you get to grips with the techniques for displaying and debugging your SQL code (I'll include indented instructions below for your assistance). Using this, you can get to the point where you have the experience not to need it any more. Without it that will be unlikely.

One of the most popular (frequently occurring rather than best liked) problems we get is with SQL strings being manipulated in VBA code.

The reason this is so difficult is that all the work is being done at a level of redirection. What I mean by this is that the coder is never working directly with the SQL itself, but rather with code which in turn, is relied on to produce the SQL that they are envisaging is required. It's rather similar to the problems coders have historically had dealing with pointers.

Anyway, a technique I often suggest to coders struggling with this (at any level. This happens to experienced coders too.) is to use either the MsgBox() function, or Debug.Print into the Immediate Pane of the debugger window, to display the value of the SQL in the string before using it (That's assuming you're not good with debugging generally. Personally I would trace to the line, then display the value prior to allowing execution of the string - See Debugging in VBA). It's really much easier to appreciate what a SQL string is meant to do, and where there may be problems, when you can see it in its entirety, and in its true form, rather than as the code is about to create it.

Good luck with your SQL Matt.