monthly report problem??

  • Thread starter Thread starter MrCC
  • Start date Start date
M

MrCC

Dear All
now i have a database for car parking ,i need to make a monthly report for
every customer

the problem is as following

assume

CustName InDate OutDate
test 10 March 2007 10 May 2007

i need to know the monthly report for customer test for april 2007 (how many
days he park in the parking)

now if i query that Indate or Outdate in between 1-april-2007 And 30-April-
2007

the result will be nothing ,although he parked 30 days in april (the whole
month)

how to solve such problem
 
IF you are doing this report for exactly one month at a time

SELECT CustName, InDate, OutDate
FROM YourTable
WHERE InDate <= #2007-04-30# and OutDate >= #2007-04-01#

If you want the dates to reflect the dates of the month you are testing then
you might want to set up something like

Parameters PeriodStart DateTime, PeriodEnd DateTime;
SELECT CustName
, IIF(InDate<PeriodStart,PeriodStart,InDate) as ParkStart
, IIF(OutDate>PeriodEnd,PeriodEnd,OutDate) as ParkEnd
FROM YourTable
WHERE InDate <= PeriodEnd and OutDate>=PeriodStart

For your sample for April that should return
CustName InDate OutDate
test 1 April 2007 30 April 2007

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
Great :)

Thanks alot



John said:
IF you are doing this report for exactly one month at a time

SELECT CustName, InDate, OutDate
FROM YourTable
WHERE InDate <= #2007-04-30# and OutDate >= #2007-04-01#

If you want the dates to reflect the dates of the month you are testing then
you might want to set up something like

Parameters PeriodStart DateTime, PeriodEnd DateTime;
SELECT CustName
, IIF(InDate<PeriodStart,PeriodStart,InDate) as ParkStart
, IIF(OutDate>PeriodEnd,PeriodEnd,OutDate) as ParkEnd
FROM YourTable
WHERE InDate <= PeriodEnd and OutDate>=PeriodStart

For your sample for April that should return
CustName InDate OutDate
test 1 April 2007 30 April 2007
Dear All
now i have a database for car parking ,i need to make a monthly report for
Click to expand...
[quoted text clipped - 19 lines]
how to solve such problem
Click to expand...
Click to expand...
 
Back
Top