I have a form and want to append 3 values into another table using the
following but can't seem to get the right syntax. Could someone please
help out ?

sSQL = "INSERT INTO DebitNote (DrNote_No, DrNote_Date, Cert_No)
values (Me.Txt_DrNote_No, #me.DrNote_Date#, me.Cert_No)"
DoCmd.RunSQL sSQL

DrNote_No is a numeric field
DrNote_Date is a date/time field
Cert_No is a text field

Thanks & Rgds,
Prakash.

Prakash <simran (AT) omantel (DOT) net.om> wrote in
news:9c4bac4c-46cb-454a-adbe-c144e24d1dce (AT) u24g2000pru (DOT) googlegroups.co
m:

SQL cannot see the form so you have to put values not references to
values. Try
values (" & Me.txt_DrNote_No & "," & #me.DrNote_Date# & ","""
me.Cert_No & """)"


--
Bob Q.
PA is y I've altered my address.

On Oct 17, 2:54*pm, Bob Quintal <rquin... (AT) sPAmpatico (DOT) ca> wrote:
Thx Bob for such a quick response. I'm still getting a syntax error.
Here's my final statement

sSQL = "INSERT INTO DebitNote (DrNote_No, DrNote_Date, Cert_No)
values (" & Me.txt_DrNote_No & "," & #me.DrNote_Date# & ","""
me.Cert_No & """)"

It says "Compile error expected expression" and highlights the "hash"
symbol just before the date.

Any ideas please ?

Rgds,
Prakash.

Prakash wrote:
discussion. :-)

The goal when doing dynamic sql is to create a string that will run as-is
when pasted in the sql view of a query builder window. So, you have to see
the result of your concatenations to see what is going wrong. debug.print is
a great tool. In this case, the line:
debug.print sSQL will allow you to see the sql statement you are attempting
to execute and should allow you to quickly see the error. In this
case,however, the problem is not so simple. The error in this case is
preventing the string from being built. The hash marks have to be part of
the literal string expressions (inside the quotes) in your statement, like
this:

values (" & Me.txt_DrNote_No & ",#" & me.DrNote_Date & "#,"""

Bob had a slight typo in his reply. It should have been

values (" & Me.txt_DrNote_No & ",#" & me.DrNote_Date & "#,"""
me.Cert_No & """)"

That being said, I'd recommend using the following instead, to handle cases
where the user has a non American Short Date format:

values (" & Me.txt_DrNote_No & "," & Format(me.DrNote_Date,
"\#yyyy\-mm\-dd\#") & ","""
me.Cert_No & """)"


"Prakash" <simran (AT) omantel (DOT) net.om> wrote

On Oct 17, 5:25*pm, "Douglas J Steele"
<NOSPAM_djsteele (AT) NOSPAM_gmail (DOT) com> wrote:
Thanks Douglas! I did encounter Bob's tiny typo but managed to work my
way around it. Many thanks to you Bob ... you were a great help.
Douglas, thanks for pointing out that caveat ... I'll give it a shot.

Once again ... thanks everyone :-)

Rgds,
Prakash.

Prakash <simran (AT) omantel (DOT) net.om> wrote in
news:863a2879-4614-4c48-84a1-91ab67e2e77a (AT) o15g2000prh (DOT) googlegroups.co
m:

Glad you got it to work.

Sorry about the typos, It was early morning and I was still half
asleep, or I would have caught and corrected them.

Make that 90% still asleep, I should have caught them at half asleep.

--
Bob Q.
PA is y I've altered my address.