Insert Into string

radek225

Registered User.
Local time
Today, 09:43
Joined
Apr 4, 2013
Messages
307
I always have problem with the end of the string
what is wrong in this string?
Code:
    strSQL = "Insert into tblGoraZleceniaNowa (Naklad_pracy, IloscStron, KoloryP, KoloryT, FormatX, FormatY, id_zlecenia, nazwa_id) values (" & Nz(Me.Naklad_pracy, "") & ", " & Nz(Me.IloscStron, "") & ", '" & Nz(Me.KoloryP, "") & "', '" & Nz(Me.KoloryT, "") & "', " & Nz(Me.FormatX, "") & ", " & Nz(Me.FormatY, "") & ", '" & Forms!frmZlecenieMarzena!ID_Zlecenia & "', " & Nz(Me.Nazwa_id, 0) & ")"
 
What problem are you having? Is there an error message?
 
check it on immediate window:

?strSql
 
Like arnelgp says, try that.

Alternatively - the slow way but doable as you only have eight values - remove them one at a time until it runs. The last one to be removed is the area to focus on.

Hhave you tried adding ";" before the final end quote?
 
Immediately after your problem line, type
Code:
 Debug Print strSQL
This should show you what the string is being set to.
Or you could use a message box
Code:
 Msgbox strSQL
Sometimes, typos and other errors show up more clearly when you see the whole thing written out.

As far as the semi-colon goes, I find that sometimes missing it out cause me problems. e.g.
"Select * from table1"
might error, but
"Select * from table1;"
might work
 
As far as the semi-colon goes, I find that sometimes missing it out cause me problems. e.g.
"Select * from table1"
might error, but
"Select * from table1;"
might work

Yes but when write a clean sql then must be ";" but If we have string in vba then without ";".

So debug.print shows me
Code:
Insert into tblGoraZleceniaNowa (Naklad_pracy, IloscStron, KoloryP, KoloryT, FormatX, FormatY, id_zlecenia, nazwa_id) values (200, 2, 'c;m;y;k', 'c;m;y;k', 5, 4, '1055/14', 3)
So everything shoudl be ok, but it's not:/ I think the problem could be at the end of the string
 
You can add a breakpoint on that line, and check the different values.
(Maybe something is wrong with that 'Forms!frmZlecenieMarzena!ID_Zlecenia')
The quotes looks all ok to me. So all i can think of now is that some value is null or weird.

Can you post the message you get when you try that sql ?
 
Last edited:
I use chr(34) for a string

the insert stringneeds to look like this for a number, date, and string

insert into mytable (number, date, string)
select 12, #12 August 2015#, "string text"

to do this with variables

s = "select " & numericvar
s = s & " , " & "#" & formatteddatevar & "# "
s = s & " , " & chr(34) & stringvar & chr(34)
 
Once again, better minds prevail. I do the same, regarding chr(34), but missed it here. Hopefully, it solves the problem.
 
Ok guys, the problem was Nz(Me.Nazwa_id, 0) because it's a combie field:)
 

Users who are viewing this thread

Back
Top Bottom