By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,462 Members | 2,394 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,462 IT Pros & Developers. It's quick & easy.

Having problems with dates

100+
P: 434
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
Share this Question
Share on Google+
8 Replies


100+
P: 434
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

Expert 100+
P: 266
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
Expert Mod 15k+
P: 31,186
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

100+
P: 434
How do you set a date field to allow nulls?
Jan 26 '10 #5

NeoPa
Expert Mod 15k+
P: 31,186
As with any field, you set the Required property to No in the field design.
Jan 26 '10 #6

100+
P: 434
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
Expert Mod 15k+
P: 31,186
I've already covered that point in post #4 Tom. Check it again.
Jan 27 '10 #8

TheSmileyCoder
Expert Mod 100+
P: 2,321
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.