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

 

[cstickman]

So I was playing around with the form and adding information and discovered that it is making me fill in all the fields except for loan2. All of them have to be filled out before it will write the data to the database. Can we change this somehow? At times when entering in a new order not all fields will be added until later on during the order process. I did have to add another field to the database and updated the code and I am not sure if that caused anything. Below is the new code:

<code>
Dim strSQL As String

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

</code>

On the percentage field I changed it to number and single digit to get the percentage to work correctly. So I am not sure if that is causing it. Thanks

 

[Joe4]

All of them have to be filled out before it will write the data to the database. Can we change this somehow?

Where does this table reside? Is it in Access or SQL (or something else)?
You need to look at the underlying table structure (of the "settlement" table) and see which fields are required and if they accept/allow zero length strings.
If they are required and do not allow zero length strings, those fields need values whenever a record is inserted.

 

[cstickman]

So I went through and did what you suggested on an earlier post and did each column / text field one by one. It became an issue with the date field. I believe because in the sql string it is adding ## which is not the format of the date field. So I can leave all fields blank minus the date fields. Do you have suggestions around this? I have 4 date fields and two of them can be updated at a later time. Thanks

 

[Joe4]

It all depends on what the answer to my first question in my previous post is.
If it is an Access table, the "#" should work. But you may need to check your Microsoft Regional settings to see how your dates need to be entered.

 

[cstickman]

It is a linked database from access 2013. How do I check my regional settings?

 

[Joe4]

See: https://support.office.com/en-za/ar...ta-types-edf41006-f6e2-4360-bc1b-30e9e8a54989

Note, if it is Access, and your date fields are of a Date data type, the "#" should work fine. I tested it myself and it worked.

 

[cstickman]

I checked my regional settings and I am using the correct format. In my access table they have date/time as the data type. In the text field itself I have under format Short Date. Anywhere I could look to make sure it is the correct options chosen? Thanks

 

[Joe4]

If you run it to a MsgBox, what does it show for the dates you are building or are trying to Insert?
Make sure that there aren't any blank, so you are trying to insert anything like ## with nothing in between.

 

[cstickman]

Thanks!! Once again you have figured it out. The SQL was trying to add ## when no value was entered. Once I changed it we are back in business. Thanks