Looping through fields by type (1 Viewer)

ClaraBarton

Registered User.
Local time
Today, 08:36
Joined
Oct 14, 2019
Messages
427
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

I’m here to help
Staff member
Local time
Today, 08:36
Joined
Oct 29, 2018
Messages
21,358
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.
 

Galaxiom

Super Moderator
Staff member
Local time
Tomorrow, 02:36
Joined
Jan 20, 2009
Messages
12,849
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

Super Moderator
Staff member
Local time
Today, 11:36
Joined
Feb 19, 2002
Messages
42,970
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

Top Bottom