monthly report problem??

[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

 

[John Spencer]

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
..

 

[MrCC via AccessMonster.com]

Great

Thanks alot

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

[quoted text clipped - 19 lines]

how to solve such problem