Queries: Dates-return only days served within the month reporting

[Windsorcat via AccessMonster.com]

I am new here, and a novice level Access user. I am building a query. I
have client records I am trying to calculate the number of days stayed in
our facility in a month. I have a Client table, Client admissions, and
client services tables.

The fields I am using are Actual_Start_Date and Actual_Close_Date from the
Client Services table, and I enter criteria to show me a month at a time (ex:
Actual_Start_Date <2/1/06, Actual_Close_Date >12/31/05 to give me January
data).

From this I need the number of days stayed within a month. Currently I get
the entire number of days stayed ex: Actual_Start_Date is 1/20/06 and
Actual_Close_Date is 2/5/06. I get a total stay of 16 days. What I want is
11 days in January and 5 days in February. Any ideas on how to put this in
an expression?

I hope this is enough information.

Thanks,
Windsorcat

 

[KARL DEWEY]

Try this --
SELECT [Client Services].ClientID, [Client Services].Actual_Start_Date,
[Client Services].Actual_Close_Date, DateDiff("d",IIf(CVDate([Enter month and
year MMM YYYY])<[Actual_Start_Date],[Actual_Start_Date],CVDate([Enter month
and year MMM YYYY])),IIf(DateAdd("m",1,CVDate([Enter month and year MMM
YYYY]))-1>[Actual_Close_Date],[Actual_Close_Date],DateAdd("m",1,CVDate([Enter
month and year MMM YYYY]))-1))+1 AS [Number of days stayed]
FROM [Client Services]
WHERE (((DateDiff("d",IIf(CVDate([Enter month and year MMM
YYYY])<[Actual_Start_Date],[Actual_Start_Date],CVDate([Enter month and year
MMM YYYY])),IIf(DateAdd("m",1,CVDate([Enter month and year MMM
YYYY]))-1>[Actual_Close_Date],[Actual_Close_Date],DateAdd("m",1,CVDate([Enter
month and year MMM YYYY]))-1))+1)>0));

 

[Windsorcat via AccessMonster.com]

Thank you, but I need a little more help please. I entered the "DateDiff" as
below, and it works. However, why do I get 4 prompts for the month and year
and what can I do, to not have to enter that every time? Also, since the
DateDiff provides me the results I need, what does the "Where" statement do?


I truly appreciate your help and how much time you have saved me!

Try this --
SELECT [Client Services].ClientID, [Client Services].Actual_Start_Date,
[Client Services].Actual_Close_Date, DateDiff("d",IIf(CVDate([Enter month and
year MMM YYYY])<[Actual_Start_Date],[Actual_Start_Date],CVDate([Enter month
and year MMM YYYY])),IIf(DateAdd("m",1,CVDate([Enter month and year MMM
YYYY]))-1>[Actual_Close_Date],[Actual_Close_Date],DateAdd("m",1,CVDate([Enter
month and year MMM YYYY]))-1))+1 AS [Number of days stayed]
FROM [Client Services]
WHERE (((DateDiff("d",IIf(CVDate([Enter month and year MMM
YYYY])<[Actual_Start_Date],[Actual_Start_Date],CVDate([Enter month and year
MMM YYYY])),IIf(DateAdd("m",1,CVDate([Enter month and year MMM
YYYY]))-1>[Actual_Close_Date],[Actual_Close_Date],DateAdd("m",1,CVDate([Enter
month and year MMM YYYY]))-1))+1)>0));

I am new here, and a novice level Access user. I am building a query. I
have client records I am trying to calculate the number of days stayed in

[quoted text clipped - 16 lines]

Thanks,
Windsorcat

 

[KARL DEWEY]

why do I get 4 prompts for the month and year
Check on your typing. For multiple use of a prompt I always type it once
and then copy and paste it in all of the other places so that I do not have
any leading or trailing spaces or any other such differences in typing.
Without the > 0 it would also pull records out side the prompt
window and indicate a negative value for them.

Thank you, but I need a little more help please. I entered the "DateDiff" as
below, and it works. However, why do I get 4 prompts for the month and year
and what can I do, to not have to enter that every time? Also, since the
DateDiff provides me the results I need, what does the "Where" statement do?


I truly appreciate your help and how much time you have saved me!

Try this --
SELECT [Client Services].ClientID, [Client Services].Actual_Start_Date,
[Client Services].Actual_Close_Date, DateDiff("d",IIf(CVDate([Enter month and
year MMM YYYY])<[Actual_Start_Date],[Actual_Start_Date],CVDate([Enter month
and year MMM YYYY])),IIf(DateAdd("m",1,CVDate([Enter month and year MMM
YYYY]))-1>[Actual_Close_Date],[Actual_Close_Date],DateAdd("m",1,CVDate([Enter
month and year MMM YYYY]))-1))+1 AS [Number of days stayed]
FROM [Client Services]
WHERE (((DateDiff("d",IIf(CVDate([Enter month and year MMM
YYYY])<[Actual_Start_Date],[Actual_Start_Date],CVDate([Enter month and year
MMM YYYY])),IIf(DateAdd("m",1,CVDate([Enter month and year MMM
YYYY]))-1>[Actual_Close_Date],[Actual_Close_Date],DateAdd("m",1,CVDate([Enter
month and year MMM YYYY]))-1))+1)>0));

I am new here, and a novice level Access user. I am building a query. I
have client records I am trying to calculate the number of days stayed in

[quoted text clipped - 16 lines]

Thanks,
Windsorcat