INSERT VBA query (1 Viewer)

[torz]

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.

    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 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]

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]

you can use Replace(txtdetaildescription, "'","''"). Replacing the single ' with double '' will insert the single quotes.

 

[torz]

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]

Instead of ' use double double quotes ""
Like so:
... ,""" & txtDescription & """, ...

 

[torz]

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

    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]

Using the double double quotes should work, unless there is also double quotes in your description

 

[torz]

NOOOOOOOOOOOOOOOOOOO!

Still having problems :banghead::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?

    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]

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]

So this code doesn't work:

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]

So this code doesn't work:

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]

How about setting up a couple variables to hold a string setup. Like this:

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]

How about setting up a couple variables to hold a string setup. Like this:

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