Kaye said:
I have a database that is storing warehouse inventory records. For
example I have the following fields in my table:
warehouseReceiptID > autonumber
dateReceived > date
timeReceived > time
warehouseStorageCharge > number
balancePaid > Yes/no
I would like to put some kind of conditional statement that says:
if dateReceived is more than seven days from today's date, I would
like to add a $.10 to the warehouseStorageCharge field and continue
to add the $.10 with each passing day. However once the balancePaid
has been set to Yes, than the storage charge would stop charging.
I would assume that a macro might be involved but need direction on
how to do this.
Any help would be greatly appreciated
I believe you're going to find that cumbersome to implement and
unreliable to maintain. But a small change in your table structure and
your logic could make it quite easy to calculate the total storage
charge as of the current date in a query, whenever you need it.
Suppose you change your BalancePaid field from a Yes/No field to a
Date/Time field. The field is Null if the balamce hasn't been paid yet.
When the balance has been paid, then BalancePaid is set to the date on
which that happened.
With the table set up that way, then you can always calculate the
StorageCharge as
StorageCharge:
IIf((DateDiff("d", DateReceived, Nz(BalancePaid, Date())) - 7) > 0,
DateDiff("d", DateReceived, Nz(BalancePaid, Date())) - 7) * .10,
0)
I may not have that exactly right, as I just dashed it off. But what
it's supposed to mean is:
If BalancePaid is Null, use the current date in the following
calculations;
Else use BalancePaid.
Calculate the number of days between DateReceived and that date,
minus 7 for the "free" first week.
If the resulting number is more than zero, calculate StorageCharge
as that number times $0.10 (10 cents per day);
Else StorageCharge is zero.
Note that this calculated field would not be stored in the table, but
calculated on the fly whenever you need it. Hence you never need to
update the table with revised storage charges, and it can never be out
of date.
The only problem I foresee is if you change the storage charge rate.
You really ought to store that number in a table, anyway, rather than
hard-coding it in the field definition as I have done. But what do you
do when you want to change the rate from $0.10/day to $0.15? This
simple scheme would retroactovely recalculate the storage charges for
everything currently in storage, so if you change it today, something
that yesterday had accumulated a StorageCharge of $1.00 (17 days in
storage = 10 * 0.10) now has a StorageCharge of (11 * .15) = $1.65.
That could be a problem for your business, or not. If it is, you could
use a more elaborate scheme in which you use a table of dated storage
rates, calculate how many days an item in storage has been subject to
each rate, and add them up to get the total charges. This could be done
by a user-written function, or it could be done entirely by a query (but
it would be a rather complex query). Or you might do a process whenever
you change the rate, that redefines each non-paid item in storage in
such a way that the calculation can remain simple.
I agree that setting this up -- if changes to the storage rate concern
you -- could potentially be a little complicated. However, I still
think it would be more reliable than hoping to run an update every day
to add charges to every non-paid item.
