 |  |  |  |  |  |  |  |
FileMaker group of dates
Please support our MS Access and FileMaker Pro advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases
 |
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
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.
|
Similar Threads
- Disable CD-ROM drive through Group Policy Win 2000 (Windows NT / 2000 / XP)
- 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)
- 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
| Thread Tools | Search this Thread |
Latest MS Access and FileMaker Pro Posts
- Today's Posts
- Unanswered Threads
Related Forum Features
Tag cloud for MS Access and FileMaker Pro
