INSERT INTO help for text and date

[cstickman]

Hello Everyone,

I need some assistance and I am sure I am just missing it. I have a table with a field1 and data type of text, then field 2 is data type date/time. I know we need to use the # for date fields. Here is my code below

<code>
Currentdb.Execute "INSERT INTO test(field1, field2) " & _
" VALUES('" & Me.textbox1 & "','" & Me.textbox2 & "#, "')"
</code>

I keep getting an error syntax error in string in query expression "12/18/1976#,'.

12/18/1976 is the date I was using to see if it worked. So I do know that I am not ending the # correctly. Any help would be greatly appreciated. Thanks

 

[Joe4]

Here is a little hint. Write your SQL statements as Strings. Then you can easily use Message Boxes to verify that you have written it correctly.
As you have written it, you are missing the # in front of the date (you are using a single quote).
Try this:

Dim strSQL as String
strSQL="INSERT INTO test(field1, field2) " & _
" VALUES('" & Me.textbox1 & "',#" & Me.textbox2 & "#)"

' Just use this to verify your SQL string looks good
MsgBox strSQL

Currentdb.Execute strSQL

 

[cstickman]

Thanks Joe!! I knew I was doing something wrong. It is times you just look at it over and over and pull your hair out.

I agree, and I will move to SQL statements as Strings.

 

[cstickman]

I have another INSERT INTO problem. I guess I am not understanding how to start a date/time, number and text and terminating it correctly. Here is what I have, it will go column name and data type. stream - text, vendor - text, loan1 - number, loan2 - number, fname - text, lname - text, balance - currency, settlement - currency, percentage - number, sstart - date, send - date, sterms - text, approved - text, status - text, closed - text, comments - memo, counter - text, settled - text, com - date, hqreview - text, hqdate - date, and floor - text. Below is my code and I am getting a compile error right after Me.txtsstart. Any help in letting me know what I am doing wrong would be greatly appreciated. Thanks

<code>
Dim strSQL As String

strSQL = "INSERT INTO settlement(stream, vendor, loan1, loan2, fname, lname, balance, settlement, " & _
"percentage, sstart, send, sterms, approved, status, closed, comments, " & _
"counter, settled, com, hqreview, hqdate, floor) " & _
" VALUES('" & Me.cmbvendor & "','" & Me.cmbstream & "','" & Me.txtloan1 & "," & Me.txtloan2 & "," & Me.txtfname & "','" & _
Me.txtlname & "','" & Me.txtbalance & "','" & Me.txtsettlement & "','" & Me.txtpercentage & "," & _
Me.txtsstart "#,'" Me.txtsend & "#,'" & Me.txtsterms & "',#" & Me.cmbapproved & "','" & me.cmbstatus & "','" & Me.txtreason & "','" & _
Me.txtcomment & "','" & Me.cmbcounter & "','" & Me.cmbsettle & "',#" & Me.txtcom & "#,'" & me.cmbhqreview & "','" & _
Me.txthqdate & "',#" & Me.cmbfloor & "#)"

CurrentDb.Execute strSQL

</code>

 

[Joe4]

Here is what you need to remember:

- every date entry needs to be surrounded by hash tags (these are your date qualifiers), i.e. #6/10/2015#
- every text entry needs to be surrounded by single quotes (these are your text qualifiers), i.e. 'John Smith'
- numeric entries do not need qualifiers, i.e. 500
- each field is separated by a comma, i.e. #6/10/2015#, 'John Smith', 500

So as you are building your string, in between each field, you have something that looks like this:
... Me.TextField1 & "',#" & Me.DateField1 ...

So let's break apart what we have in between the concatenate symbols (&):
- since the entire string is a literal value, we have it surrounded by double-quotes "
- the single-quote after the first double-quote is your ending text qualifier for TextField1
- the next character is a comma, that we use to separate the fields
- the next character is a hash tag, which is our starting date qualifier for DateField1

And it repeats, on and on. So each one of these (except for the first and last) are the ending qualifier for the preceding field, and starting qualifier for the next field.

Armed with this knowledge, see if you can go through your problem again and find where the issue is. Remember, you can use "MsgBox strSQL" to visually inspect it. Blatant appears should hopefully be readily apparent.

Also note that string fields can only hold 255 characters. If your string exceeds like, you might need to break it up into strSQL1 and strSQL2. Then use:
CurrentDb.Execute strSQL1 & strSQL2

 

[cstickman]

Thank you for the explanation. I believe I have the code correct now, but I am receiving the error Run-time error '3134' // Syntax error in INSERT INTO statement. I only have 127 characters in the code. When I click on debug it highlights in yellow CurrentDb.Execute strSQL. Below is the new code

<code>
Dim strSQL As String

strSQL = "INSERT INTO settlement(stream, vendor, loan1, loan2, fname, lname, balance, settlement, " & _
"percentage, sstart, send, sterms, approved, status, closed, comments, " & _
"counter, settled, com, hqreview, hqdate, floor) " & _
" VALUES('" & Me.cmbvendor & "','" & Me.cmbstream & "'," & Me.txtloan1 & "," & Me.txtloan2 & ",'" & Me.txtfname & "','" & _
Me.txtlname & "','" & Me.txtbalance & "','" & Me.txtsettlement & "'," & Me.txtpercentage & ",#" & _
Me.txtsstart & "#,#" & Me.txtsend & "#,'" & Me.txtsterms & "','" & Me.cmbapproved & "','" & Me.cmbstatus & "','" & Me.txtreason & "','" & _
Me.txtcomment & "','" & Me.cmbcounter & "','" & Me.cmbsettle & "',#" & Me.txtcom & "#,'" & Me.cmbhqreview & "',#" & _
Me.txthqdate & "#,'" & Me.cmbfloor & "')"

CurrentDb.Execute strSQL
</code>

 

[Joe4]

I don't see anything that stands out, and without having access to the database, I really cannot say that all the fields are qualified correctly, or all the values from your text boxes are valid.

What I often do in these situations is start small, and build my way up.
That is, first create your SQL statement ONLY populating the required fields. Once you get that to work, add the other fields, one-by-one, and test each instance, until you find where the error is occurring.

You may also want to try running it like this instead:

DoCmd.RunSQL strSQL

I am not sure why, but sometimes I am more successfully getting it to run using that code than the "CurrentDB.Execute" command.

 

[james_lankford]

    Dim strSQL As String
    
    strSQL = ""
    strSQL = strSQL & "INSERT INTO " & vbCrLf
    strSQL = strSQL & "  settlement " & vbCrLf
    strSQL = strSQL & "( " & vbCrLf
    strSQL = strSQL & "  stream, vendor, loan1, loan2, " & vbCrLf
    strSQL = strSQL & "  fname, lname, balance, settlement, " & vbCrLf
    strSQL = strSQL & "  percentage, sstart, send, sterms, " & vbCrLf
    strSQL = strSQL & "  approved, status, closed, comments, " & vbCrLf
    strSQL = strSQL & "  counter, settled, com, hqreview, " & vbCrLf
    strSQL = strSQL & "  hqdate, floor " & vbCrLf
    strSQL = strSQL & ") " & vbCrLf
    strSQL = strSQL & "VALUES " & vbCrLf
    strSQL = strSQL & "( " & vbCrLf
    strSQL = strSQL & "  '" & Me.cmbvendor & "', '" & Me.cmbstream & "', " & Me.txtloan1 & ", " & Me.txtloan2 & ", " & vbCrLf
    strSQL = strSQL & "  '" & Me.txtfname & "', '" & Me.txtlname & "', '" & Me.txtbalance & "', '" & Me.txtsettlement & "', " & vbCrLf
    strSQL = strSQL & "  '" & Me.txtpercentage & ", #" & Me.txtsstart & "#, #" & Me.txtsend & "#, '" & Me.txtsterms & "', " & vbCrLf
    strSQL = strSQL & "  '" & Me.cmbapproved & "', '" & Me.cmbstatus & "', '" & Me.txtreason & "', '" & Me.txtcomment & "', " & vbCrLf
    strSQL = strSQL & "  '" & Me.cmbcounter & "', '" & Me.cmbsettle & "', #" & Me.txtcom & "#, '" & Me.cmbhqreview & "', # " & vbCrLf
    strSQL = strSQL & "  '" & Me.txthqdate & "#, '" & Me.cmbfloor & "' " & vbCrLf
    strSQL = strSQL & ") " & vbCrLf
    
on error resume next 
    
    CurrentDb.Execute strSQL
    
    if err.number <> 0 then 
        debug.print strSQL 
        msgbox strSQL 
        exit sub
    end if

 

[cstickman]

Joe - Thanks for the suggestions. I will go through and check all the data types in the database. So keep the SQL statement with the column names and add a value one at a time? Also the other command to run the SQL did the exact same thing.

James - Thanks for the re-write in the code. So I entered in dummy information and click on add and a box comes up with all my columns and values and I click on ok, but nothing is added to the database. Any suggestions?

Thanks to you both for all of your help with this!!

 

[Joe4]

I mean something like this:

[COLOR=#333333][FONT=monospace]strSQL = "INSERT INTO settlement(stream) " [/FONT][/COLOR][COLOR=#333333][FONT=monospace]& _[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]" VALUES('" & Me.cmbstream & "')"
[/FONT][/COLOR]

So you are only starting off with one (or a few fields - the minimum you need to add a new record, which depends upon which fields are required). Then, if that works, build from there by adding another field and testing, etc, etc.

NOTE: I just noticed something. Make sure that the fields are in the same order in the INSERT INTO and VALUES clauses. Look at the beginning of each in the code you posted:

[COLOR=#574123][FONT=monospace]strSQL = "INSERT INTO settlement([/FONT][/COLOR][COLOR=#ff0000][FONT=monospace]stream, vendor,[/FONT][/COLOR][COLOR=#574123][FONT=monospace] ...
[/FONT][/COLOR]

[COLOR=#574123][FONT=monospace]" VALUES('" & [/FONT][/COLOR][COLOR=#ff0000][FONT=monospace]Me.cmbvendor[/FONT][/COLOR][COLOR=#574123][FONT=monospace] & "','" & [/FONT][/COLOR][COLOR=#ff0000][FONT=monospace]Me.cmbstream[/FONT][/COLOR][COLOR=#574123][FONT=monospace] ...[/FONT][/COLOR]

It appears to me that you may have the order switched (which is first, stream or vendor?)
These need to be in agreement with each other.