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? 5 2787
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:
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-----
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:
>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-----
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 This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: evolve |
last post by:
why doesn't c# seem to support some kind of 'null date'
for instance:
i have an application where the user is not obliged to enter a date until
an event happens (e.g. a bug was fixed on xyz)
...
|
by: Vern |
last post by:
I'm using the Microsoft Enterprise Data Access block to call the stored
procedure. One of the values the stored procedure returns is the stop date.
When the stop date is null, the program crashes....
|
by: Dean Slindee |
last post by:
I am looking for the "right" way to handle inserting and presenting null
date values.
Public Const c_NullDate As Date = #12:00:00 AM#
If I set the value of a date variable in an SQL Server insert...
|
by: GB |
last post by:
Is there a function to set a null date in vb.net? Normally, I create a
const NULLDATE = "1/1/1900", but that becomes a drag at times. Especially,
handling a nulldate from SQL into a dataset.
...
|
by: Brad |
last post by:
If I need to assign a NULL value to a date variable, how would I go about
doing that? For right now I am using 01-01-2001, however I don't want to use
a "real" date if the date should really be...
| |
by: Ronald S. Cook |
last post by:
When I pass a null date from my client to my service project, it comes
across as #12:00:00 AM#. While I can easily code for this, it seems like a
hack. Is this normal?
I'm using WCF, by the...
|
by: klove1209 |
last post by:
Good afternoon,
I am currently working on this unbounded form that has numerous date fields. I wanted to know if it is possible to save data in a form, with a null date field. I am currently...
|
by: metsu962 |
last post by:
Okay so I've been tearing my hair out for weeks over this issue. What I have is a Crystal Report that's showing a table pulled from an Access Database. The database is a HUGE pile of junk and it...
|
by: mwittekiend |
last post by:
I am trying to convert a NULL date to a Y/N flag. It is giving me a conversion error. Here is the code. Any help is appreciated. Thanks
'CommitDateTimeFlag' = (case when CommitDateTime IS NULL...
|
by: Hystou |
last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
|
by: Oralloy |
last post by:
Hello folks,
I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>".
The problem is that using the GNU compilers,...
| |
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: 6302768590 |
last post by:
Hai team
i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated ...
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence...
| |