Syntax Error for INSERT INTO statement 1

[srast]

Hello all;
I am trying to run an Insert Into statement from an event in an Access form, and I get the following error message:
"Run-time erorr 3134. Syntax error in INSERT INTO statement."
Here is my code:
INSERT INTO Restock Orders (PONumber, DateOrdered) VALUES ('10048BO1', '#12/24/2002#')
I tried the date field with quotes, and without quotes, with no luck. I also tried it without the '#' delimeters too. PONumber is a text type in my table, because it contains letters as well as numbers.
Any ideas would be greatly appreciated.
Thanks!
Steve

 

[Zathras]

If "Restock Orders" is two words and not a typo, I think you need to say [Restock Orders] and the format for the date is to use #12/24/2002# (no quotes)

Try this:

INSERT INTO [Restock Orders] (PONumber, DateOrdered) VALUES ('10048BO1', #12/24/2002#)

 

[srast]

Zathras:
I would give you five stars if I could!
Thanks for saving my sanity.
I really aprreciate it.


I have another question though.
I had a combobox that automatically looked up the record when the user choose an item. (This came thru the wizard).
The field used to be a number, but I changed it in the table itself to a text type.
Now the code doesn't work, and I get the following error:
"Run time error 3463. Data type mismatch in criteria expression"
Here is the code:
Set rs = Me.Recordset.Clone
rs.FindFirst "[PONumber] = " & str(Me![Combo25])
Me.Bookmark = rs.Bookmark
It worked before, why not now?
I tried removing the "str", but that didn't help.
Any ideas?
Thanks
Steve

 

[Zathras]

I don't like to suggest code without a chance to debug it myself first, but in the interests of time...

If PONumber is now a text type column, then the data on the right-hand side of the equal sign needs to be in quotes otherwise the SQL processor tries to interpret it as a column name in a table or a number depending on whether the first character is alpha or numeric.

The error "Data type mismatch" suggests to me that the selected item (from Combo25) is a number and so the SQL compiler is seeing PONumber=456 when it expects to see PONumber='456' where the single quotes indicate that the value is textual.

Strictly speaking, you don't need the str( ) when using the ampersand for concatenation but it doesn't hurt. What it needs are the single quotes. Like this:

rs.FindFirst "[PONumber] = '" & str(Me![Combo25]) &"'"

Hope that helps.

 

[srast]

Thanks again, Zathras.
Once again, your reply is on the money.
I appreciate the assistance.
Steve