Insert Into string

[radek225]

I always have problem with the end of the string
what is wrong in this string?

    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) & ")"

 

[Alc]

What problem are you having? Is there an error message?

 

[radek225]

What problem are you having? Is there an error message?

It's syntax error

 

[arnelgp]

check it on immediate window:

?strSql

 

[radek225]

check it on immediate window:

?strSql

How to check it? Some procedure with debug.print?

 

[Alc]

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?

 

[radek225]

Like arnelgp says, try that.

Could You show me what should I write to see problem in immediate window?

Hhave you tried adding ";" before the final end quote?

In string ";"? I never heard of that

 

[Alc]

Immediately after your problem line, type

 Debug Print strSQL

This should show you what the string is being set to.
Or you could use a message box

 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

 

[radek225]

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

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

 

[Grumm]

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 ?

 

[gemma-the-husky]

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)

 

[Alc]

Once again, better minds prevail. I do the same, regarding chr(34), but missed it here. Hopefully, it solves the problem.

 

[radek225]

Ok guys, the problem was Nz(Me.Nazwa_id, 0) because it's a combie field

 

[Alc]

Glad you found it.