Looping through fields by type

ClaraBarton

Registered User.
Local time
Yesterday, 16:38
Joined
Oct 14, 2019
Messages
666
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.
 
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:
Code:
IIf(Format([DateField],"mmdd")="0101",Year([DateField]),[DateField])
However, I'm not sure what kind (data type) of column you will get.
 
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.
 
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")
 

Users who are viewing this thread

Back
Top Bottom