Month Year Date formatting

[MorningSun]

I would like to have a drop down box on a form that allows a user to select Month,Year (October,2005). With the datepicker, you have to select a day... which is irrelevant in my case. I just need month and year but don't want to type the values for the combobox in for an infinite period of time. Is that possible?

Also, if I have this drop down box set to default to the current date: =format(date(),"mmmm,yyyy"), how do I subtract a month from that? I want the form that they enter data on to automatically populate with the previous month and THEN also have it as a combobox so they can select the correct month & year if the previous month is not the one they want to enter data for...

Is this possible?

Thanks so much!!!!

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[missinglinq]

I would use two drop down boxes (Month and Year) just like online retailers use for you to enter the expiration date of your credit card. Not much work enetring their values; only 12 entries for the months, and even at 20 years you're talking about minutes to enter them all. Then you can concatenate the results into the appropriate field. If picking a month and year from a dropdown box is too much of a burden on your users, they probably shouldn't be doing data entry.

There's ALWAYS more than one way to skin a cat!

 

[Pampers]

a month-day prior from now can be like:
date()-31


Pampers.

You're never too young to learn

 

[MorningSun]

How do I concatenate then into the appropriate field as a date though? I have it set up where they enter month and year but they are text fields... I need the query to recognize "October,2005" as a date...

As for month-day prior, I will try that!

Thanks!!

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.

 

[TheAceMan1]

How are ya MorningSun . . . . .

Bear in mind, as far as Access is concerned a date is the integer part of a value, time is the fractional part. [purple]The integer accounts for month, day, year[/purple] where Date +1 is the next day. The point is, [blue]Month, Year[/blue] [purple]is not recognized by Access as a legal date[/purple]. Gotta have the day or Access will Error.

Formatting ([blue]a display only function[/blue]) simply allows you to display the value in the date field to your liking. This is key . . . [purple]the value in the date field is one thing (required by Access), the display is another![/purple]

Now, since you have to perform date math, your catenating has to include a day (you could always use the 1st of each month for instance) to get an acceptable date for Access. Concating you would have:

[blue]   Me!Month & " 1, " & Me!Year[/blue]

To convert to an actual date value:

[blue]   Me!Textbox = DateValue(Me!Month & " 1, " & Me!Year)[/blue]

The textbox of course, formatted appropriately.

As an idea for a combobox, you could have an independent table of months across a number of years. The RowSource of the combo would be based on a query and the bound field of the combo would be the date field. The query would include a custom field to display the formatted view. Users see the formatted field (Month Year), but the actual date field is being selected/stored!

Your thoughts!!

See Ya! . . . . . .

 

[MorningSun]

Thanks SO MUCH AceMan! I can't wait to try these ideas. I'll let you know on Monday how they work!!!

Dawn Coleman, Business Analyst
UnumProvident Corporation

PLEASE RECYCLE AND ENCOURAGE YOUR OFFICE/COWORKERS TO DO SO AS WELL.