Difference between two dates

[Carlee]

Hi there,

I have to create monthly billing invoices for impression counts of print
devices. To do this I have created a nested query for my reports record
source. What I have done is create parameter prompts for the user to enter
the billing month start date and billing month end date. In addition, the
user must enter the previous months start and end dates.

The reason I did this is so that I can get the impression counts for the two
months, subtract the difference, and bill on usage. Queries are set up like
this:
Query1: ImpressionCount1, CountMonth (prompt for billing month), Serial
Number
Query2: ImpressionCount2, CountMonth (prompt for previous to billing
month), Serial Number
Query3: ImpressionCount2-ImpressionCount1 as Print Count, SerialNumber


Problem: This process is too cumbersome for the user. (rightly so!)

Issue: How can do the following:
1) have the user enter the billing month range (say Aug 1 to Aug 31).
2) have the database take the value entered, and use it to pull the previous
month to do the calculations
3) have the database present results of calculation for the billing month
subtracted from the previous month.

Regards, and thank you in advance,
Carlee

 

[Chaim]

DateSerial is very useful in this context.

DateSerial(2005, 8, 0) returns the last day of the previous month
DateSerial(2005, 8, 1) is the first of the current month
DateSerial(2005, 8 + 1, 1) is the first of the following month

Using Year(), Month() and Day() functions you can use DateSerial on a date
expression. For example,

DateSerial(Year(Date()), Month(Date()) - 1, 0) is the first day of the
previous month.

So, rather than have the user enter two dates, (and assuming you want the
current year), you could ask the user to enter the month number (8 for
August) and build the expression as needed for feeding to DateSerial.

Hope this helps.

 

[John Spencer (MVP)]

Nit Pick

Actually
DateSerial(Year(Date()), Month(Date()) - 1, 0)
is the last day of the two months ago. Today is Sept 1 and this returns July 31

DateSerial(Year(Date()), Month(Date()) - 1, 1)
is the first day of the previous month