469,165 Members | 2,253 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,165 developers. It's quick & easy.

Having problems with dates

470 256MB
I don't know why I'm having so much problem with this date. I have a database that we are adding the birthdate to. I've setup the field in the master file and have a update screen that the user select the person from a drop down menu. When I try and do an update statement with the birthdate = #" & Birthdate & "# if there is a birthdate entered it works fine. If they call up a person and don't enter a birthdate I get an error, it doesn't like a null date but it's ok to have a null date. Where am I going wrong why can't I have a null date?
Thanks again for all your help.
Jan 25 '10 #1
8 1353
CD Tom
470 256MB
PS: the error I get is Run-Time error 3075
syntax error in date in query expression '#' when I look at it it's Null
Jan 25 '10 #2
ajalwaysus
266 Expert 100+
You cannot pass a null to a date, so you may need to use an if statment to build your query.

Example:

Expand|Select|Wrap|Line Numbers
  1. If IsNull(BirthDate) then
  2.      strSQL = "SELECT *" & _
  3.      " FROM Table1" & _
  4.      " WHERE Person = 'AJ'"
  5. Else 
  6.      strSQL = "SELECT *" & _
  7.      " FROM Table1" & _
  8.      " WHERE Person = 'AJ'"
  9.      " AND birthdate = #" & Birthdate & "#"
  10. Endif
-AJ
Jan 25 '10 #3
NeoPa
32,167 Expert Mod 16PB
In SQL you can set a field to Null, but not an empty date literal.

It's the difference between :
Expand|Select|Wrap|Line Numbers
  1. Set MyDate = #5/31/2010#
  2. Set MyDate = ##
  3. Set MyDate = Null
The first is valid. The third is valid (for Date fields which allow Nulls). The second is not.
Jan 26 '10 #4
CD Tom
470 256MB
How do you set a date field to allow nulls?
Jan 26 '10 #5
NeoPa
32,167 Expert Mod 16PB
As with any field, you set the Required property to No in the field design.
Jan 26 '10 #6
CD Tom
470 256MB
It is already set to No in the required field, I checked that first. But when Iset the vbirthdate = null and try to update the record with an sql update statement and use Birthdate = #" & vbirthdate & "# I get the error.
syntax error in date in query expression '#'
Jan 27 '10 #7
NeoPa
32,167 Expert Mod 16PB
I've already covered that point in post #4 Tom. Check it again.
Jan 27 '10 #8
TheSmileyCoder
2,321 Expert Mod 2GB
Expand|Select|Wrap|Line Numbers
  1. birthdate = nz(Birthdate,#" & Birthdate & "#,null)
Jan 29 '10 #9

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by Alistair | last post: by
5 posts views Thread by Jonathan Crawford | last post: by
5 posts views Thread by Jim Fox | last post: by
9 posts views Thread by Neil | last post: by
3 posts views Thread by Brian Simmons | last post: by
1 post views Thread by CARIGAR | last post: by
reply views Thread by zhoujie | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.