Dynamic "Month" Field controlled by Date Entry...

[ewiessner]

Hello.
I've been reading these forums for a few months now while developing a database for logging bugs during website QA, but never posted.

I need a way to change the "Month" combo dynamically based on what a user enters into the "StartDate" and "EndDate" Fields. (I'm using Access '97)

e.g.
I have Validation set up on both start and end date fields so they are required before a record can be saved. I would like the "Month" combo box to update with the proper month after a user has entered the StartDate and EndDate.
(Basically a timesaver for users who are entering 20-25 criteria into a single form.)

There's one hangup:
the date ranges aren't typical, as in, "November 1-30 = November"... It's more like "10/23 through 11/30 = November"
here's what that chunk of the form looks like:

So if I were to enter 9/27 start, and 10/27 end, "October" would populate in the "Month" combo box, and I could continue to other fields.

Any input would be appreciated, and I apologize if this is a repeat post, I couldn't find any similar topics.

Thanks,
-E

p.s. - If it helps, I have a 'Month' table with 1-12 = Jan - Dec, the 'StartDate' and 'EndDate' fields are text entry boxes bound to the master 'Details' table where all the data is stored.

 

[ghudson]

Will the combo box always be the month that the user keys into end date field?

If so then I would not allow the user to select a month, just use a formula to "display" the month name in a new text box [not the old combo box] based on the date keyed into the end date field.

=Format([txtEndDate],"mmmm")

 

[ewiessner]

thanks

The end date doesn't necessarily always equal the Month, for example, a record could be created for the November cycle, but end next August. It's "Month" would still be November, as far as we're concerned.

The code you gave me works at the form level just fine, but I need a bound field that'll store that data in the master table, so it can be queried on later, and can also be overridden, in the case i mentioned before.

Thanks for the quick response

-E

 

[sonny]

Just set the fields value using code on the after update event .