Using Value Lists for Month and Year

[Geoff Shaw]

This seems a simple idea, but I can't figure it.

I want to create two fields called 'Month' and 'Year' for data entry via Value Lists (the first listing Jan thro' Dec, the second listing 2005, 2006 and 2007). This is how it is done on those holiday booking web sites.

How do I configure the fields and how do I extract the information from them to form a valid date (assuming the first of each month)?

[-Queue-]

Create two value lists, one containing the months and one containing the years. Format each field to display the appropriate list, then create a calculation field with a date result of

Case( not (IsEmpty(Month) or IsEmpty(Year)); Date( Month; 1; Year ) )

[Geoff Shaw]

This sounds promising.

Do I format the 'Month' and 'Year' fields to be Text or Date fields?

Thanks for responding so fast

Geoff

[-Queue-]

Month should be text and Year can be number or text. Neither should be dates, because they are only a portion of the date.

[Geoff Shaw]

Just tried it and no matter what month I type into the 'Month' field I get a date returned 1/12/2004 in the calculation field when entering '2005' in the 'Year' field and 1/12/2005 if I enter 2006 as the year.

[comment]

The formula -Queue- gave you expects Month to be a number. What are you typing into the 'Month' field?

[Geoff Shaw]

I've tried typing either Aug or August. It is set up as a text field and I would prefer my users to enter it this way rather than a month number, which can cause confusion when dealing with different years (i.e. Apr - Mar or Jan - Dec).

[comment]

If you're going to make a value list - and I presume you will, considering the forum section - you will have to settle on one or another. Let us know which one will it be, and we'll adjust the calc to fit.

[-Queue-]

Doh!

Case( not (IsEmpty(Month) or IsEmpty(Year)); Date( (Position( "JanFebMarAprMayJunJulAugSepOctNovDec"; Month; 0; 1 ) + 2) / 3; 1; Year ) )

or

Case( not (IsEmpty(Month) or IsEmpty(Year)); Date( (Position( ValueListItems( Get(FileName); "Months" ); Month; 0; 1 ) + 3) / 4; 1; Year ) )

I omitted the conversion, sorry!

[Geoff Shaw]

Just brilliant Queue. It works!

Thank you so much.

Geoff

[Søren Dyhr]

Case( not (IsEmpty(Month) or IsEmpty(Year)); Date( (Position( ValueListItems( Get(FileName); "Months" ); Month; 0; 1 ) + 3) / 4; 1; Year ) )

Caution Caution, will only work with abreviations with the lenght of 3 stuffed in the valuelist ... should be reworked in case full words of variable length are required in the popup.

The things are getting tricky when several languages are used based on system settings - Two valuelist are then required one with the names from the systemsetting for the popup and one similar with trailing @@@@'s to make the positioning the start of each word multiplications of the longest month names length. To get the system settings word has the list to be build via the MonthName function.

The reason for going thru all these measures is that this pseudo Case'ing is slightly faster than the genuine.

--sd

[comment]

( Position( "JanFebMarAprMayJunJulAugSepOctNovDec"; Left ( Month ; 3 ) ; 1; 1 ) + 2 ) / 3

Not solving the language problem, but allows for the two alternatives mentioned here.