Cardinal header issue

[Guest]

Hi. I have a report that has a Years of Service header based on this
calcualted field-

Years of Service: Diff2Dates("my",[MemberDate],[Service Reference date:
(MM/DD/YY)],True)

Everything seemed fine until the years of service got into the double
digits. Now the header is grouping every number that begins with a "1" into
the same heading. Example:
under the 11 year of Service heading:
11 years 3 months...
10 years 0 months...
1 year 4 months..

Do I need to say that 1 year of service is "01"? How would I do that? Or
better yet, I'd appreciate suggestions for the handling of cardinal issues in
headers. Thanks for your help!

 

[Guest]

Add another field/column in your record source with a calculation like:
SortingMth: DateDiff("m",[MemberDate],[Service Reference date: (MM/DD/YY)])
Use this column to sort/group in your report.

 

[Guest]

Interesting. Not quite what I'm looking for so I didn't explain well.

The report needs to be grouped by years of service.
Then, the detail section lists the length of service
Length of Service: Diff2Dates("ymd",[MemberDate],[Service Reference date:
(MM/DD/YY)],True)

So I'm getting
Year of Service (grouping), with Length of Service listing
4 years
4 years 5 months 1 day
3 years
3 years 2 months 6 days
3 years 1 month 3 days
2 years
2 years 3 months 4 days
11 years
11 years 3 months 16 days
10 years 0 months 1 day
1 year 4 months 0 days

I'd appreciate additional suggestions. Thanks!

Add another field/column in your record source with a calculation like:
SortingMth: DateDiff("m",[MemberDate],[Service Reference date: (MM/DD/YY)])
Use this column to sort/group in your report.
--
Duane Hookom
Microsoft Access MVP

Hi. I have a report that has a Years of Service header based on this
calcualted field-

Years of Service: Diff2Dates("my",[MemberDate],[Service Reference date:
(MM/DD/YY)],True)

Everything seemed fine until the years of service got into the double
digits. Now the header is grouping every number that begins with a "1" into
the same heading. Example:
under the 11 year of Service heading:
11 years 3 months...
10 years 0 months...
1 year 4 months..

Do I need to say that 1 year of service is "01"? How would I do that? Or
better yet, I'd appreciate suggestions for the handling of cardinal issues in
headers. Thanks for your help!

 

[Guest]

You can't sort/group on the text value returned by the Diff2Dates() funciton.
Ignore it entirely in your sorting and grouping. Create your own expression
for sorting and grouping. If you want to group by the years then use the
function found at http://www.mvps.org/access/datetime/date0001.htm. Further
sorting can be performed on another expression which would depend on if the
Service Reference Date is the same for every member.

--
Duane Hookom
Microsoft Access MVP

Interesting. Not quite what I'm looking for so I didn't explain well.

The report needs to be grouped by years of service.
Then, the detail section lists the length of service
Length of Service: Diff2Dates("ymd",[MemberDate],[Service Reference date:
(MM/DD/YY)],True)

So I'm getting
Year of Service (grouping), with Length of Service listing
4 years
4 years 5 months 1 day
3 years
3 years 2 months 6 days
3 years 1 month 3 days
2 years
2 years 3 months 4 days
11 years
11 years 3 months 16 days
10 years 0 months 1 day
1 year 4 months 0 days

I'd appreciate additional suggestions. Thanks!

Add another field/column in your record source with a calculation like:
SortingMth: DateDiff("m",[MemberDate],[Service Reference date: (MM/DD/YY)])
Use this column to sort/group in your report.
--
Duane Hookom
Microsoft Access MVP

Hi. I have a report that has a Years of Service header based on this
calcualted field-

Years of Service: Diff2Dates("my",[MemberDate],[Service Reference date:
(MM/DD/YY)],True)

Everything seemed fine until the years of service got into the double
digits. Now the header is grouping every number that begins with a "1" into
the same heading. Example:
under the 11 year of Service heading:
11 years 3 months...
10 years 0 months...
1 year 4 months..

Do I need to say that 1 year of service is "01"? How would I do that? Or
better yet, I'd appreciate suggestions for the handling of cardinal issues in
headers. Thanks for your help!

 

[Guest]

Thanks for the tips. The Service Reference Date is the same for every member
(although it needs to be an input parameter that the user can fill enter
because the reference date will change every year).

I am running my report built from a query. I've not used functions for
reports and I'm not quite sure how to integrate code with the sql that I have
and the input parameter. I also don't have the Public Sub CalcAge in my
modules. Sorry- this is all a bit new.

I appreciate your advice.

You can't sort/group on the text value returned by the Diff2Dates() funciton.
Ignore it entirely in your sorting and grouping. Create your own expression
for sorting and grouping. If you want to group by the years then use the
function found at http://www.mvps.org/access/datetime/date0001.htm. Further
sorting can be performed on another expression which would depend on if the
Service Reference Date is the same for every member.

--
Duane Hookom
Microsoft Access MVP

Interesting. Not quite what I'm looking for so I didn't explain well.

The report needs to be grouped by years of service.
Then, the detail section lists the length of service
Length of Service: Diff2Dates("ymd",[MemberDate],[Service Reference date:
(MM/DD/YY)],True)

So I'm getting
Year of Service (grouping), with Length of Service listing
4 years
4 years 5 months 1 day
3 years
3 years 2 months 6 days
3 years 1 month 3 days
2 years
2 years 3 months 4 days
11 years
11 years 3 months 16 days
10 years 0 months 1 day
1 year 4 months 0 days

I'd appreciate additional suggestions. Thanks!

Add another field/column in your record source with a calculation like:
SortingMth: DateDiff("m",[MemberDate],[Service Reference date: (MM/DD/YY)])
Use this column to sort/group in your report.
--
Duane Hookom
Microsoft Access MVP


:

Hi. I have a report that has a Years of Service header based on this
calcualted field-

Years of Service: Diff2Dates("my",[MemberDate],[Service Reference date:
(MM/DD/YY)],True)

Everything seemed fine until the years of service got into the double
digits. Now the header is grouping every number that begins with a "1" into
the same heading. Example:
under the 11 year of Service heading:
11 years 3 months...
10 years 0 months...
1 year 4 months..

Do I need to say that 1 year of service is "01"? How would I do that? Or
better yet, I'd appreciate suggestions for the handling of cardinal issues in
headers. Thanks for your help!