Insert Into

[Ann]

I found a few different ways of doing an Insert Into on the site. Some with
double quotes and some with single quotes but this is the only one I could
get to work. But it only works if everything is on one line. I have about
30 fields to insert which is just too many. I tried to break them up but I'm
not a programmer and everything I try doesn't work. I get an "expected end
of statement" error. Can someone please show me how to break a line of code?
I've tried different things but just can't get it right. Thanks for the
help. Here is the code:

Dim SQL As String

SQL = "INSERT INTO tblBackupAllFinal (Deal, Provider, TIN, Impact,
FinalDeal, ReceivedDt)" & _
"VALUES ('" & Me!Deal & "', '" & Me!Provider & "', '" & Me!TIN & "', '" &
Me!Impact & "', '" & Me!FinalDeal & "', '" & Me!ReceivedDt & "')"

DoCmd.RunSQL SQL

 

[Brendan Reynolds]

You can break a line of code by ending the line that is to be continued with
a space followed by an underscore, like so ...

SomeVariable = "This is " & _
"some text"

An alternative to breaking lines of code is to build up the string like so
....

SomeVariable = "This is "
SomeVariable = SomeVariable & "some text"

You might find the second method above easier to keep track of when building
complex SQL statements.

 

[Stefan Hoffmann]

hi Ann,

I found a few different ways of doing an Insert Into on the site. Some with
double quotes and some with single quotes but this is the only one I could
get to work. But it only works if everything is on one line. I have about
30 fields to insert which is just too many. I tried to break them up but I'm
not a programmer and everything I try doesn't work. I get an "expected end
of statement" error. Can someone please show me how to break a line of code?
I've tried different things but just can't get it right. Thanks for the
help. Here is the code:

Dim SQL As String

SQL = "INSERT INTO tblBackupAllFinal (Deal, Provider, TIN, Impact,
FinalDeal, ReceivedDt)" & _
"VALUES ('" & Me!Deal & "', '" & Me!Provider & "', '" & Me!TIN & "', '" &
Me!Impact & "', '" & Me!FinalDeal & "', '" & Me!ReceivedDt & "')"

DoCmd.RunSQL SQL

Public Function QuotedString(AString As String) As String

'VBA.Strings.Replace() requires Access>=2000
QuotedString = "'" & Replace(AString, "'", "''") & "'"

End Function

SQL = "INSERT INTO tblBackupAllFinal " & _
"(Deal, Provider, TIN, Impact, FinalDeal, ReceivedDt) " & _
"VALUES (" & QuotedString(Me!Deal) & ", " & _
QuotedString(Me!Provider) & ", " & _
QuotedString(Me!TIN) & ", " & _
QuotedString(Me!Impact) & ", " & _
QuotedString(Me!FinalDeal) & ", " & _
QuotedString(Me!ReceivedDt) & _
")"

But i assume that not all your fields are strings (field type text), are
they?


mfG
--> stefan <--

 

[Douglas J. Steele]

What are the data types for the various fields?

Values being inserted into Text fields need quotes around them, values being
inserted in Numeric fields don't. Values being inserted into Date fields
need octothorpes (#) characters around them, and must be in mm/dd/yyyy or an
unambiguous format, regardless of what the Regional Settings may have the
Short Date format set to.

For the purposes of illustration, I'll assume that Deal, Provider and TIN
are Numeric, Impact and FinalDeal are Text, and RecievedDt is Date:

SQL = "INSERT INTO tblBackupAllFinal (" & _
"Deal, Provider, TIN, Impact, FinalDeal, ReceivedDt)" & _
"VALUES (" & Me!Deal & ", " & Me!Provider & ", " & Me!TIN & ", " & _
"'" & Me!Impact & "', '" & Me!FinalDeal & "', " & _
Format(Me!ReceivedDt, "\#mm\/dd\/yyyy\#") & ")"

I'm using single quotes to delimit the values being passed to Text fields.
When you use single quotes, note that if the string being passed to a Text
field has an apostrophe in it (such as O'Reilly), you need to use the
Replace function to change all occurrences of single quotes in the string to
two single quotes in a row. If that's a possibility, the line above would be
written as:

SQL = "INSERT INTO tblBackupAllFinal (" & _
"Deal, Provider, TIN, Impact, FinalDeal, ReceivedDt)" & _
"VALUES (" & Me!Deal & ", " & Me!Provider & ", " & Me!TIN & ", " & _
"'" & Replace(Me!Impact, "'", "''") & "', '" & _
Replace(Me!FinalDeal, "'", "''") & "', " & _
Format(Me!ReceivedDt, "\#mm\/dd\/yyyy\#") & ")"

Exagerated for clarity, that's

SQL = " INSERT INTO tblBackupAllFinal ( " & _
" Deal, Provider, TIN, Impact, FinalDeal, ReceivedDt) " & _
" VALUES ( " & Me!Deal & ", " & Me!Provider & ", " & Me!TIN & ", " & _
" ' " & Replace(Me!Impact, " ' ", " ' ' ") & " ', '" & _
Replace(Me!FinalDeal, " ' ", " ' ' " ) & " ', " & _
Format(Me!ReceivedDt, " \#mm\/dd\/yyyy\# ") & " ) "

 

[Ann]

No, I have used dates, yes/no and memo fields. Do I need to do something
different for those? Is the memo field handled as text since it is, it's
just longer then the text type?

 

[Stefan Hoffmann]

hi Ann,

No, I have used dates, yes/no and memo fields. Do I need to do something
different for those? Is the memo field handled as text since it is, it's
just longer then the text type?

Douglas showed you, how to handle dates and numeric values.

Yes/No fields (boolean values) are handled like numeric values, so you
don't need quotes.

Memo fields contain text, so you have to quote them.


mfG
--> stefan <--

 

[Ann]

Hi Stefan,

I'm going to give it a try and see what happens. I think I'll add a few at
a time in case I run into trouble.

Thanks everyone for the help...I love this site...I'm always learning
something new from everyone.

 

[Ann]

Hi Stefan,

I made the got through most of the fields. I changed the format for dates
and dropped the quotes for the boolean and everything was working great until
I hit a record that didn't have any data to insert. Now I keep getting a
run-time error, "Invalid use of Null". Unless it's a Yes/No field. Those
still seem to work the right way. Is that because they are "0 or -1"???

Can you help me with the "Null" problem? I really appreciate it.

 

[Stefan Hoffmann]

hi Ann,

I made the got through most of the fields. I changed the format for dates
and dropped the quotes for the boolean and everything was working great until
I hit a record that didn't have any data to insert. Now I keep getting a
run-time error, "Invalid use of Null". Unless it's a Yes/No field. Those
still seem to work the right way. Is that because they are "0 or -1"???

Can you help me with the "Null" problem? I really appreciate it.

Assuming TIN is numeric:

SQL = "INSERT INTO tblBackupAllFinal (TIN) " & _
"VALUES (" & Me!TIN & ")"

will translate to the following

SQL = "INSERT INTO tblBackupAllFinal (TIN) VALUES ()"

if TIN is Null. This is an erroneous statement. To avoid this case you
can use the Nz() function:

SQL = "INSERT INTO tblBackupAllFinal (TIN) " & _
"VALUES (" & Nz(Me!TIN, "NULL") & ")"

which will translate to

SQL = "INSERT INTO tblBackupAllFinal (TIN) VALUES (NULL)"


mfG
--> stefan <--

 

[Michel Walsh]

Hi,


To avoid formatting problem, delimiter, date format, decimal "dot" which is
not a dot, etc, you can use the syntax FORMS!formName!controlName:



SQL = "INSERT INTO tblBackupAllFinal (Deal, Provider, TIN, Impact,
FinalDeal, ReceivedDt)
VALUES (FORMS!fname!Deal , FORMS!fname!Provider, FORMS!fname!TIN,
FORMS!fname!Impact , FORMS!fname!FinalDeal , FORMS!fname!ReceivedDt )"



DoCmd.RunSQL SQL



Hoping it may help,
Vanderghast, Access MVP