Go Back   Access World Forums > Microsoft Access Discussion > Reports

 
Reply
 
Thread Tools Rate Thread Display Modes
Old 10-14-2019, 12:40 PM   #1
ClaraBarton
Newly Registered User
 
Join Date: Oct 2019
Posts: 16
Thanks: 0
Thanked 0 Times in 0 Posts
ClaraBarton is on a distinguished road
Looping through fields by type

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.

ClaraBarton is offline   Reply With Quote
Old 10-14-2019, 01:22 PM   #2
theDBguy
Iím here to help
 
theDBguy's Avatar
 
Join Date: Oct 2018
Location: SunnySandyEggo
Posts: 5,799
Thanks: 57
Thanked 1,269 Times in 1,250 Posts
theDBguy has a spectacular aura about theDBguy has a spectacular aura about
Re: Looping through fields by type

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.
__________________
Just my 2 cents...

To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
|
To view links or images in signatures your post count must be 10 or greater. You currently have 0 posts.
theDBguy is online now   Reply With Quote
Old 10-14-2019, 06:22 PM   #3
Galaxiom
Super Moderator
 
Join Date: Jan 2009
Location: NSW Australia
Posts: 11,655
Thanks: 98
Thanked 1,500 Times in 1,415 Posts
Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold Galaxiom is a splendid one to behold
Re: Looping through fields by type

Quote:
Originally Posted by ClaraBarton View Post
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.

Galaxiom is offline   Reply With Quote
Old 10-14-2019, 10:46 PM   #4
Pat Hartman
Super Moderator
 
Join Date: Feb 2002
Location: Stratford,Ct USA
Posts: 28,253
Thanks: 15
Thanked 1,592 Times in 1,512 Posts
Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all Pat Hartman is a name known to all
Re: Looping through fields by type

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")

__________________
Bridge Players Still Know All the Tricks
Pat Hartman is offline   Reply With Quote
Reply

Thread Tools
Display Modes Rate This Thread
Rate This Thread:

Forum Jump

Similar Threads
Thread Thread Starter Forum Replies Last Post
Looping through all fields and saving metadata Cowboy_BeBa Modules & VBA 1 03-01-2017 03:40 AM
Continous Forms - Looping through records & Manipulating Fields On Form LarryB Forms 15 05-29-2015 06:54 AM
Looping fields within a report chok120 Reports 5 06-28-2007 08:31 AM
looping through table fields bjackson Tables 1 10-14-2004 05:06 PM
looping through fields Mcgrco Modules & VBA 2 02-05-2003 10:15 AM




All times are GMT -8. The time now is 12:44 PM.


Microsoft Access Help
General
Tables
Queries
Forms
Reports
Macros
Modules & VBA
Theory & Practice
Access FAQs
Code Repository
Sample Databases
Video Tutorials

Featured Forum post


Sponsored Links


Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
(c) copyright 2017 Access World