How to handle empty date fields

[Pat]

Hi all,

You guys are the best, I've gotten so much good information from this
group that it's not even funny!

I'm writing an access database at work (that's going to be used
temporarily until the better client-server setup is completed) that
keeps track of every item in our stores, and allows the user to
manipulate some of the data.

I've created a column in the database of items called "audit_date" (of
type date) that keeps track of the last time an item had any of it's
information modified. When an item number is looked up and a row is
modified, I just store the system date in the audit-date field.

I want to show the last known audit_date on the screen with the other
relevant item related information after I've searched and pulled up an
item. My problem is that most items are not yet going to have anything
store in the audit_date field... in which case I want the system to
display "Never" in the text box that's linked to that field.

I assumed that this would be as easy as creating an if statement in VB
to the effect of:

If recordset!audit_date Is Null Then text_audit_date = "Never"
Else text_audit_date = recordset!audit_date
endif

But it doesn't appear to want to work that way. I need to understand
what is stored in an empty date field if there is no data. I apparently
can't compare looking for Nulls, the system doesn't seem to like that
too much.

Any thoughts?

 

[Keith Wilby]

Hi all,

You guys are the best, I've gotten so much good information from this
group that it's not even funny!

I'm writing an access database at work (that's going to be used
temporarily until the better client-server setup is completed) that
keeps track of every item in our stores, and allows the user to
manipulate some of the data.

I've created a column in the database of items called "audit_date" (of
type date) that keeps track of the last time an item had any of it's
information modified. When an item number is looked up and a row is
modified, I just store the system date in the audit-date field.

I want to show the last known audit_date on the screen with the other
relevant item related information after I've searched and pulled up an
item. My problem is that most items are not yet going to have anything
store in the audit_date field... in which case I want the system to
display "Never" in the text box that's linked to that field.

I assumed that this would be as easy as creating an if statement in VB
to the effect of:

If recordset!audit_date Is Null Then text_audit_date = "Never"
Else text_audit_date = recordset!audit_date
endif

But it doesn't appear to want to work that way. I need to understand
what is stored in an empty date field if there is no data. I apparently
can't compare looking for Nulls, the system doesn't seem to like that
too much.

Any thoughts?

Try using a calculated field in the underlying query:

Iif(IsNull([audit_date]),"Never",[audit_date])

HTH - Keith.
www.keithwilby.com

 

[Douglas J Steele]

Keith's shown you the easy work-around. I just thought I'd address your
original question.

In VBA, you use the IsNull function, such as "If
IsNull(recordset!audit_date) Then", rather than using the Is Null operator
(which is intended for SQL)

 

[Pat]

You guys are great, thank you!

Doug- Your solution was exactly what I needed, I was unaware of the
IsNull function. Sorry for asking such a noob question.