•
•
•
•
What is DaniWeb IT Discussion Community?
You're currently browsing the MS Access and FileMaker Pro section within the Web Development category of DaniWeb, a massive community of 423,507 software developers, web developers, Internet marketers, and tech gurus who are all enthusiastic about making contacts, networking, and learning from each other. In fact, there are 4,664 IT professionals currently interacting right now! Registration is free, only takes a minute and lets you enjoy all of the interactive features of the site.
Please support our MS Access and FileMaker Pro advertiser: Programming Forums
Views: 1146 | Replies: 1
 |
•
•
Join Date: Sep 2007
Posts: 2
Reputation: 
Rep Power: 0
Solved Threads: 0
Newbie Poster
Using FileMaker Pro 8 I have a table whit a column for dates, like showing below.
dates
Mon, Sept 10, 2007
Wed, Sept 12, 2007
Wed, Sept 19, 2007
Mon, Oct 8, 2007
Mon, Oct 15, 2007
I want a field(s) to show all those records like this
Sept 10, 12, 19 Oct 8, 15
I will appreciate your help
dates
Mon, Sept 10, 2007
Wed, Sept 12, 2007
Wed, Sept 19, 2007
Mon, Oct 8, 2007
Mon, Oct 15, 2007
I want a field(s) to show all those records like this
Sept 10, 12, 19 Oct 8, 15
I will appreciate your help
•
•
Join Date: Oct 2007
Posts: 1
Reputation:
Rep Power: 0
Solved Threads: 0
Newbie Poster
First, ensure that the field containing your dates is of the DATE type.
File > Define > Database... Choose your table from the dropdown. Look at the field containing your dates. Under Type, it should say Date. If not, select the field hold down Ctrl + D or choose Date from the Type dropdown, press Enter and Choose OK. You may also want to change the name of this field to DATE to ease entering of the calculation below.
Next, create another field. Again, you can name it anything but I called it DATE_LIST.
Stil in the define database bialog, type DATE_LIST into the field name textbox, choose Calculation from the dropdown and click Create.
Copy this code and paste it into the field:
Before closing the calculation screen, click Storage Options... and check Do not store calculation results -- recalculate when needed
This formula only shows seven dates. To add to this number, copy and paste the red text below the existing code. Increment each bold number within the red text by one. For more, repeat.
You must perform a find for your targeted dates then, sort by DATE (ascending) for this formula to appear properly.
The abbreviations I used for the dates are as follows:
Jan
Feb
March
April
May
June
July
Aug
Sept
Nov
Dec
For further assistance, please feel free to hop over to my FM blog at laurenkuhlman.wordpress.com I have lots of handy calulations there. May I also suggest fmforums.com.
File > Define > Database... Choose your table from the dropdown. Look at the field containing your dates. Under Type, it should say Date. If not, select the field hold down Ctrl + D or choose Date from the Type dropdown, press Enter and Choose OK. You may also want to change the name of this field to DATE to ease entering of the calculation below.
Next, create another field. Again, you can name it anything but I called it DATE_LIST.
Stil in the define database bialog, type DATE_LIST into the field name textbox, choose Calculation from the dropdown and click Create.
Copy this code and paste it into the field:
If(Month(GetNthRecord ( DATE ; 1 ))=1;"Jan";
If(Month(GetNthRecord ( DATE ; 1 ))=2;"Feb";
If(Month(GetNthRecord ( DATE ; 1 ))=3;"March";
If(Month(GetNthRecord ( DATE ; 1 ))=4;"April";
If(Month(GetNthRecord ( DATE ; 1 ))=5;"May";
If(Month(GetNthRecord ( DATE ; 1 ))=6;"June";
If(Month(GetNthRecord ( DATE ; 1 ))=7;"July";
If(Month(GetNthRecord ( DATE ; 1 ))=8;"Aug";
If(Month(GetNthRecord ( DATE ; 1 ))=9;"Sept";
If(Month(GetNthRecord ( DATE ; 1 ))=10;"Oct";
If(Month(GetNthRecord ( DATE ; 1 ))=11;"Nov";
If(Month(GetNthRecord ( DATE ; 1 ))=12;"Dec";""
)))))))))))) & " " & Day(GetNthRecord ( DATE ; 1 ))
&
If(Get ( FoundCount )>1;
If(Month(GetNthRecord ( DATE ; 1 ))=Month(GetNthRecord ( DATE ; 2 ));", " & Day(GetNthRecord ( DATE ; 2 ));" " &
If(Month(GetNthRecord ( DATE ; 2 ))=1;"Jan";
If(Month(GetNthRecord ( DATE ; 2 ))=2;"Feb";
If(Month(GetNthRecord ( DATE ; 2 ))=3;"March";
If(Month(GetNthRecord ( DATE ; 2 ))=4;"April";
If(Month(GetNthRecord ( DATE ; 2 ))=5;"May";
If(Month(GetNthRecord ( DATE ; 2 ))=6;"June";
If(Month(GetNthRecord ( DATE ; 2 ))=7;"July";
If(Month(GetNthRecord ( DATE ; 2 ))=8;"Aug";
If(Month(GetNthRecord ( DATE ; 2 ))=9;"Sept";
If(Month(GetNthRecord ( DATE ; 2 ))=10;"Oct";
If(Month(GetNthRecord ( DATE ; 2 ))=11;"Nov";
If(Month(GetNthRecord ( DATE ; 2 ))=12;"Dec";""
)))))))))))) & " " & Day(GetNthRecord ( DATE ; 2 ))))
&
If(Get ( FoundCount )>2;
If(Month(GetNthRecord ( DATE ; 2 ))=Month(GetNthRecord ( DATE ; 3 ));", " & Day(GetNthRecord ( DATE ; 3 ));" " &
If(Month(GetNthRecord ( DATE ; 3 ))=1;"Jan";
If(Month(GetNthRecord ( DATE ; 3 ))=2;"Feb";
If(Month(GetNthRecord ( DATE ; 3 ))=3;"March";
If(Month(GetNthRecord ( DATE ; 3 ))=4;"April";
If(Month(GetNthRecord ( DATE ; 3 ))=5;"May";
If(Month(GetNthRecord ( DATE ; 3 ))=6;"June";
If(Month(GetNthRecord ( DATE ; 3 ))=7;"July";
If(Month(GetNthRecord ( DATE ; 3 ))=8;"Aug";
If(Month(GetNthRecord ( DATE ; 3 ))=9;"Sept";
If(Month(GetNthRecord ( DATE ; 3 ))=10;"Oct";
If(Month(GetNthRecord ( DATE ; 3 ))=11;"Nov";
If(Month(GetNthRecord ( DATE ; 3 ))=12;"Dec";""
)))))))))))) & " " & Day(GetNthRecord ( DATE ; 3 ))))
&
If(Get ( FoundCount )>3;
If(Month(GetNthRecord ( DATE ; 3 ))=Month(GetNthRecord ( DATE ; 4 ));", " & Day(GetNthRecord ( DATE ; 4 ));" " &
If(Month(GetNthRecord ( DATE ; 4 ))=1;"Jan";
If(Month(GetNthRecord ( DATE ; 4 ))=2;"Feb";
If(Month(GetNthRecord ( DATE ; 4 ))=3;"March";
If(Month(GetNthRecord ( DATE ; 4 ))=4;"April";
If(Month(GetNthRecord ( DATE ; 4 ))=5;"May";
If(Month(GetNthRecord ( DATE ; 4 ))=6;"June";
If(Month(GetNthRecord ( DATE ; 4 ))=7;"July";
If(Month(GetNthRecord ( DATE ; 4 ))=8;"Aug";
If(Month(GetNthRecord ( DATE ; 4 ))=9;"Sept";
If(Month(GetNthRecord ( DATE ; 4 ))=10;"Oct";
If(Month(GetNthRecord ( DATE ; 4 ))=11;"Nov";
If(Month(GetNthRecord ( DATE ; 4 ))=12;"Dec";""
)))))))))))) & " " & Day(GetNthRecord ( DATE ; 4 ))))
&
If(Get ( FoundCount )>4;
If(Month(GetNthRecord ( DATE ; 4 ))=Month(GetNthRecord ( DATE ; 5 ));", " & Day(GetNthRecord ( DATE ; 5 ));" " &
If(Month(GetNthRecord ( DATE ; 5 ))=1;"Jan";
If(Month(GetNthRecord ( DATE ; 5 ))=2;"Feb";
If(Month(GetNthRecord ( DATE ; 5 ))=3;"March";
If(Month(GetNthRecord ( DATE ; 5 ))=4;"April";
If(Month(GetNthRecord ( DATE ; 5 ))=5;"May";
If(Month(GetNthRecord ( DATE ; 5 ))=6;"June";
If(Month(GetNthRecord ( DATE ; 5 ))=7;"July";
If(Month(GetNthRecord ( DATE ; 5 ))=8;"Aug";
If(Month(GetNthRecord ( DATE ; 5 ))=9;"Sept";
If(Month(GetNthRecord ( DATE ; 5 ))=10;"Oct";
If(Month(GetNthRecord ( DATE ; 5 ))=11;"Nov";
If(Month(GetNthRecord ( DATE ; 5 ))=12;"Dec";""
)))))))))))) & " " & Day(GetNthRecord ( DATE ; 5 ))))
&
If(Get ( FoundCount )>5;
If(Month(GetNthRecord ( DATE ; 5 ))=Month(GetNthRecord ( DATE ; 6 ));", " & Day(GetNthRecord ( DATE ; 6 ));" " &
If(Month(GetNthRecord ( DATE ; 6 ))=1;"Jan";
If(Month(GetNthRecord ( DATE ; 6 ))=2;"Feb";
If(Month(GetNthRecord ( DATE ; 6 ))=3;"March";
If(Month(GetNthRecord ( DATE ; 6 ))=4;"April";
If(Month(GetNthRecord ( DATE ; 6 ))=5;"May";
If(Month(GetNthRecord ( DATE ; 6 ))=6;"June";
If(Month(GetNthRecord ( DATE ; 6 ))=7;"July";
If(Month(GetNthRecord ( DATE ; 6 ))=8;"Aug";
If(Month(GetNthRecord ( DATE ; 6 ))=9;"Sept";
If(Month(GetNthRecord ( DATE ; 6 ))=10;"Oct";
If(Month(GetNthRecord ( DATE ; 6 ))=11;"Nov";
If(Month(GetNthRecord ( DATE ; 6 ))=12;"Dec";""
)))))))))))) & " " & Day(GetNthRecord ( DATE ; 6 ))))
&
If(Get ( FoundCount )>6;
If(Month(GetNthRecord ( DATE ; 6 ))=Month(GetNthRecord ( DATE ; 7 ));", " & Day(GetNthRecord ( DATE ; 7 ));" " &
If(Month(GetNthRecord ( DATE ; 7 ))=1;"Jan";
If(Month(GetNthRecord ( DATE ; 7 ))=2;"Feb";
If(Month(GetNthRecord ( DATE ; 7 ))=3;"March";
If(Month(GetNthRecord ( DATE ; 7 ))=4;"April";
If(Month(GetNthRecord ( DATE ; 7 ))=5;"May";
If(Month(GetNthRecord ( DATE ; 7 ))=6;"June";
If(Month(GetNthRecord ( DATE ; 7 ))=7;"July";
If(Month(GetNthRecord ( DATE ; 7 ))=8;"Aug";
If(Month(GetNthRecord ( DATE ; 7 ))=9;"Sept";
If(Month(GetNthRecord ( DATE ; 7 ))=10;"Oct";
If(Month(GetNthRecord ( DATE ; 7 ))=11;"Nov";
If(Month(GetNthRecord ( DATE ; 7 ))=12;"Dec";""
)))))))))))) & " " & Day(GetNthRecord ( DATE ; 7 ))))
Before closing the calculation screen, click Storage Options... and check Do not store calculation results -- recalculate when needed
This formula only shows seven dates. To add to this number, copy and paste the red text below the existing code. Increment each bold number within the red text by one. For more, repeat.
You must perform a find for your targeted dates then, sort by DATE (ascending) for this formula to appear properly.
The abbreviations I used for the dates are as follows:
Jan
Feb
March
April
May
June
July
Aug
Sept
Nov
Dec
For further assistance, please feel free to hop over to my FM blog at laurenkuhlman.wordpress.com I have lots of handy calulations there. May I also suggest fmforums.com.
|
•
•
•
•
•
•
•
•
DaniWeb MS Access and FileMaker Pro Marketplace
•
•
•
•
Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
Linear Mode
- Disable CD-ROM drive through Group Policy Win 2000 (Windows NT / 2000 / XP / 2003)
- Zend PHP Certification (PHP)
- Shine Media Group - Company Website (Website Reviews)
- The Return of about:blank (Viruses, Spyware and other Nasties)
- Group files by Artist in XP (Windows NT / 2000 / XP / 2003)
- dates (Visual Basic 4 / 5 / 6)
- Group Project Ideas (Geeks' Lounge)
Other Threads in the MS Access and FileMaker Pro Forum
- Previous Thread: how to send automated email alerts daily using msaccess
- Next Thread: joining 2002 workgroups in 2007
