insert query error: missing semicolon(;)

[ylp]

Hi,

I have a query in VBA access 2003

strSQL = "INSERT INTO tblTest(totalNum, totalWeight)" _
& " VALUES" & totalGal & ", " & "" & totalW& ")" _
& " WHERE ID = " & Me.ID & " ;"

CurrentDb().Execute strSQL

The qry is in immediate window:
INSERT INTO tblTest( totalNum, totalWeight) VALUES(120,88)
WHERE ID = 3;

But got error:
Run-time error'3137':
Missing semicolon( at end of SQL statement.

Thanks very much!

 

[pbaldy]

You would not have a WHERE clause with a VALUES clause.

 

[ylp]

Hi,
Thanks so much for you reply.

I delete the where clause, but the query did not work, the record did not go into the database. That's reasonable, because the qry does not know where to insert. I need to insert based on the ID.

Please help!

 

[pbaldy]

What happens if you copy the SQL (without the WHERE clause) into an empty query and try to run it? You should get a better error message.

 

[ylp]

I got:
INSERT INTO tblTest( totalNum, totalWeight) VALUES(120,88) ;

No error! and no data write into the database. Thanks!!

 

[pbaldy]

I'd say you're welcome, but it sounds like there's still a problem? Don't you want a record inserted?

 

[ylp]

Thanks.

I DO want a record written into database, but where is the problem?

 

[pbaldy]

Did you test running an actual query? You'll get a better error there. My guess is that there's a required field not being populated. To get an error in code:

CurrentDb().Execute strSQL, dbFailOnError

 

[ylp]

Thanks so much for your great help!

It almost works, I got better error using your command.

There is one more problem:
I need to insert a field with text value from form, the query is:
strSQL = "INSERT INTO tblTest(totalNum, totalWeight,units)" _
& " VALUES" & totalGal & ", " & totalW& "," & Me.Text24.Value & ");"

I got Run-time error 3601, too few parameters, Expected1

but if I add "'Me.Text24.Value '", its value is "LB", but it just insert "Me.Text24.Value" into the database.

 

[pbaldy]

If it's a text value, it needs to be surrounded by single quotes. Try

& " VALUES" & totalGal & ", " & totalW & ",'" & Me.Text24.Value & "');"

Are you adding the opening parentheses with the totalGal variable?

 

[ylp]

Hi,

Many thanks!

It works great!

Have a nice afternoon!

 

[pbaldy]

No problem, glad we got it sorted out.