How can I update a second field from a Combo Box?

[Guest]

I use two combo boxes to select birth month (January-December) and day (1-31)
from separate tables in a Data Entry Form. These are text fields which print
fine on most reports but they don't sort correctly to pint a list by
birthdays. I added a field in each table and entered the corresponding
2-digit number for each month or day (text) and added a field in the main
table for this new date (ex: 0509 for May 9.)

This worked well once I ran some update queries to replace all the data in
the new 'date' field but I need a way to keep it current whenever a new
birthday is entered. Is there a way to pick up this additional field in each
combo box? I know only the 'bound' column will be accepted as the value for
that field. But can the second field be stored as a variable and then
combined with the other one and saved to the new birthday field when the user
moves off that record. Can this be done 'On Exit" ? Or is there some other
way to accomplish this?

 

[Marshall Barton]

I use two combo boxes to select birth month (January-December) and day (1-31)
from separate tables in a Data Entry Form. These are text fields which print
fine on most reports but they don't sort correctly to pint a list by
birthdays. I added a field in each table and entered the corresponding
2-digit number for each month or day (text) and added a field in the main
table for this new date (ex: 0509 for May 9.)

This worked well once I ran some update queries to replace all the data in
the new 'date' field but I need a way to keep it current whenever a new
birthday is entered. Is there a way to pick up this additional field in each
combo box? I know only the 'bound' column will be accepted as the value for
that field. But can the second field be stored as a variable and then
combined with the other one and saved to the new birthday field when the user
moves off that record. Can this be done 'On Exit" ? Or is there some other
way to accomplish this?

Forget about that extra field, it's inappropriate and is
already causing problems.

The month and day fields you already had are sufficient to
do the calculation for sorting purposes whenever it's
needed. For example, if both the month field and the day
field are always two characters (i.e. have a leading zero if
needed), then the sorting and grouping expression could just
be:
=monthfield & dayfield

Alternatively, you could set the report's Sorting and
Grouping to:
=DateSerial(0, monthfield, dayfield)
This has the added advantage that a text box with its Format
property set to mmmm dd can be used to format that
expression as July 23 if you should ever want to present it
that way (note what it does for folks born on Feb 29).

 

[Guest]

That raises a new issue. My month fields are the actual words: 'January',
etc, not text numbers, because I want the user to pick from a list to avoid
typos. Same with the day field, user picks "1-31" from a drop-down list.
Most of the time these actual fields are what we want to see on screeen and
print in reports. This is the first itime we have needed to sort on these
fields.

Are you saying I should change these fields to a combined date field, yyyy
mm? How would I have the user select the correct month and day on the data
entry form that would result in one date field?

gardengirl

 

[Guest]

One other point that I need to mention is that we use only the month and day,
never the year, for our bithday information. (Garden club ladies are young
at heart - forever!)

 

[Marshall Barton]

That only changes the expression, not the concept. Try
using this expression in Sorting and Grouping:

=CDate(monthfield & " " & dayfield & " " & Year(Date()))

And, No, you do not need a year value (the current year will
do).

What does "most of the time" mean???

 

[Marshall Barton]

Well, of course there is no year. I would never suggest
otherwise ;-)

 

[Guest]

"Most of the time" means that until this recent request for a birthday list,
we had no need to sort on birthday. I had switched the birthday field
several years ago from a simple '02/15' entry to the month name and day
(February 15) as that was what we needed to print in the annual Handbook. I
see from your example that we could have done that using CDate or other
functions if we used a full date.

I hate to give up having the user select the month name from a list as that
seems to reduce errors compared to entering a number. Any suggestions?

That only changes the expression, not the concept. Try
using this expression in Sorting and Grouping:

=CDate(monthfield & " " & dayfield & " " & Year(Date()))

And, No, you do not need a year value (the current year will
do).

What does "most of the time" mean???
--
Marsh
MVP [MS Access]

That raises a new issue. My month fields are the actual words: 'January',
etc, not text numbers, because I want the user to pick from a list to avoid
typos. Same with the day field, user picks "1-31" from a drop-down list.
Most of the time these actual fields are what we want to see on screeen and
print in reports. This is the first itime we have needed to sort on these
fields.

Are you saying I should change these fields to a combined date field, yyyy
mm? How would I have the user select the correct month and day on the data
entry form that would result in one date field?

gardengirl

 

[Marshall Barton]

No need to give up any of that, just try using the
expression I posted. It should be all you need to do to
sort the report's records.

 

[Guest]

It worked! I still can't believe it worked without changing any fields. I
had to look up what the CDate function did before I 'got it' that I didn't
need to change any fields. Thanks for a simple answer to what seemed like a
big challenge.

 

[Marshall Barton]

"I love it when a plan comes together" - The A-Team
;-)

 

[Guest]

Everything works on sorting the combined month and birthday -- except when I
try to add grouping to force a page break after each month. If I add a
group header/footer, it requires an interval, and if I choose month, the
month is in order but the days are not. I like using the Group Header
because I can pick up the new month and print it at the top of the page
(hiding duplicates) but I need to have the days in the right order. I tried
to use On Event and Expression Builder to add code as suggested in "Help" for
forcing a page break, but the sample code didn't fit my situation and I don't
know what to use to adapt it to my situation.

"I love it when a plan comes together" - The A-Team
;-)
--
Marsh
MVP [MS Access]

It worked! I still can't believe it worked without changing any fields. I
had to look up what the CDate function did before I 'got it' that I didn't
need to change any fields. Thanks for a simple answer to what seemed like a
big challenge.

 

[Marshall Barton]

Sorry to take so long to get back to you, but I've been out
of town for two weeks.

Just add the same expression to the next row in the Sorting
and Grouping list, but without any interval.

Actually, to group on the month, it would have been a little
easier to insert this expression:
=CDate(monthfield & " 1 " & Year(Date()))
above the one you already had and forget about the interval
in both of them.

 

[Guest]

Thanks so much. I'll try that. I appreciate your getting back to me on this
-- but you are entitled to get away once in awhile!! I appreciate your
simple, uncomplicated answers. They really help.

Sorry to take so long to get back to you, but I've been out
of town for two weeks.

Just add the same expression to the next row in the Sorting
and Grouping list, but without any interval.

Actually, to group on the month, it would have been a little
easier to insert this expression:
=CDate(monthfield & " 1 " & Year(Date()))
above the one you already had and forget about the interval
in both of them.
--
Marsh
MVP [MS Access]

Everything works on sorting the combined month and birthday -- except when I
try to add grouping to force a page break after each month. If I add a
group header/footer, it requires an interval, and if I choose month, the
month is in order but the days are not. I like using the Group Header
because I can pick up the new month and print it at the top of the page
(hiding duplicates) but I need to have the days in the right order. I tried
to use On Event and Expression Builder to add code as suggested in "Help" for
forcing a page break, but the sample code didn't fit my situation and I don't
know what to use to adapt it to my situation.