How to structure query to obtain the needed data

[cambar]

Database consists of records of visitors who have stayed at various contracted locations. Have ADATE and DDATE. Need to isolate all those who have stayed any days in October. So, in some cases they may have begun their stay in September and left during October or they may have begun their stay in October and not yet left. I further, need to be able to count the number of days each visitor has stayed during October.

I cannot seem to get what I need. I have specified ADATE Between 10/01/2010 And 10/31/2010 or DDATE Between 10/01/2010 and 10/31/2010 and have done DateDiff to get days. However, DateDiff is including all days in the count, not just those in October and is not counting October days at all for those visitors who have not departed yet. Can you please help me with how to structure this query?

Thanks so much
cambar

[NeoPa]

You probably want to look at something with a structure similar to :

SELECT *
     , DateDiff('d',
                IIf([ADate] < #10/1/2010#, #10/1/2010#, [ADate]),
                IIf([DDate] > #10/30/2010#, #10/30/2010#, [DDate])) AS OctDays
FROM   [Table]
WHERE  ([ADate]<=#10/30/2010#)
  AND  ([DDate]>=#10/1/2010#)

[cambar]

NeoPa response does not count October days if there is no DDATE. If the visitor arrived in late October and has not yet departed, it will not provide the number of October days. And if the visitor arrived and left the same day, the day count is 0 when it needs to be 1.

[NeoPa]

OK. Adding one is pretty simple I would guess, but :

Cambar:
it will not provide the number of October days

doesn't tell me what it does do. Does it give an error? Zero maybe?

Anyway, try this for now :

SELECT *
     , DateDiff('d',
                IIf([ADate] < #10/1/2010#, #10/1/2010#, [ADate]),
                IIf(Nz([DDate], Date()) > #10/30/2010#, #10/30/2010#, Nz([DDate], Date()))) + 1 AS OctDays
FROM   [Table]
WHERE  ([ADate] <= #10/30/2010#)
  AND  (Nz([DDate], Date()) >= #10/1/2010#)

[cambar]

Thank you very much for your help. This works.
camgar

[NeoPa]

I'm pleased that was helpful Cambar :-)