Month only field problem

[GI Smith]

Designed a database that lists all reports due to outside agencies for the
boss to use to track the report name, department and person responsible,
agency receiving report, frequency of report (annual, quarterly, monthly
etc), starting period (month name), ending period (month name), month due and
day due. Don't care about year, these requirements are driven by various
environmental permits and the timeframes for reporting to the agencies are
the same each year (this is why I did not use a date field - because year is
always included).

The three fields - month due, starting period and ending period - are
provided by a MonthName table via a combo list on the data input form.

The problems -
#1 the list in alphabetically sorted rather than chronologic so I don't
think the combo list was the best way to accomplish this.
#2 Would like to be able to type in the month number ie 6 instead of June
but want June to appear on the form after the month number is typed in.
#3 The department managers will be using the database and would like them to
have flexible query that would let them use either "6" or "June". Is this
possible?

Thanks in advance.

 

[Duane Hookom]

If you only want to store the month, then use a numeric field with numbers
1-12. There are a boat load of date functions that allow you to display this
in any format you like.

 

[GI Smith]

Duane - Don't know if I can't see the forest for the trees or if I've been
looking at this too long.... I added a test field named StartMonth, set it up
as numeric data type in the table design window, under the General tab the
field size is long interger and Format is mmmm. When I attempt to input a
numeric number in field on the form I get an error. Should I just set up a
numerical field and format mmmm in the properties on the form?

Appreciate any help. Can't believe I'm so snagged on this...btw using
Access 2000 (it's government issue - no books)

 

[John Vinson]

Duane - Don't know if I can't see the forest for the trees or if I've been
looking at this too long.... I added a test field named StartMonth, set it up
as numeric data type in the table design window, under the General tab the
field size is long interger and Format is mmmm. When I attempt to input a
numeric number in field on the form I get an error. Should I just set up a
numerical field and format mmmm in the properties on the form?

A format of mmmm is appropriate for a Date/Time field - but NOT for an
integer field. A Date is stored as a number, a count of days and
fractions of a day (times) since midnight, December 30, 1899... if the
mmmm format were to work at all, 1 would be shown as December (since
1.0 is equivalent to #12/31/1899#) and 2 through 12 as January! Not
what you want!

Instead, you can either use

Format(DateSerial(2004, StartMonth, 1), "mmmm")

or create a little twelve-row table with a field for the month number
and for the month name.

John W. Vinson[MVP]

 

[Duane Hookom]

Don't set the format of field to mmmm. This format is reserved for the
VIEWING of date fields. If you want to display the month number as the Month
Name, use a form with a combo box as the display for the month field. You
can set the properties:
Row Source Type: Value List
Row Source: 1,"January",2,"February",3,"March",...12,"December"
Number of Columns: 2
Bound Column: 1
Column Widths: 0",1"

 

[GI Smith]

John,

Thanks for the suggestion. I think I will add a number cross-reference to
the existing table MonthName which is what I'm using to populate the Period
Start, Period End and Month Due fields. The reports are always due the same
month, same day so no year information is required, which is why I did not
use a date field to begin with. Originally I planned to set up the periods
(annual, quarterly, semi-annual etc) with a combo list, problem is some
reports are fiscal year, some calendar. I didn't want the end user to have
to deal with several different criteria so figured plugging in months would
be better in the long run.

Problem with the month field is - as a combo list it auto sorts the month
names alphabetically so assigning a number value should keep it in the proper
calendar order.

Thanks to everyone helping out. This support network is great for working
through the snags.

Suzanne

 

[GI Smith]

Okay - I've tried it both ways, a table/query and the value list. Both
options work. But I cannot select the month by the number OR the month name.
I can enter month name only. Is there a way to allow the end user either
option? Is it worth the time?

Thanks for the insight - I promise I'll stop harping on this problem, I'm
sure there are more to come later.

 

[Duane Hookom]

With only 12 possibilities, I would make them enter the month number. You
could set the Row Source property to:
Row Source: 1,"1-January",2,"2-February",3,"3-March",...12,"12-December"

There are probably solutions that allow entry of either depending on the
KeyPress values. I would think there are more important priorities.