creating a custom date from two "between..and" dates

[DubboPete]

Hi all,

Not sure if this can be done, but I am trying to run a set of queries
in sequence which calculate age groups for customers in a specific
period, then apply a month and year value to an appended entry in a
table, all based on criteria of "between #variable-start-date# and
#variable-end-date#.

The dates would always be a month period, so for instance,
[DateRegistered] between #1/1/08# and #31/1/08#.

The query then retrieves the customer data, grouping it by various
ages, and appends it to the table, but I want the appended data to
reflect Jan-08 as the snapshot date.

I have it at the stage where the various customer age groups are
counted up, but want to append Jan-08 to the table along with the
other stuff!

Is this possible?

thanks in anticipation

DubboPete

 

[John Spencer]

Yes, it is possible.

Field: DateRegistered
Criteria: Between DateSerial([Enter Start Year],[Enter Start Month
Number],1) and DateSerial([Enter Start Year],[Enter Start Month
Number]+1,0)

Add a calculated column to get the first day of the month ( better to store
the whole date if you want to search ranges)
Field: MonthStart: DateSerial([Enter Start Year],[Enter Start Month
Number],1)

If you absolutely MUST store the month name and year (it will be a string)
then use the following expression as your calculated field.
Field: MonthStart: Format(DateSerial([Enter Start Year],[Enter Start Month
Number],1) ,"mmm-yy")
--
John Spencer
Access MVP 2002-2005, 2007-2008
Center for Health Program Development and Management
University of Maryland Baltimore County
..