Newbie has a Query Question

[Bruce]

Im using Access 2003. I have created a database that tracks customer orders. I provide a service that bills a fixed rate per day of service. In my database I have an order implementation date (ORDIMPLDATE), and order expiration date (ORDEXPDATE). Each month I need to prepare an invoice for all orders that were active that month and formulate a monthly cost based on how many days the order was active during that month. Some orders may have been implemented or expired in the middle of a month.

Any ideas? Also, does Access know how many days are in each month?



Thanks!
Bruce

[NTC]

You need a new field in your table; label it as DayCount and make it an integer type field

In your form add this field

go into VB Help area or google about and learn the DateDiff method syntax.... make a DateDiff statement and put it in the AfterUpdate event of the OrderExpireDate field...

this will count for you the days difference and put the value into your new field. Yes, Access knows the days in a month, even if it is leap year...so it doesn't matter if they straddle two different months....

[Bruce]

Thanks for the info. I did find the DateDiff command and have been working with it. The hard part is first identifying the orders active within that month (ex. November where IMPLDATE <$12/01/2009$ and EXPDATE >$12/01/2009$) then an IIf statement that determines if the IMPLDATE was within that month (use IMPLDATE) or previous to that month, (use 11/1/2009), then the EXPDATE >12/1/2009 use 12/01/2009, then DateDiff to determine the number of days active within the month of 11, *x per day.

This formula works but I hope to find a way to enter criteria to run this each month without having to edit the hard coded date IIF statements.
I tried to use [Enter Start Billing Date] [Enter Stop Billing Date] but it doesn't like it. I'll keep tinkering.