This has to be basic but I'm #%$# at coding.

Have a field that will typically be a date (short date format). In some cases I need to put NA in this field but I can't with date format. Want to make it format cell so if "NA" is typed it will format as text, if not as "short date". Tired to use iif statements to no avail under format field in table design but it doesn't work for me.

Am I trying in wrong place??

Fields can only be formatted as one data type. What you need to do is to create a query based on the contents of your table.

In a column that you want to display "N/A" you will need the following

nDate:Nz(YourDateField,"N/A")

This will DISPLAY N/A in the datasheet for all records where there is a null value in the date field.

CodeMaster::cool:

Thanks for pointing out something that should have run over me like a truck. (guess I need even thicker glasses)

The only problem with the null field value is the field exists but is left open until a date is provided. (The field is a date when material is ordered but is not applicable to all projects) I will look at adding perhaps a check box that asks whether the material is needed or not and referencing that field for a positive value, unless you have a more elegant suggestion.

Thanks for getting me on track.


cnye (definitely NOT a code master)

In many systems you have to account for all possible senerios, but by having the field in place it will always be there if needed. As an example you may want a field to store the companies web address, but not all companies have websites. The field remains NULL for that company but when the get one you can enter it there.

So by having the date field there you can always reference it when required.

And don't worry I am not a code master to other people, someone will always know more than you do, and if that was not the case you would be talking to Bill Gates instead of me.

CodeMaster::cool:

I think you need the extra checkbox field. Because null can have two meanings - "not entered yet" and "never going to be entered", you need to distinguish.