Setting a Date field to Null

[Micash]

Hi all
Haven't been around for ages. I have a problem that nobody seems to know how to resolve. In VB I have a textbox bound to a date field in Acess. When I want to empty the textbox because the date is incorrect and anknown for the moment, I can't drive home a null value to the database.
datPD.recordset("DOB").value = vbNull does not work
datPD.recordset("DOB").value = Null does not work either.
Any clues?

 

[addy]

I assume the field in the table is set to allow null values?

Probably a stupid question but I've seen it (and done it myself) before

 

[Micash]

Hi Addy
I guess I must be stupid. I have been working with Access 2003. Where do I set the table to allow Null values - in particular this date field? Is it the default value?
Regards
Mike

 

[SkipVought]

Table Design

Skip,
[sup]Don't let the Diatribe...
talk you to death![/sup][sub]
Just traded in my old subtlety...
for a NUANCE![/sub]

 

[Micash]

Hi Skip
Great answer - however, if a field is set as data type: Date - I can't see where one would set the field to accept Null values. Enlighten me, please.

Mike

 

[PHV]

In the table design view highlight the DOB field and have a look at the General tab of its properties.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[missinglinq]

I'm with Micash! There is no "Allow Null" in Table design for Date/Time, Text nor Number in 2003 and I don't remember one in v2000, but it's been a while! Text datatype has an "Allow Zero Length."

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[dhookom]

I think the property in the table design is "Required". If the field is Required, then it doesn't allow nulls. Set the Required property to No if you want to allow nulls.

If this doesn't reveal the answer, please provide more of your code and its context.

Duane
Hook'D on Access
MS Access MVP

 

[missinglinq]

We really need to know what the OP means by

I can't drive home a null value to the database

If he means he can't save/leave the record once it's set to Null, then I suspect Duane is correct. But he should be able to set it to Null while he's still on the record, before he does anything to save it.

The Missinglinq

Richmond, Virginia

There's ALWAYS more than one way to skin a cat!

 

[Micash]

Hi Guys
Perhaps my expression "drive home" is not good programming speak - but I really meant that I can't get the database DOB date field to accept a Null value when the textbox bound to that field is cleared. It does not respond at all to "" as well. The same kind of error (3426) is generated. I handle the error thus:
ErrorHandler:
If Err.Number = ...
....
ElseIf Err.Number = 3426 Then
MsgBox "Either the D.O.B field or Membership date fields are empty. Once a date has been entered previously, these fields cannot be left vacant.", vbCritical + vbSystemModal, "Date Missing"

Regards
Mike

 

[dhookom]

You still aren't provide much context or your code. Can we assume you are updating a recordset or are executing an insert or something?

Duane
Hook'D on Access
MS Access MVP

 

[Micash]

Yes, I am updating a recordeset. The code is not that complex. See below. Only there are are about 16 fields bound to text-boxes to update.

datPD.recordset.edit
datPD.recordset("DOB").value = txtPD(6).text
datPD.recordset.update

I wanted to do the following:

datPD.recordset.edit
if txtPD(6).text = "" then
datPD.recordset("DOB").value = Null
Else
datPD.recordset("DOB").value = txtPD(6).text
End if
datPD.recordset.update

Mike

 

[dhookom]

Have you tried setting a breakpoint in your code to see what is happening? Can we assume the error is generated on the datPD.Recordset.Update line?

Duane
Hook'D on Access
MS Access MVP

 

[Micash]

Yes - I have set breakpoints to follow what's happening all along the way. The error is generated immediately after the "datPD.recordset("DOB").value = Null" line.

 

[dhookom]

If the error happens on that line, do you get the error when there is a value in the date? Are you sure the recordset contains the field?

Duane
Hook'D on Access
MS Access MVP

 

[Micash]

Yup. These are my tables - my very own creation. DOB is data type 'date'. Other fields are mostly text fields with one 'yes/no' and one 'number' field (in this specific table).
I know I can work around this problem by making the DOB field a text field and insert dates like "20090424" and then display the info in a date format. The problem is that this is not the only table that has a date field, and in more than one place I do work with the dates that will require a global fix in the whole application. So the solution I was hoping to find would eliminate a lot of tedious and detailed remodelling.
Mike

 

[dhookom]

I can't believe this is so difficult. There must be thousands of VB apps that set date fields to Null. Have you tried the same code in Access? Keep in mind you won't use the "text" property of a control.

Since the error occurs prior to [tt][blue]
datPD.recordset.update[/blue][/tt]
I don't think the issue is with updating the table. Can you provide more of your code?

Duane
Hook'D on Access
MS Access MVP