query criteria based on before and after date

[maverick8888]

Lounge,

Hello all again. While searching for a solution for the latest question i have i am having a issue with trying to phrase the right question, along with the search function on Eileenslounge as well. I have created, with Hans help, that has many calculations in quieres based on numbers. Unfortunately the calculations will change based on price changes. My question is; all my calculations are based on before january 22nd, new postal rates start on january 22nd. Is there some way to filter criteria of what numbers are calulated based on date or do i have to double all my calcualtion quieres and have one set for before and after the 22nd? I fear the doubling of the quieres will slow the processing even more. I guess im looking for an if/then/else statement as the example follows;

if date >01/22/12 then
number of pieces times X
else
if date <01/22/12 then
number of pieces times Y

and even moreso, which manner would reduce processing time so i can go with the other.

EDIT: taking into account that i would need to pull both situations from the same query

Michael

[HansV]

In the following, I'll show deliberately simplified examples.

I'd create a table to store the rates with their history:

tblRates.png

Strictly speaking, the EndDate field is superfluous, but it makes the calculations a lot easier. For the "current" rates, just enter a date some time in the future as end date. You can adjust that when the time comes.

You probably don't want to look at today's date, but at the contract date, or send date, or similar. So you need a date/time field in the mailing table too:

tblMailings.png

You can then combine them in a query:

qryCost design.png

Note that the tables are joined directly on the Item field, and that the "join" on the date is done in the form of a condition in the Criteria row.

This is the result of the query:

qryCost.png

You can sort, group and sum the data any way you want.