Access changes value to 00:00:00

[garyholc]

Hi

In my form I have a function where the user can say if a date is not required, for example, if the date something was returned is not needed, they press a button, the field is made black, but in order to identify these records later on, I placed the value of 30/12/1899 in the field.

My idea was when I ever run queries on the data, I can create a calculated field to change this value to NA.

I made a query, which uses one of these fields and wrote the statement,

iif(year([date_returned])=1899,"NA",[date_returned])

The problem is, the statement seems to work, but instead of writing NA in the field, it writes 00:00:00. I am guessing it is because the field is a date format, but is there anyway of writing into this statement that I want the value returned as NA if 1899 exists in the field?

Thanks
Gary

 

[namliam]

Well to start with doing this would then be smarter
iif(year([date_returned])=1899,"NA",Format([date_returned], "DD-MM-YYYY"))

Because now both true and false outcome are text, thus preventing any conflict in the type of the field and causing implicit conversions.

Next in your report make sure your field is set to a text, not to a date/time format.

This should resolve your issue.