INSERT VBA query (1 Viewer)

torz

Registered User.
Local time
Today, 14:07
Joined
Jun 21, 2014
Messages
73
Hey guys,
I'm not sure where this has gone wrong, done a lot of searching and googling and different attempts with the " & ' but just can't seem to find it...
Basically its scrapping a webpage and then fires the below to add it all into a table.
It works perfectly with debug.print, but the table insert always gives the Run-time error '3075' error. Seems to be to do with txtDetailedDescription as the next part of the error message is Syntax error (missing operator) in query expression "** general comments **
Comments:

and so on, which is part of what the fields value should be.

Code:
    CurrentDb.Execute "INSERT INTO [tempITAM]([iTamNumber], [lblLastResolvedDate], [lblClosedDate], [txtDescription], [txtWorkLog], [txtDetailedDescription], [txtResolution])" & _
                  "VALUES ('" & rs("iTamNumb") & "', '" & lblLastResolvedDate & "', '" & lblClosedDate & "', '" & txtDescription & "', '" & txtWorkLog & "', '" & txtDetailedDescription & "', '" & txtResolution & "');"

I'm sure its something easy that I have overlooked or at least I hope so!

Thanks guys!
 

namliam

The Mailman - AWF VIP
Local time
Today, 06:07
Joined
Aug 11, 2003
Messages
11,695
The statement itself is proper...
Keep the following in mind:
- No ' around numbers , i.e. your tTamNumb?
- Instead of ' use # around dates and make sure those dates are in US date format
- Since you are using ' for strings, the strings them selves cannot have those single quotes in them or it will break your sql.
 

torz

Registered User.
Local time
Today, 14:07
Joined
Jun 21, 2014
Messages
73
The statement itself is proper...
Keep the following in mind:
- No ' around numbers , i.e. your tTamNumb?
- Instead of ' use # around dates and make sure those dates are in US date format
- Since you are using ' for strings, the strings them selves cannot have those single quotes in them or it will break your sql.

the itamnumb starts with INC which is why it is the way it is and working fine, I thought it might have been that the detailed description had ' in it, and you have confirmed my suspicion.

how should I lay out the detaildescription instead of '"txtdetaildescription"' ?
I have tried just about every way I can think of...
 

TJPoorman

Registered User.
Local time
Yesterday, 22:07
Joined
Jul 23, 2013
Messages
402
you can use Replace(txtdetaildescription, "'","''"). Replacing the single ' with double '' will insert the single quotes.
 

torz

Registered User.
Local time
Today, 14:07
Joined
Jun 21, 2014
Messages
73
I'm obviously doing it wrong, when I replace the '" & txtdetail '" & txtDetailedDescription & "' with Replace(txtdetaildescription, "'","''") it seems to comment out the remaining part of the query?
 

namliam

The Mailman - AWF VIP
Local time
Today, 06:07
Joined
Aug 11, 2003
Messages
11,695
Instead of ' use double double quotes ""
Like so:
... ,""" & txtDescription & """, ...
 

torz

Registered User.
Local time
Today, 14:07
Joined
Jun 21, 2014
Messages
73
it still did not like that, pretty sure that was one of the original that I tried :)

finally got it working with the replace function

Code:
    CurrentDb.Execute "INSERT INTO [tempITAM]([iTamNumber], [lblLastResolvedDate], [lblClosedDate], [txtDescription], [txtDetailedDescription], [txtWorkLog], [txtResolution]) " & _
                  "VALUES ('" & rs("iTamNumb") & "', '" & lblLastResolvedDate & "', '" & lblClosedDate & "', '" & txtDescription & "', '" & Replace([txtDetailedDescription], "'", "") & "', '" & txtWorkLog & "', '" & txtResolution & "' );"

yay

thanks for your help guys!
 

namliam

The Mailman - AWF VIP
Local time
Today, 06:07
Joined
Aug 11, 2003
Messages
11,695
Using the double double quotes should work, unless there is also double quotes in your description :(
 

torz

Registered User.
Local time
Today, 14:07
Joined
Jun 21, 2014
Messages
73
NOOOOOOOOOOOOOOOOOOO!

Still having problems :mad::banghead::confused::eek::mad::banghead::banghead::banghead:

So here is what I currently have, which is basically working fine. The only issue that I have now is that it freaks out if the lblClosedDate & lblLastResolvedDate are blank (using the #" "# for the dates). how would I get past that?


Code:
    CurrentDb.Execute "INSERT INTO [tempITAM]([iTamNumber], [lblLastResolvedDate], [lblClosedDate], [txtDescription], [txtDetailedDescription], [txtWorkLog], [txtResolution]) " & _
                  "VALUES ('" & rs("iTamNumb") & "', '" & lblLastResolvedDate & "', '" & lblClosedDate & "', '" & Replace([txtDescription, "'", "") & "', '" & Replace([txtDetailedDescription], "'", "") & "', '" & Replace([txtWorkLog], "'", "") & "', '" & Replace([txtResolution], "'", "") & "' );"
 

namliam

The Mailman - AWF VIP
Local time
Today, 06:07
Joined
Aug 11, 2003
Messages
11,695
How about "simply" building the insert to suite your needs or use a recordset?

dim rs as dao.recordset
set rs = currentdb.openrecordset("tempITAM")
rs.addnew
rs![iTamNumber] = ...
rs![lbl...
rs.update
rs.close
set rs = nothing
 

TJPoorman

Registered User.
Local time
Yesterday, 22:07
Joined
Jul 23, 2013
Messages
402
So this code doesn't work:

Code:
CurrentDb.Execute ("INSERT INTO [tempITAM]([iTamNumber], [lblLastResolvedDate], [lblClosedDate], [txtDescription], [txtDetailedDescription], [txtWorkLog], [txtResolution]) " & _
                "VALUES ('" & rs("iTamNumb") & "', #" & lblLastResolvedDate & "#, #" & lblClosedDate & "#, '" & _
                    Replace([txtDescription], "'", "''") & "', '" & Replace([txtDetailedDescription], "'", "''") & "', '" & Replace([txtWorkLog], "'", "''") & "', '" & _
                    Replace([txtResolution], "'", "''") & "' );")
 

torz

Registered User.
Local time
Today, 14:07
Joined
Jun 21, 2014
Messages
73
So this code doesn't work:

Code:
CurrentDb.Execute ("INSERT INTO [tempITAM]([iTamNumber], [lblLastResolvedDate], [lblClosedDate], [txtDescription], [txtDetailedDescription], [txtWorkLog], [txtResolution]) " & _
                "VALUES ('" & rs("iTamNumb") & "', #" & lblLastResolvedDate & "#, #" & lblClosedDate & "#, '" & _
                    Replace([txtDescription], "'", "''") & "', '" & Replace([txtDetailedDescription], "'", "''") & "', '" & Replace([txtWorkLog], "'", "''") & "', '" & _
                    Replace([txtResolution], "'", "''") & "' );")

Nope comes back with syntax error in date in query expression '#' error.
The above will work if both the resolve & closed dates happen to be populated, but they won't always be populated @ the time of this query being executed. the above worked as intended when changing the #'s to ' on the closedate as this was the only field that came back with "" value

so still not sure how to get around the problem..
 

TJPoorman

Registered User.
Local time
Yesterday, 22:07
Joined
Jul 23, 2013
Messages
402
How about setting up a couple variables to hold a string setup. Like this:

Code:
Dim strResolved As String
Dim strClosed As String

If Nz(lblLastResolvedDate,"")<>"" Then
    strResolved="#" & lblLastResolvedDate & "#"
Else
    strResolved = "''"
End If
If Nz(lblClosedDate,"")<>"" Then
    strClosed="#" & lblClosedDate & "#"
Else
    strClosed="''"
End If

CurrentDb.Execute ("INSERT INTO [tempITAM]([iTamNumber], [lblLastResolvedDate], [lblClosedDate], [txtDescription], [txtDetailedDescription], [txtWorkLog], [txtResolution]) " & _
                "VALUES ('" & rs("iTamNumb") & "', " & strResolved & ", " & strClosed & ", '" & _
                    Replace([txtDescription], "'", "''") & "', '" & Replace([txtDetailedDescription], "'", "''") & "', '" & Replace([txtWorkLog], "'", "''") & "', '" & _
                    Replace([txtResolution], "'", "''") & "' );")
 

torz

Registered User.
Local time
Today, 14:07
Joined
Jun 21, 2014
Messages
73
How about setting up a couple variables to hold a string setup. Like this:

Code:
Dim strResolved As String
Dim strClosed As String

If Nz(lblLastResolvedDate,"")<>"" Then
    strResolved="#" & lblLastResolvedDate & "#"
Else
    strResolved = "''"
End If
If Nz(lblClosedDate,"")<>"" Then
    strClosed="#" & lblClosedDate & "#"
Else
    strClosed="''"
End If

CurrentDb.Execute ("INSERT INTO [tempITAM]([iTamNumber], [lblLastResolvedDate], [lblClosedDate], [txtDescription], [txtDetailedDescription], [txtWorkLog], [txtResolution]) " & _
                "VALUES ('" & rs("iTamNumb") & "', " & strResolved & ", " & strClosed & ", '" & _
                    Replace([txtDescription], "'", "''") & "', '" & Replace([txtDetailedDescription], "'", "''") & "', '" & Replace([txtWorkLog], "'", "''") & "', '" & _
                    Replace([txtResolution], "'", "''") & "' );")

I did something similar but this is a much easier way :)
its go through 38 of the 200 odd records so far with out any issues so fingers crossed :)
 

Users who are viewing this thread

Top Bottom