Date format

[HEG]

I set up a date field on a table and a form to show the 3-
character month and 4-character year, using the following
format:
Format: mmm yyyy
Input Mask: >L>LL\0000;0;_

Since most entries to this field are the same and indicate
the previous month I set the default for this field on the
form as:
=DateAdd ("m", -1, Now())

The correct date seems to appear on the form (as Mar
2004), but the table is actually storing a General Date
(mm/dd/yy h:mm:ss AM (or PM)).

The query that drives the report that needs this data has
a parameter asking for the month and year of the report,
and expects to find (mmm yyyy). When the query or the
report is generated the results are empty. If I remove the
parameter they return all records.

Is there a better way to structure the default value or is
there some other better solution?

 

[Brian Camire]

Date/Time fields are for storing a single point in time, not a "span" of
time (like a month).

To avoid the kinds of complications you are having, you might use two fields
instead of one -- a month field (you will probably want this to be a number
from 1 to 12, so that you can sort on it), and a year field.

Or, if you want to keep using one Date/Time field, you might:

1. Set the existing values in your date field to be the first of the month.

If this is really what you want, you want to make these changes permanently,
and you are satisfied this will give you what you want (in other words, you
might consider making a backup beforehand), you might use an update query
whose SQL looks something like this:

UPDATE
[Your Table]
SET
[Your Table].[Your Date Field] = DateSerial(Year([Your Table].[Your Date
Field]),Month([Your Table].[Your Date Field]),1);

2. Prevent values of your date field in your table from being anything
other than (midnight on) the first of the month.

You might do this by setting the Validation Rule property of your date field
to an expression like this:

DateSerial(Year([Your Date Field]),Month([Your Date Field]),1)

This assumes your date field is required.

You might also want to set the Validation Text property of your date field
to something meaningful like "<Your Date Field> must be the first of the
month".

3. Set the Default Value property of the control bound to your date field
on your form to be the first of the previous month by using an expression
like:

DateSerial(Year(Date()),Month(Date())-1,1)

Hope this helps.

 

[Allen Browne]

Use Date() instead of Now(). That gets rid of the time component.

Try:
=DateSerial(Year(Date()), Month(Date()-1), 1)
Access makes sense of that even in January.