Insert help needed 2

[whistler1]

i looked for a long time but i can't find the problem
this is the part of my code where the problems are:

sql = "INSERT INTO dagverkoop3 (datum, berekend, bloemen, planten, potterie, btw6, btw21, maatstaf6, maatstaf21 , bloemstukken, droogbloemen, kaartjes, teleflor, terug, leeggoed, groenten, fruit, voorgesneden, drank, gedroogd, salades, allerlei, korting)" & _
"Values(" & i & ", " & ber & ", " & blo & ", " & pla & ", " & pot & " " & _
"" & btw61 & ", " & btw211 & ", " & maa6 & ", " & maa21 & ", " & blo1 & " " & _
"" & dro & ", " & kaa & ", " & tel & ", " & ter & ", " & lee & ", " & gro & " " & _
"" & fru & ", " & voo & ", " & dra & ", " & ged & ", " & sal & ", " & all & " " & _
"" & kor & ")"
cn.Execute sql


the error message I recieve:

'syntaxiserror in query-expression 7 19'

hope someone can help me

 

[BiggerBrother]

I think you are missing a couple of comma and a space as well. Try :

sql = "INSERT INTO dagverkoop3 (datum, berekend, bloemen, planten, potterie, btw6, btw21, maatstaf6, maatstaf21 , bloemstukken, droogbloemen, kaartjes, teleflor, terug, leeggoed, groenten, fruit, voorgesneden, drank, gedroogd, salades, allerlei, korting) " & _
"Values (" & i & ", " & ber & ", " & blo & ", " & pla & ", " & pot & ", " & _
btw61 & ", " & btw211 & ", " & maa6 & ", " & maa21 & ", " & blo1 & ", " & _
dro & ", " & kaa & ", " & tel & ", " & ter & ", " & lee & ", " & gro & ", " & _
fru & ", " & voo & ", " & dra & ", " & ged & ", " & sal & ", " & all & ", " & _
kor & ")"
cn.Execute sql

Good luck

BB

 

[whistler1]

if I do this, I get nother error:

'number of queryvalues and targetfields does not match'

 

[cbsm]

look also at the date and number format.
You do not say which database you are using but in access for example :
a date has to be between # (and check the format also mm/dd/yyyy I think).
" values (#" & Format(MyDate, "mm/dd/yyyy") & "# ,
if in numbers you use the comma [,] as a decimal separator it will leads to problems as well use [.]
Strings need to be between single quotes
" values ('" & myString & "' ,

 

[whistler1]

I'm using an access database, everything is in numbers
if the comma as decimal separator is the problem, i'm going to have much more problems
i'm building this program to put the data in another program (for the accountancy), but this needs the comma as decimal separator, so I can't change that

 

[BiggerBrother]

before cn.execute sql line, can you include the line debug.print sql. Then copy and paste the line that appears in the intermediate window here. It may be that one of your data entries is throwing the command out.

BB

 

[whistler1]

this is what comes out:

INSERT INTO dagverkoop3 (datum, berekend, bloemen, planten, potterie, btw6, btw21, maatstaf6, maatstaf21 , bloemstukken, droogbloemen, kaartjes, teleflor, terug, leeggoed, groenten, fruit, voorgesneden, drank, gedroogd, salades, allerlei,korting) Values (20040401, 393,64, 0, 103,12, 27,7, 19,89, 7,32, 331,58, 34,85, 0, 0, 5,65, 0, 0, 0, 41,55, 110,25, 0, 8,82, 0, 0, 0, 0)

 

[RoyVidar]

I think this should work:

[tt] "Values(" & i & ", " & replace(cstr(ber),",",".") & ...[/tt]

You can also use the approach from chiph's faq faq709-1526 on Access, where you shouldn't need this formatting.

Roy-Vidar

 

[BiggerBrother]

Ah that will be it then. You could insert these values into the database as string or as xx.xx integer values.

If you want to use the values for mathematical equations, then xx.xx values are the right thing toi use, else i would use string values, then you can store the values as 32,23 .

bb

 

[whistler1]

thanks
that worked
a star for BiggerBrother and RoyVidar