semimonthly billing query

[Jon]

I need to be able to search by date for those members who
joined my organization between the 1st and the 15th, and
separate those who joined between teh 16th and 31st, or
the last day of the month. I need to be able to search
back 3 years, irrespective of the month, since the members
are billed every month. I have a field with mm/dd/yyyy as
the date of first sale field.

Thanks in advance.

Jon

 

[John Spencer (MVP)]

You can use the DAY function to return the day of the month and then apply
criteria against that or you can use a calculated field to tell you that.

Field: WhichHalf: IIF(Day([SaleDate]<16,"First","Second")

Alternative way to get first of month group.

Field: WhatDay: Day([SaleDate])
Criteria: <16

Both of these may be slow since you have to calculate the date and then apply
criteria against the calculation and will not be able to use any indexes.

This MIGHT be a case for de-normalizing your data; but I would not do that
unless your performance was really slow.

And even then, you could create a table of all the dates involved (plus a few
years) and a field that specifies which half the month the date is in or the day
of month number. This could be done fairly quickly with a bit of code. The
added table then could be joined to your member table when needed. It would
have an index where needed and would take care of any situation where you needed
to change the SaleDate.

My first recommendation is to try one of the first two solutions and see if the
performance is acceptable.