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

 

[james_lankford]

...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?...

the box comes up because there was an error

that's what the
if err.number <> 0
does

if the error does not equal zero then display the sql string

I was hoping that seeing and carefully reading the sql string would allow you to see a problem

for example, is a name is
John O'Malley

and you try to insert it by wrapping single quotes around it
'John O'Malley'

then that would fail

so I was hoping you would see something like that
of see a badly formatted date
or see a blank value being inserted when the database doesn't allow blanks
or see something out of order the way Joe said

if you have the code window (the vba module) open when you run try the insert then the sql qill also be written to the debug window

you could copy it and paste it here if it isn't sensitive

 

[cstickman]

Hi Guys

Joe - thanks about the fields and columns matching. I did have it backwards for vendor and stream. I did switch it and still get the same error.

I am going to copy the error I am receiving as this is all just dummy info anyways. Please see below for the error

<code>
INSERT INTO
settlement
(
vendor, stream, loan1, loan2,
fname, lname, balance, settlement,
percentage, sstart, send, sterms,
approved, status, closed, comments,
counter, settled, com, hqreview,
hqdate, floor
)
VALUES
(
‘TEST’, ‘firm’, 111222333444,,
‘Test First’, ‘Test Last’, ‘5000’, ‘2500’,
‘0.5, #06/01/2015#, #06/01/2015#, ‘1 lump sum’,
‘Yes’, ‘Open’, ‘Pending’, ‘pending’,
‘No’, ‘No’, #06/01/2015#, ‘Yes’, #
‘06/01/2015#, ‘Below’
)
</code>

 

[Joe4]

What is the Data Type for Loan1 and Loan2?
You are trying to import 111222333444 into it.
If it is a number Data type, is it one big enough to hold such a number?
If it is a Text field, you will need single quotes around that number.

What is the Data Types for your Balance and Settlement fields?
Is they are numeric, you need to remove the single-quotes from around the values you are inserting for them.

Also, what are the Data Types for all your Yes/No fields?

 

[Joe4]

Based on your post here:
http://www.mrexcel.com/forum/microsoft-access/861634-insert-into-help-text-date.html#post4186646

These are your data types:

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,
floor - text

So you have an issue with Currency and Settlement, as you should not have single-quotes around fields of Currency Data Type.

Your Loan number fields may or may not be OK, depending on which numeric Data Type you picked.

You also have an issue with your Percentage and Hqdate fields, specificially a single-quote at the beginning of each that should not be there since they are not text fields, i.e.

[COLOR=#333333][FONT=monospace]VALUES[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]([/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]‘TEST’, ‘firm’, 111222333444,,[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]‘Test First’, ‘Test Last’, ‘5000’, ‘2500’,[/FONT][/COLOR]
[COLOR=#ff0000][FONT=monospace]‘0.5[/FONT][/COLOR][COLOR=#333333][FONT=monospace], #06/01/2015#, #06/01/2015#, ‘1 lump sum’,[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]‘Yes’, ‘Open’, ‘Pending’, ‘pending’,[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace]‘No’, ‘No’, #06/01/2015#, ‘Yes’, #[/FONT][/COLOR]
[COLOR=#ff0000][FONT=monospace]‘06/01/2015#[/FONT][/COLOR][COLOR=#333333][FONT=monospace], ‘Below’[/FONT][/COLOR]
[COLOR=#333333][FONT=monospace])[/FONT][/COLOR]

 

[james_lankford]

it also looks like you have a single quote in front of your percentage
'0.5

"You also have an issue with your last date, specificially a single-quote after the first #"

I think that's my fault
when I rewrote the code

 

[Joe4]

If you change the loan1 and loan2 fields to a Text Data Type, then this code worked for me:

INSERT INTO
settlement
(
vendor, stream, loan1, loan2,
fname, lname, balance, settlement,
percentage, sstart, send, sterms,
approved, status, closed, comments,
counter, settled, com, hqreview,
hqdate, floor 
) 
VALUES
(
'TEST', 'firm', '111222333444',,
'Test First', 'Test Last', 5000, 2500,
0.5, #06/01/2015#, #06/01/2015#, '1 lump sum',
'Yes', 'Open', 'Pending', 'pending',
'No', 'No', #06/01/2015#, 'Yes',
#06/01/2015#, 'Below'
)

 

[cstickman]

Thank you both very much for all of the help as I am learning and trying to understand all of this. I am learning so much with your detailed explanations. So I checked the loan1 and loan2 values and they are number with long integer.

Joe, you brought up a good point one feature I would like to add down the road is to export it to excel. Should I change them to text fields? We all know how Excel changes number fields to scientific values and then someone would have to go through the report and change them to text fields.

 

[Joe4]

Long Integer is not big enough to hold your entry of 111222333444.
See: https://support.office.com/en-nz/article/Set-the-field-size-ba65e5a7-2e6f-4737-8e72-36b93f966a33

Long Integer — For integers that range from -2,147,483,648 to +2,147,483,647.

So Long Integer cannot hold a entry of 12 digits.

Yes, I would go with Text. With ID fields, where you usually aren't going to be doing mathematical computations on them, it is generally good practice to store them as Text (especially if you may have leading zeroes).

 

[cstickman]

Guys, I have updated the code based on your suggestions and it is working correctly now. I put some test data into the form and it all worked and was logged in the database. So now onto step 2 and 3 of the project. I have made two other forms one for updating the data and one to delete the data. The form has a built in subform that runs a query to the subform table. The user will then select the line they want to update and it will populate the fields. Once they are done editing the information they would click on the update button. So can I use the same INSERT INTO command, but change it to UPDATE for the update and DELETE for the delete portion of it? I believe I also need to do this to reference the unique ID it was given in the database? I have that labeled as ID - data value as auto number. I think I need to use the WHERE command on the ID field? ("WHERE ID=" & Me.txtid.Tag)

Thanks again guys!!

 

[Joe4]

So can I use the same INSERT INTO command, but change it to UPDATE for the update and DELETE for the delete portion of it? I believe I also need to do this to reference the unique ID it was given in the database? I have that labeled as ID - data value as auto number. I think I need to use the WHERE command on the ID field? ("WHERE ID=" & Me.txtid.Tag)

No, the syntax for DELETE and UPDATE queries is different.

When you use DELETE, you are deleting the whole record, so you do not need field references in the DELETE clause. It is actually much simpler.
You will need to use the WHERE clause though, to tell it which records to delete.
See: Delete Query in Access

When you use UPDATE, you usually only include the fields that you are changing (using the SET command to indicate what they are being changed to). And you will need to use the WHERE clause to tell it which records to delete.
See: SQL Update

If you have any questions on those, I would recommend starting a new thread.
The general rule of thumb is to start a new thread with a new question, unless the new question is dependent on the previous one.