Between dates and show only days in the month requested

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hello again,
I am back with another problem with my report that my boss is looking for
First question: when putting in my Between [Enter start date] And [Enter end date] why may it not work? (I have 97'

Second..
My report (Table) has:StartDate, EndDate, DaysIncarcerated (among other things which are not important

I know how to get the number of days the individual was incerserated,
now my boss was wondering how to "change"the startdate IF it does not fall into the query

CONFUSING...
Right now: StartDate: 01/31/04 EndDate: 02/04/04 DaysIncarcerated:

The report will be for February(or any month requested) so the StartDate would need to change if necessary if it does not fall into the query month..
So in the final report it would change to StartDate: 02/01/04 EndDate: 02/04/04 DaysIncarcerated:
I do not need the EndDate to change just the StartDate if necessary

Re
 
To ensure the correct interpretation of your parameters, declare them:
- Open the query in design view.
- Choose Parameters from the query menu.
- Enter two lines, with the spelling exactly the same, and type Date/Time.

Your query needs to find the difference in days between:
- the later of StartDate and [Enter start date], and
- the earlier of EndDate and [Enter end date].

Type this into the Field row of the query design grid in a fresh column:
DateDiff("d",
IIf([StartDate] > [Enter start date]), [StartDate], [Enter start date])
IIf([EndDate] < [Enter end Date]), [EndDate], [Enter end date]))

You will also need to restrict the query to only days where there is some
overlap so you don't get negative values in the field above.
WHERE (StartDate <= [Enter end date]) AND (EndDate >= [Enter start date])

--
Allen Browne - Microsoft MVP. Perth, Western Australia.

Reply to group, rather than allenbrowne at mvps dot org.

Regina said:
Hello again,
I am back with another problem with my report that my boss is looking for.
First question: when putting in my Between [Enter start date] And [Enter
Click to expand...
end date] why may it not work? (I have 97')
Second...
My report (Table) has:StartDate, EndDate, DaysIncarcerated (among other
Click to expand...
things which are not important)
I know how to get the number of days the individual was incerserated,
now my boss was wondering how to "change"the startdate IF it does not fall into the query.

CONFUSING....
Right now: StartDate: 01/31/04 EndDate: 02/04/04 DaysIncarcerated: 4

The report will be for February(or any month requested) so the StartDate
Click to expand...
would need to change if necessary if it does not fall into the query
month...
So in the final report it would change to StartDate: 02/01/04 EndDate:
Click to expand...
02/04/04 DaysIncarcerated: 3
 
Regina.
I posted this response a few weeks back,
This is the SQL for a query that should do most of what
you expressed.
SELECT Incarcerated.Inmate, Incarcerated.DateIn,
Incarcerated.DateOut, Year([DateIn]) AS YearIn, Year
([DateOut]) AS YearOut, Month([DateIn]) AS MonthIn, Month
([DateOut]) AS MonthOut, IIf([DateIn]<[Enter Month]
& "/1/" & [Enter Year (4 digit)],[Enter Month] & "/1/" &
[Enter Year (4 digit)],[DateIn]) AS DateStart, IIf
([DateOut]>[Enter Month] & "/" & [Last Day of Month] & "/"
& [Enter Year (4 digit)],[Enter Month] & "/" & [Last Day
of Month] & "/" & [Enter Year (4 digit)],[DateOut]) AS
DateEnd, DateDiff("d",[DateStart],[DateEnd])+1 AS DaysIncar
FROM Incarcerated
WHERE (((Year([DateIn]))<=[Enter Year (4 digit)]) AND
((Year([DateOut]))>=[Enter Year (4 digit)]) AND ((Month
([DateIn]))<=[Enter Month]) AND ((Month([DateOut]))>=
[Enter Month]));
Hope this helps
Fons
-----Original Message-----
Hello again,
I am back with another problem with my report that my boss is looking for.
First question: when putting in my Between [Enter start
Click to expand...
date] And [Enter end date] why may it not work? (I have
97')
Second...
My report (Table) has:StartDate, EndDate,
Click to expand...
DaysIncarcerated (among other things which are not
important)
I know how to get the number of days the individual was incerserated,
now my boss was wondering how to "change"the startdate IF
Click to expand...
it does not fall into the query.
CONFUSING....
Right now: StartDate: 01/31/04 EndDate:
Click to expand...
02/04/04 DaysIncarcerated: 4
The report will be for February(or any month requested)
Click to expand...
so the StartDate would need to change if necessary if it
does not fall into the query month...
So in the final report it would change to StartDate:
Click to expand...
02/01/04 EndDate: 02/04/04 DaysIncarcerated: 3
 
Frons,
Sorry I thought it was a different type of answer. I will try your answer again and see if I get an error code...

Regina
 
Frons:
Here is my SQL

SELECT JuvenileNames.LastName, JuvenileNames.DateIn, JuvenileNames.DateOut, Year([DateIn]) AS YearIn, Year
([DateOut]) AS YearOut, Month([DateIn]) AS MonthIn, Month
([DateOut]) AS MonthOut, IIf([DateIn]<[Enter Month]
& "/1/" & [Enter Year (4 digit)],[Enter Month] & "/1/" &
[Enter Year (4 digit)],[DateIn]) AS DateStart, IIf
([DateOut]>[Enter Month] & "/" & [Last Day of Month] & "/"
& [Enter Year (4 digit)],[Enter Month] & "/" & [Last Day
of Month] & "/" & [Enter Year (4 digit)],[DateOut]) AS
DateEnd, DateDiff("d",[DateStart],[DateEnd])+1 AS DaysIncar
FROM JuvenileNames
WHERE (((Year([DateIn]))<=[Enter Year (4 digit)]) AND
((Year([DateOut]))>=[Enter Year (4 digit)]) AND ((Month
([DateIn]))<=[Enter Month]) AND ((Month([DateOut]))>=
[Enter Month]));

It does ask me for Month (assume spell out month or 01), then ask for 4 digit year, then ask for last date (...31) then ask for Last dat (...Saturday) ....

When I hit run there is no data showing, and when I go to make a report it will not allow me to use any of the fields from the query??

Help
Regina
 
Regina, I noticed in your query you used the lastname of
the juvenile as the sort item, I would be careful with
that since what happens if you two juveniles with the same
last name? etc.
Let me know how things are working out.
Fons
-----Original Message-----
Frons,
Sorry I thought it was a different type of answer. I
Click to expand...
will try your answer again and see if I get an error
code...
 
Regina, I looked at it again, and adapted the same names
as you describe in your sql.
It does ask for the month "2" for february
It does ask for the 4 digit year "2004"
And it asks for the number of days in the month (for the
report) 29 (leap year)
when I ran that agains some sample data I show below, It
comes up with all the right data elements and I can use
this query as the source for a report or form.
Sample dta I have;
LastName DateIn DateOut
A 1/21/2004 3/5/2004
B 2/11/2004 2/18/2004
C 1/30/2004 2/15/2004

query result is;
LastName DateIn DateOut YearIn YearOut MonthIn MonthOut
DateStart DateEnd DaysIncar
A 1/21/2004 3/5/2004 2004 2004 1 3
2/1/2004 2/29/2004 29
B 2/11/2004 2/18/2004 2004 2004 2 2
2/11/2004 2/18/2004 8
C 1/30/2004 2/15/2004 2004 2004 1 2
2/1/2004 2/15/2004 15

I am sure if I spend some more time on it I can work it
out where I do not have to ask for an input for the number
of days in the report month.

Hope this helps.
Fons
-----Original Message-----
Frons:
Here is my SQL

SELECT JuvenileNames.LastName, JuvenileNames.DateIn,
Click to expand...
JuvenileNames.DateOut, Year([DateIn]) AS YearIn, Year
([DateOut]) AS YearOut, Month([DateIn]) AS MonthIn, Month
([DateOut]) AS MonthOut, IIf([DateIn]<[Enter Month]
& "/1/" & [Enter Year (4 digit)],[Enter Month] & "/1/" &
[Enter Year (4 digit)],[DateIn]) AS DateStart, IIf
([DateOut]>[Enter Month] & "/" & [Last Day of Month] & "/"
& [Enter Year (4 digit)],[Enter Month] & "/" & [Last Day
of Month] & "/" & [Enter Year (4 digit)],[DateOut]) AS
DateEnd, DateDiff("d",[DateStart],[DateEnd])+1 AS DaysIncar
FROM JuvenileNames
WHERE (((Year([DateIn]))<=[Enter Year (4 digit)]) AND
((Year([DateOut]))>=[Enter Year (4 digit)]) AND ((Month
([DateIn]))<=[Enter Month]) AND ((Month([DateOut]))>=
[Enter Month]));

It does ask me for Month (assume spell out month or 01),
Click to expand...
then ask for 4 digit year, then ask for last date (...31)
then ask for Last dat (...Saturday) ....
When I hit run there is no data showing, and when I go to
Click to expand...
make a report it will not allow me to use any of the
fields from the query??
 
Thanks for all your help...
I am also playing around with the data. I told my boss that it will be awhile before he can get what he wants but be patient. If the requester has to put in all the information that is ok....I think..

Thanks for all your help and time spent on this situation..

Regina
 
Regina, I'll gladly look at a copy of your database with
some sample data. If you wish contact me at my email f o
n s p o n s i o @ m s n . c o m
-----Original Message-----
Thanks for all your help....
I am also playing around with the data. I told my boss
Click to expand...
that it will be awhile before he can get what he wants but
be patient. If the requester has to put in all the
information that is ok....I think...
 
Fons.

When I go to work on Tuesday I will email you my data and let you play with the information..
Right now, when putting in the data, I just change then DateIn to verify the information requested in the form, and return to the form after the report is finished to change the DateIn back....My boss is happy right now but just making the report work without changing the dates by self would be fantastic..

Thanks for all your hel
Regina
 
Back
Top