Dates driving me to drink.

[NC_Sue]

I have a contacts database in which I have birthdays listed for some (not
all) of the contacts and in which years of birth are know for some (not all)
of the contacts. I have 2 date fields I'm working with: DOB (full date of
birth), format mmmm dd yyyy, and MonthDayOnly, format mmmm dd. What I want to
do is design a query that will allow me to design a report which will group
on month, then sort by date within month, then sort by last name if multiple
contacts are have the same birthday. Using the MonthDayOnly field, everything
sorts correctly. But when I try to manipulate queried data to produce the
sort of sort I want, everything gets bollixed up. I think it's because I know
the year of birth for some - not all - of my contacts & therefore it's
sorting by year, then month, then day... but if that's the case, the
MonthDayOnly sort should be screwy too. Can you help?

(PS - If I sound incoherent & confused, it's because I'm completely
confused!!!)

 

[Jerry Whittle]

Me too. Especially December 31st and January 1st. ;-)

Sounds like you might have seperate fields for MonthDayOnly and DOB. If so,
not good. You only need the Date of Birth (DOB) field to do all of your
calculations. Dump the MonthDayOnly. The DOB field must be a Date/Time
datatype however. Seems some of the individuals don't want to give it up for
the year? In that case use something bogus like 1900. If they don't want to
tell, let them by over 100 years old!

In your query bring down the DOB field two extra times. Change one of them
to read Months: Month([DOB]) and the other to Days: Day([DOB]) . This will
give you the numerical value of the month and days. Do not bother sorting in
the query.

Now in the report based on the above query, Group On the Months data; within
the Months group on the Days; then sort on the Names. By default both the
Months and Days should sort properly.

There you go.

 

[Rick B]

First, you should revise your design a bit. If you have a birthdate, you
must fill in two separate fields with basically the same data. That is poor
database design.

Either break it into three fields (BirthDay, BirthMonth, BirthYear) or just
have one field. I personally store the birthdate as a date. If I do not
know the year, I always use 1900. Then I can use if statements to say "if
the birthyear is 1900, don't print the age".

To sort, either do it in your report's grouping and soprting by entering (in
the first group)

=Month(BirthDate)

Then in the second sort:
=Date(Birthdate)

Then in the third sort:
[LastName]


In my birthday lists, I include a group header for the month so my report
looks kinda like this...



January:
01 Smith, John 48 years old
06 Adams, Bob unknown
06 Jones, Nancy 36 years old
15 Branden, Lisa 23 years old

February
14 Love, Bradley unknown


etc.

 

[BruceM]

When I say the subject line I thought it was a complaint about bad dates
that had found its way into the wrong newsgroup. Then I saw that it was
frustration with Access. What I did in a very similar situation was to add
a new field to the query:
BirthdaySort: DateSerial(Year(Date()),Month([Birthday]),Day([Birthday]))
[Birthday] is a date field in the table. I grouped on this field, sorted
ascending. It takes the month and day of a birthday, and adds the current
year.
I also added a BirthdayDay field to the query:
BirthdayDay: Format([Birthday],"dd")
I sorted on that in the Report's Sorting & Grouping box. Then I sorted by
last name. You could also sort within the query, if that helps. The thing
that ended up working for me was contriving new date fields in the query.
You haven't mentioned if your full date field and your month/day field are
date fields. If they are, and if you are entering just the month and day
into the month/day field, I believe that it will default to the current
year. Try changing the field's format to the full date to see if that is
the case. If it is, the sort order you ended up with probably makes sense.
I assume you are using a form to enter the information. In the After Update
event of the text box that is bound to the FullDate field, you could add
code to copy the number to the MonthDay field, something like:
Me.txtMonthDay = Me.txtFullDate
where txtMonthDay is the text box bound to the month/day field.
This means you will have two date fields for the same date, but if you are
displaying the full date when it is available then I can't really see a way
around the duplication. Just be sure that you don't use the month/day field
for determining ages or anything like that.
I expect there is a better way, but this is what I came up with.

 

[Rick B]

Note: I think it should say Day(BirthDate) below, not Date(BirthDate).



--
Rick B

First, you should revise your design a bit. If you have a birthdate, you
must fill in two separate fields with basically the same data. That is
poor database design.

Either break it into three fields (BirthDay, BirthMonth, BirthYear) or
just have one field. I personally store the birthdate as a date. If I do
not know the year, I always use 1900. Then I can use if statements to say
"if the birthyear is 1900, don't print the age".

To sort, either do it in your report's grouping and soprting by entering
(in the first group)

=Month(BirthDate)

Then in the second sort:
=Date(Birthdate)

Then in the third sort:
[LastName]


In my birthday lists, I include a group header for the month so my report
looks kinda like this...



January:
01 Smith, John 48 years old
06 Adams, Bob unknown
06 Jones, Nancy 36 years old
15 Branden, Lisa 23 years old

February
14 Love, Bradley unknown


etc.



--
Rick B

I have a contacts database in which I have birthdays listed for some (not
all) of the contacts and in which years of birth are know for some (not
all)
of the contacts. I have 2 date fields I'm working with: DOB (full date of
birth), format mmmm dd yyyy, and MonthDayOnly, format mmmm dd. What I
want to
do is design a query that will allow me to design a report which will
group
on month, then sort by date within month, then sort by last name if
multiple
contacts are have the same birthday. Using the MonthDayOnly field,
everything
sorts correctly. But when I try to manipulate queried data to produce the
sort of sort I want, everything gets bollixed up. I think it's because I
know
the year of birth for some - not all - of my contacts & therefore it's
sorting by year, then month, then day... but if that's the case, the
MonthDayOnly sort should be screwy too. Can you help?

(PS - If I sound incoherent & confused, it's because I'm completely
confused!!!)

 

[NC_Sue]

Thanks to all of you responding to this thread. Between your responses, I've
gotten closer to my goal... BUT...

I'm trying to sort first by month, then by day, then by last name with
section header for month. I've given everyone the same bogus year of birth
for this purpose (1900). Any idea why I've got 2 Aprils? And 2 Julys? And 2
Decembers?
--
Thanks for your time!

First, you should revise your design a bit. If you have a birthdate, you
must fill in two separate fields with basically the same data. That is poor
database design.

Either break it into three fields (BirthDay, BirthMonth, BirthYear) or just
have one field. I personally store the birthdate as a date. If I do not
know the year, I always use 1900. Then I can use if statements to say "if
the birthyear is 1900, don't print the age".

To sort, either do it in your report's grouping and soprting by entering (in
the first group)

=Month(BirthDate)

Then in the second sort:
=Date(Birthdate)

Then in the third sort:
[LastName]


In my birthday lists, I include a group header for the month so my report
looks kinda like this...



January:
01 Smith, John 48 years old
06 Adams, Bob unknown
06 Jones, Nancy 36 years old
15 Branden, Lisa 23 years old

February
14 Love, Bradley unknown


etc.



--
Rick B

I have a contacts database in which I have birthdays listed for some (not
all) of the contacts and in which years of birth are know for some (not
all)
of the contacts. I have 2 date fields I'm working with: DOB (full date of
birth), format mmmm dd yyyy, and MonthDayOnly, format mmmm dd. What I want
to
do is design a query that will allow me to design a report which will
group
on month, then sort by date within month, then sort by last name if
multiple
contacts are have the same birthday. Using the MonthDayOnly field,
everything
sorts correctly. But when I try to manipulate queried data to produce the
sort of sort I want, everything gets bollixed up. I think it's because I
know
the year of birth for some - not all - of my contacts & therefore it's
sorting by year, then month, then day... but if that's the case, the
MonthDayOnly sort should be screwy too. Can you help?

(PS - If I sound incoherent & confused, it's because I'm completely
confused!!!)

 

[Rick B]

If you use the grouping and sorting and sort on Month([BirthDate]) and
include a group header, then you should get what you want. Then in the
second grouping and sorting, use Day([Birthdate])... As stated in my
previous post.

Assuming you have a simple report that pulls all the records, and you use
the grouping and sorting I mentioned, and you include only a group header on
the MONTH, then you should be fine.

Did you remove the extra field from your design, as mentioned by several
folks?

If you DON'T do any grouping or sorting, do you get one line per record as
you'd expect?

If you add the grouping without any headers do you still get the results
you'd expect?

If you then add the header, do you get what you'd expect?

Break it down and add one element at a time until you figure out where the
problem is coming in.

--
Rick B

Thanks to all of you responding to this thread. Between your responses,
I've
gotten closer to my goal... BUT...

I'm trying to sort first by month, then by day, then by last name with
section header for month. I've given everyone the same bogus year of birth
for this purpose (1900). Any idea why I've got 2 Aprils? And 2 Julys? And
2
Decembers?
--
Thanks for your time!

First, you should revise your design a bit. If you have a birthdate, you
must fill in two separate fields with basically the same data. That is
poor
database design.

Either break it into three fields (BirthDay, BirthMonth, BirthYear) or
just
have one field. I personally store the birthdate as a date. If I do not
know the year, I always use 1900. Then I can use if statements to say
"if
the birthyear is 1900, don't print the age".

To sort, either do it in your report's grouping and soprting by entering
(in
the first group)

=Month(BirthDate)

Then in the second sort:
=Date(Birthdate)

Then in the third sort:
[LastName]


In my birthday lists, I include a group header for the month so my report
looks kinda like this...



January:
01 Smith, John 48 years old
06 Adams, Bob unknown
06 Jones, Nancy 36 years old
15 Branden, Lisa 23 years old

February
14 Love, Bradley unknown


etc.



--
Rick B

I have a contacts database in which I have birthdays listed for some
(not
all) of the contacts and in which years of birth are know for some (not
all)
of the contacts. I have 2 date fields I'm working with: DOB (full date
of
birth), format mmmm dd yyyy, and MonthDayOnly, format mmmm dd. What I
want
to
do is design a query that will allow me to design a report which will
group
on month, then sort by date within month, then sort by last name if
multiple
contacts are have the same birthday. Using the MonthDayOnly field,
everything
sorts correctly. But when I try to manipulate queried data to produce
the
sort of sort I want, everything gets bollixed up. I think it's because
I
know
the year of birth for some - not all - of my contacts & therefore it's
sorting by year, then month, then day... but if that's the case, the
MonthDayOnly sort should be screwy too. Can you help?

(PS - If I sound incoherent & confused, it's because I'm completely
confused!!!)

 

[Jerry Whittle]

Need to see the SQL for the query to answer this one.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks to all of you responding to this thread. Between your responses, I've
gotten closer to my goal... BUT...

I'm trying to sort first by month, then by day, then by last name with
section header for month. I've given everyone the same bogus year of birth
for this purpose (1900). Any idea why I've got 2 Aprils? And 2 Julys? And 2
Decembers?
--
Thanks for your time!

First, you should revise your design a bit. If you have a birthdate, you
must fill in two separate fields with basically the same data. That is poor
database design.

Either break it into three fields (BirthDay, BirthMonth, BirthYear) or just
have one field. I personally store the birthdate as a date. If I do not
know the year, I always use 1900. Then I can use if statements to say "if
the birthyear is 1900, don't print the age".

To sort, either do it in your report's grouping and soprting by entering (in
the first group)

=Month(BirthDate)

Then in the second sort:
=Date(Birthdate)

Then in the third sort:
[LastName]


In my birthday lists, I include a group header for the month so my report
looks kinda like this...



January:
01 Smith, John 48 years old
06 Adams, Bob unknown
06 Jones, Nancy 36 years old
15 Branden, Lisa 23 years old

February
14 Love, Bradley unknown


etc.



--
Rick B

I have a contacts database in which I have birthdays listed for some (not
all) of the contacts and in which years of birth are know for some (not
all)
of the contacts. I have 2 date fields I'm working with: DOB (full date of
birth), format mmmm dd yyyy, and MonthDayOnly, format mmmm dd. What I want
to
do is design a query that will allow me to design a report which will
group
on month, then sort by date within month, then sort by last name if
multiple
contacts are have the same birthday. Using the MonthDayOnly field,
everything
sorts correctly. But when I try to manipulate queried data to produce the
sort of sort I want, everything gets bollixed up. I think it's because I
know
the year of birth for some - not all - of my contacts & therefore it's
sorting by year, then month, then day... but if that's the case, the
MonthDayOnly sort should be screwy too. Can you help?

(PS - If I sound incoherent & confused, it's because I'm completely
confused!!!)

 

[NC_Sue]

Yes, Rick, I did remove the naughty fields.
If I don't group/sort, I get what I expect.
If I group/sort, I get what I expect with the exception of 2 Aprils, 2
Julys, & 2 Decembers... which perplexes me no end.
--
Thanks for your time!

If you use the grouping and sorting and sort on Month([BirthDate]) and
include a group header, then you should get what you want. Then in the
second grouping and sorting, use Day([Birthdate])... As stated in my
previous post.

Assuming you have a simple report that pulls all the records, and you use
the grouping and sorting I mentioned, and you include only a group header on
the MONTH, then you should be fine.

Did you remove the extra field from your design, as mentioned by several
folks?

If you DON'T do any grouping or sorting, do you get one line per record as
you'd expect?

If you add the grouping without any headers do you still get the results
you'd expect?

If you then add the header, do you get what you'd expect?

Break it down and add one element at a time until you figure out where the
problem is coming in.

--
Rick B

Thanks to all of you responding to this thread. Between your responses,
I've
gotten closer to my goal... BUT...

I'm trying to sort first by month, then by day, then by last name with
section header for month. I've given everyone the same bogus year of birth
for this purpose (1900). Any idea why I've got 2 Aprils? And 2 Julys? And
2
Decembers?
--
Thanks for your time!

First, you should revise your design a bit. If you have a birthdate, you
must fill in two separate fields with basically the same data. That is
poor
database design.

Either break it into three fields (BirthDay, BirthMonth, BirthYear) or
just
have one field. I personally store the birthdate as a date. If I do not
know the year, I always use 1900. Then I can use if statements to say
"if
the birthyear is 1900, don't print the age".

To sort, either do it in your report's grouping and soprting by entering
(in
the first group)

=Month(BirthDate)

Then in the second sort:
=Date(Birthdate)

Then in the third sort:
[LastName]


In my birthday lists, I include a group header for the month so my report
looks kinda like this...



January:
01 Smith, John 48 years old
06 Adams, Bob unknown
06 Jones, Nancy 36 years old
15 Branden, Lisa 23 years old

February
14 Love, Bradley unknown


etc.



--
Rick B



I have a contacts database in which I have birthdays listed for some
(not
all) of the contacts and in which years of birth are know for some (not
all)
of the contacts. I have 2 date fields I'm working with: DOB (full date
of
birth), format mmmm dd yyyy, and MonthDayOnly, format mmmm dd. What I
want
to
do is design a query that will allow me to design a report which will
group
on month, then sort by date within month, then sort by last name if
multiple
contacts are have the same birthday. Using the MonthDayOnly field,
everything
sorts correctly. But when I try to manipulate queried data to produce
the
sort of sort I want, everything gets bollixed up. I think it's because
I
know
the year of birth for some - not all - of my contacts & therefore it's
sorting by year, then month, then day... but if that's the case, the
MonthDayOnly sort should be screwy too. Can you help?

(PS - If I sound incoherent & confused, it's because I'm completely
confused!!!)

 

[NC_Sue]

How do I get to that?
--
Thanks for your time!

Need to see the SQL for the query to answer this one.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks to all of you responding to this thread. Between your responses, I've
gotten closer to my goal... BUT...

I'm trying to sort first by month, then by day, then by last name with
section header for month. I've given everyone the same bogus year of birth
for this purpose (1900). Any idea why I've got 2 Aprils? And 2 Julys? And 2
Decembers?
--
Thanks for your time!

First, you should revise your design a bit. If you have a birthdate, you
must fill in two separate fields with basically the same data. That is poor
database design.

Either break it into three fields (BirthDay, BirthMonth, BirthYear) or just
have one field. I personally store the birthdate as a date. If I do not
know the year, I always use 1900. Then I can use if statements to say "if
the birthyear is 1900, don't print the age".

To sort, either do it in your report's grouping and soprting by entering (in
the first group)

=Month(BirthDate)

Then in the second sort:
=Date(Birthdate)

Then in the third sort:
[LastName]


In my birthday lists, I include a group header for the month so my report
looks kinda like this...



January:
01 Smith, John 48 years old
06 Adams, Bob unknown
06 Jones, Nancy 36 years old
15 Branden, Lisa 23 years old

February
14 Love, Bradley unknown


etc.



--
Rick B



I have a contacts database in which I have birthdays listed for some (not
all) of the contacts and in which years of birth are know for some (not
all)
of the contacts. I have 2 date fields I'm working with: DOB (full date of
birth), format mmmm dd yyyy, and MonthDayOnly, format mmmm dd. What I want
to
do is design a query that will allow me to design a report which will
group
on month, then sort by date within month, then sort by last name if
multiple
contacts are have the same birthday. Using the MonthDayOnly field,
everything
sorts correctly. But when I try to manipulate queried data to produce the
sort of sort I want, everything gets bollixed up. I think it's because I
know
the year of birth for some - not all - of my contacts & therefore it's
sorting by year, then month, then day... but if that's the case, the
MonthDayOnly sort should be screwy too. Can you help?

(PS - If I sound incoherent & confused, it's because I'm completely
confused!!!)

 

[NC_Sue]

Ah - I think this is what you wanted to see -

SELECT tblContactInformation.Bogus1900BD, tblContactInformation.LastName,
tblContactInformation.FirstName, tblContactInformation.Email
FROM tblContactInformation
WHERE (((tblContactInformation.BDCardList)=Yes))
ORDER BY tblContactInformation.Bogus1900BD;


--
Thanks for your time!

Need to see the SQL for the query to answer this one.
--
Jerry Whittle
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Thanks to all of you responding to this thread. Between your responses, I've
gotten closer to my goal... BUT...

I'm trying to sort first by month, then by day, then by last name with
section header for month. I've given everyone the same bogus year of birth
for this purpose (1900). Any idea why I've got 2 Aprils? And 2 Julys? And 2
Decembers?
--
Thanks for your time!

First, you should revise your design a bit. If you have a birthdate, you
must fill in two separate fields with basically the same data. That is poor
database design.

Either break it into three fields (BirthDay, BirthMonth, BirthYear) or just
have one field. I personally store the birthdate as a date. If I do not
know the year, I always use 1900. Then I can use if statements to say "if
the birthyear is 1900, don't print the age".

To sort, either do it in your report's grouping and soprting by entering (in
the first group)

=Month(BirthDate)

Then in the second sort:
=Date(Birthdate)

Then in the third sort:
[LastName]


In my birthday lists, I include a group header for the month so my report
looks kinda like this...



January:
01 Smith, John 48 years old
06 Adams, Bob unknown
06 Jones, Nancy 36 years old
15 Branden, Lisa 23 years old

February
14 Love, Bradley unknown


etc.



--
Rick B



I have a contacts database in which I have birthdays listed for some (not
all) of the contacts and in which years of birth are know for some (not
all)
of the contacts. I have 2 date fields I'm working with: DOB (full date of
birth), format mmmm dd yyyy, and MonthDayOnly, format mmmm dd. What I want
to
do is design a query that will allow me to design a report which will
group
on month, then sort by date within month, then sort by last name if
multiple
contacts are have the same birthday. Using the MonthDayOnly field,
everything
sorts correctly. But when I try to manipulate queried data to produce the
sort of sort I want, everything gets bollixed up. I think it's because I
know
the year of birth for some - not all - of my contacts & therefore it's
sorting by year, then month, then day... but if that's the case, the
MonthDayOnly sort should be screwy too. Can you help?

(PS - If I sound incoherent & confused, it's because I'm completely
confused!!!)