Date and "N/A"

[Ernst Guckel]

Hello,

Is there anyway to store both a date and the string "N/A" in a table?? I
want to use a date field because of the added benifits of this but there are
some cases that the value is "N/A"... so I don't know how to handle this...

Thanks,
Ernst.

 

[Rick Brandt]

Hello,

Is there anyway to store both a date and the string "N/A" in a
table?? I want to use a date field because of the added benifits of
this but there are some cases that the value is "N/A"... so I don't
know how to handle this...

You might be able to *format* the field so you see "N/A" instead of null,
but you cannot *store* N/A in a DateTime field.

 

[John W. Vinson]

Hello,

Is there anyway to store both a date and the string "N/A" in a table?? I
want to use a date field because of the added benifits of this but there are
some cases that the value is "N/A"... so I don't know how to handle this...

Thanks,
Ernst.

No. A Date (regardless of the format) is a double float number, not a text
string, and you can't store "N/A" in it.

The best you can do is leave such dates NULL in the table. I can't even think
of a good way to display Nulls as N/A and still have the field editable, but
maybe there is one!

 

[fredg]

Hello,

Is there anyway to store both a date and the string "N/A" in a table?? I
want to use a date field because of the added benifits of this but there are
some cases that the value is "N/A"... so I don't know how to handle this...

Thanks,
Ernst.

In your form, set the Format property of the date control to:

mm/d/yyyy;mm/d/yyyy;;"N/A"

This will not store the "N/A" in any table, just display it if the
date field is Null. Date values will display in the format of
03/2/2008

An alternative method to call attention to a Null value in the field
is to simply use conditional formatting and change the controls back
color it it's value is null. I believe you have already asked
elsewhere how to do this, and been answered.

 

[Armen Stein]

Hello,

Is there anyway to store both a date and the string "N/A" in a table?? I
want to use a date field because of the added benifits of this but there are
some cases that the value is "N/A"... so I don't know how to handle this...

Thanks,
Ernst.

All of the answers so far will result in all your "empty" dates being
treated as "N/A". But this isn't necessarily what you want. Null
dates may indicate that you haven't filled it in yet, but that isn't
the same as declaring that it is Not Applicable.

The best way to handle this is a separate Yes/No field for DateNA. The
user can check it when the Date is N/A, and your queries, reports and
forms can use this field along with the date to display what you want.

By the way, in the form you can Null out the date when the user checks
the NA box, and set the NA box to False when the user enters a Date.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

 

[Evi]

All of the answers so far will result in all your "empty" dates being
treated as "N/A". But this isn't necessarily what you want. Null
dates may indicate that you haven't filled it in yet, but that isn't
the same as declaring that it is Not Applicable.

The best way to handle this is a separate Yes/No field for DateNA. The
user can check it when the Date is N/A, and your queries, reports and
forms can use this field along with the date to display what you want.

By the way, in the form you can Null out the date when the user checks
the NA box, and set the NA box to False when the user enters a Date.

Armen Stein
Microsoft Access MVP
www.JStreetTech.com

and using Armen's suggestion, you can combine the 2 fields for display
purposes, in your reports with a calculated field in your query that says

DateNA: IIF ([YourCheckBox]=True,"N/A", IIF(IsNull([YourDateField]), "",
Format([YourDateField],"dd/mm/yyyy"))

Evi