Having problems with dates

[CD Tom]

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.

[CD Tom]

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

[ajalwaysus]

You cannot pass a null to a date, so you may need to use an if statment to build your query.

Example:

If IsNull(BirthDate) then
     strSQL = "SELECT *" & _
     " FROM Table1" & _
     " WHERE Person = 'AJ'"
Else 
     strSQL = "SELECT *" & _
     " FROM Table1" & _
     " WHERE Person = 'AJ'"
     " AND birthdate = #" & Birthdate & "#"
Endif

-AJ

[NeoPa]

In SQL you can set a field to Null, but not an empty date literal.

It's the difference between :

Set MyDate = #5/31/2010#
Set MyDate = ##
Set MyDate = Null

The first is valid. The third is valid (for Date fields which allow Nulls). The second is not.

[CD Tom]

How do you set a date field to allow nulls?

[NeoPa]

As with any field, you set the Required property to No in the field design.

[CD Tom]

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 '#'

[NeoPa]

I've already covered that point in post #4 Tom. Check it again.

[TheSmileyCoder]

birthdate = nz(Birthdate,#" & Birthdate & "#,null)