Looping through fields by type

[ClaraBarton]

I have a database with many Date/Time fields. Oftentimes we don't have an exact date (geneology) but an approximation so we fill the field with 1/1/1861. I would like to loop through all fields of Date/Time type and do the following:
IIf(DatePart("m",[MINISTER])=1 And DatePart("d",[MINISTER])=1,Format([MINISTER],"yyyy"),[MINISTER])
In other words, if the field has a month of 1 and a day of 1, change the format to year only in the report. This formula works in a query on each field but I wondered if there's a better way.

 

[theDBguy]

Hi Clara. Welcome to AWF! A better way is to normalize your table structure, if it isn't already normalized. Otherwise, you could try something like:

IIf(Format([DateField],"mmdd")="0101",Year([DateField]),[DateField])

However, I'm not sure what kind (data type) of column you will get.

 

[Galaxiom]

In other words, if the field has a month of 1 and a day of 1, change the format to year only in the report. This formula works in a query on each field but I wondered if there's a better way.

So if the date really is January 1st, you cannot show it as such. Not a good solution.

I would probably have another field to indicate the date field is approximate. Maybe even an indication of the precision so that it could record that the month known but not the exact date.

 

[Pat Hartman]

Since you can't use the format property of the control on the report, you need to format the date in the query.

IIf(Format([DateField],"mmdd")="0101",Year([DateField]),Format([DateField], "Short Date")