I have written the following SQL to take data off of a form and insert
it into a table.

sql = "INSERT INTO Water Values('" & Me.AccountNumber & "','" &
Me.Department & "','" & Me.Delivery & "','" & Me.IndexCode & "','" &
Me.ContactPerson & "','" & Me.ContactExt & "','" & Me.Canceled & "',#"
& Me.AddDate & "#,#" & Me.CancelDate & "#,'" & Me.Fiscal_Year & "');"

It works fine if there is a date in the CancelDate field, but if the
date is left blank I get a Runtime Error
Syntax error in date in query expression'#'.

How can I fix this?

iwasinnihon wrote:

I have written the following SQL to take data off of a form and insert
it into a table.
>
sql = "INSERT INTO Water Values('" & Me.AccountNumber & "','" &
Me.Department & "','" & Me.Delivery & "','" & Me.IndexCode & "','" &
Me.ContactPerson & "','" & Me.ContactExt & "','" & Me.Canceled & "',#"
& Me.AddDate & "#,#" & Me.CancelDate & "#,'" & Me.Fiscal_Year & "');"
>
It works fine if there is a date in the CancelDate field, but if the
date is left blank I get a Runtime Error
Syntax error in date in query expression'#'.
>
How can I fix this?

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

You'll have to check for NULLs in the form. Use the IIf() function.
Ex:

& IIf(IsNull(Me.AddDate), NULL, "#" & Me.AddDate & "#") &

--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. I DO NOT respond to emails **

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBSL73UIechKqOuFEgEQJsCgCePdkJVydtwOaar0IPUeoX2j +yrIUAnjyW
cwktOKCg072ZNjWP7RqD0Io6
=NHY9
-----END PGP SIGNATURE-----

On Sep 3, 2:45*pm, MGFoster <m...@privacy.comwrote:

iwasinnihon wrote:

>

>

>

>
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1
>
You'll have to check for NULLs in the form. *Use the IIf() function.
Ex:
>
& IIf(IsNull(Me.AddDate), NULL, "#" & Me.AddDate & "#") &
>
--
MGFoster:::mgf00 <atearthlink <decimal-pointnet
Oakland, CA (USA)
** Respond only to this newsgroup. *I DO NOT respond to emails **
>
-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv
>
iQA/AwUBSL73UIechKqOuFEgEQJsCgCePdkJVydtwOaar0IPUeoX2j +yrIUAnjyW
cwktOKCg072ZNjWP7RqD0Io6
=NHY9
-----END PGP SIGNATURE-----

I must be pretty dense. I updated the line so that it reads like so

sql = "INSERT INTO Water Values('" & Me.AccountNumber & "','" &
Me.Department & "','" & Me.Delivery & "','" & Me.IndexCode & "','" &
Me.ContactPerson & "','" & Me.ContactExt & "','" & Me.Canceled & "',#"
& Me.AddDate & "#, & IIf(IsNull(Me.CancelDate), Null, "#" &
Me.CancelDate & "#") & ,'" & Me.Fiscal_Year & "');"

This gives me a compile error. If I add quotes so that it reads

sql = "INSERT INTO Water Values('" & Me.AccountNumber & "','" &
Me.Department & "','" & Me.Delivery & "','" & Me.IndexCode & "','" &
Me.ContactPerson & "','" & Me.ContactExt & "','" & Me.Canceled & "',#"
& Me.AddDate & "#," & IIf(IsNull(Me.CancelDate), Null, "#" &
Me.CancelDate & "#") & ",'" & Me.Fiscal_Year & "');"


I get a Syntax error

What am I doing wrong?

sql = "INSERT INTO Water Values('" & Me.AccountNumber & "','" &
Me.Department & "','" & Me.Delivery & "','" & Me.IndexCode & "','" &
Me.ContactPerson & "','" & Me.ContactExt & "','" & Me.Canceled &
"',#"
& Me.AddDate & "," & "IIf(IsNull(" & Me.CancelDate & "), Null, "#" &
Me.CancelDate & "#") & ",'" & Me.Fiscal_Year & "');"

you needed " around the iif statement since you are passing it into a
query and you had an extra # alternativally it would be better to do

IF isnull(me.canceldate) then
sql = "INSERT INTO Water Values('" & Me.AccountNumber & "','" &
Me.Department & "','" & Me.Delivery & "','" & Me.IndexCode & "','" &
Me.ContactPerson & "','" & Me.ContactExt & "','" & Me.Canceled &
"',#"
& Me.AddDate & "#," & Null & ",'" & Me.Fiscal_Year & "');"
else
sql = "INSERT INTO Water Values('" & Me.AccountNumber & "','" &
Me.Department & "','" & Me.Delivery & "','" & Me.IndexCode & "','" &
Me.ContactPerson & "','" & Me.ContactExt & "','" & Me.Canceled &
"',#"
& Me.AddDate & "," & "#" & Me.CancelDate & "#" & ",'" & Me.Fiscal_Year
& "');"
end if

if your query isnt working do this

msgbox(sql)

and it will output what it exatally looks like then you can see if
there is an issue with the syntax

hope this helps

Regards
Kelvan

Thank you that made the that error go away. However, now I get an
error message that says error in Insert Into statement. If I put in a
cancel date, I don't get an error.

iwasinnihon wrote:

On Sep 3, 2:45*pm, MGFoster <m...@privacy.comwrote:

>
I must be pretty dense. I updated the line so that it reads like so
>
sql = "INSERT INTO Water Values('" & Me.AccountNumber & "','" &
Me.Department & "','" & Me.Delivery & "','" & Me.IndexCode & "','" &
Me.ContactPerson & "','" & Me.ContactExt & "','" & Me.Canceled &
"',#" & Me.AddDate & "#, & IIf(IsNull(Me.CancelDate), Null, "#" &
Me.CancelDate & "#") & ,'" & Me.Fiscal_Year & "');"
>
This gives me a compile error. If I add quotes so that it reads
>
sql = "INSERT INTO Water Values('" & Me.AccountNumber & "','" &
Me.Department & "','" & Me.Delivery & "','" & Me.IndexCode & "','" &
Me.ContactPerson & "','" & Me.ContactExt & "','" & Me.Canceled &
"',#" & Me.AddDate & "#," & IIf(IsNull(Me.CancelDate), Null, "#" &
Me.CancelDate & "#") & ",'" & Me.Fiscal_Year & "');"
>
>
I get a Syntax error
>
What am I doing wrong?

I think what you need to pass to the Jet engine, would be a string
containing the word/text "Null", so in the IIF thingie, try:

.... & IIf(IsNull(Me.CancelDate), "Null", "#" & Me.CancelDate & "#")
&...

i e, quotes around "Null"

One question though - can't you use a bound form? That's a bit easier
to deal with, no need for SQL.

One comment; when passing dates to a string for evaluation/execution
by the Jet engine, one is usually encouraged to use some formatting
ensuring it will be received correctly regardless of date settings.

For instance ISO 8601, which is "yyyy-mm-dd", for more information,
and another safe format, check out Allen Brownes page
http://allenbrowne.com/ser-36.html

--
Roy-Vidar