Filter By Date

[Chad Cameron]

Hi All,

I have a query called DataSum. In this query I am summing all the hours
that a peice of equipment is used. In my report, it displays a summary of
each piece of equipment and the total hours used in the DB.

I created a form so that the user can specify the date range to use. I have
2 variables txtFromDate & txtToDate.

Now I want to filter my report by these 2 dates. It doesn't work, but I am
just guessing here. Could someone point me in the right direction.

Thanks
Chad

DoCmd.OpenReport Form_MainForm.ReportName, acPreview, , "Date Between " &
txtFromDate & " and " & txtToDate

I think my problem is that I don't have a date field in my query. When I
do, my query no longer sums properly. I think it sums for each day, which I
don't want. I guess I need to know how to filter the data without have the
date field in there.

 

[Steve C, MFS]

Hi, Chad.

Without seeing your query it is difficult to give a complete answer, but
yes, you will certainly need a date field to filter on! Are you doing the
summing on the report, or in the query and just displaying the result? You
will have to make sure your filter is applied before you sum - ie if you
produce the sum result in the query then just display it on the report,
adding the date filter to the report will make no difference.

One other aside: when using dates in parameters, surround them with the #
symbol - this tells Access it is handling a date:

DoCmd.OpenReport Form_MainForm.ReportName, acPreview, , "Date Between #" &
txtFromDate & "# and #" & txtToDate & "#"

Hope that's food for thought.

Steve C

 

[Chad Cameron]

Thanks Steve,

I am summing in the query and then just reporting it. This is where I am
stuck. The way my query is now, I have TruckA = 168 hours (7 days x 24
hours)
TruckB = 168 hours

If I add the date field to my query I get:
TruckA = 24
TruckA = 24 (total of 7 times)
TruckB = 24
TruckB = 24 etc.

I am not sure how to filter and sum at the same time.

I am new to the database world.

 

[Steve C, MFS]

OK, so you either need to filter in the query before you total, so you only
sum the relevant results, or pass everything to the report, filter the report
(so you must include the date field), then create a calculated control on the
report.

You can get parameters into a query very quickly. Include a date field in
the selection then in the criteria box type something like:

=[startDate] And <=[endDate]

This will display a pop-up box to the user. The prompt will be whatever you
put in the brackets, so [Please enter Start Date] or [What is the end date?]
would give the user a meaningful prompt without the need to get into input
forms or code to parse dates, etc.

If you would rather throw everything onto the report and use that for
summing, that is still easy to do, but a little longer to explain, which I'm
afraid I don't have time for right now. There is enough in the online help
to show you how to create summed fields on a report though.

Hope that helps,

Steve C

 

[Chad Cameron]

Either I am not explaining myself, or I just don't get it.

Before I added the date field, the query summed up all the hours for each
peice of equipment. So, I would have 1 entry for each peice of equipment.
Which makes sense and that is what I think I need..

I added the date field and put in the date filters. If I look at the
query's data, it shows my entries in that specific date range. So, say my
range is over 7 days, I have 7 entries for each peice of equipment. Which
also makes sense. I am using the sum command. It sums up for each day,
which also makes sense but, I want to sum up for the entire date range for
each piece of equipment. This is where I am stuck. I am missing a step to
sum up all the entries for each piece of equipment for a specified time
frame.

Chad

OK, so you either need to filter in the query before you total, so you
only
sum the relevant results, or pass everything to the report, filter the
report
(so you must include the date field), then create a calculated control on
the
report.

You can get parameters into a query very quickly. Include a date field in
the selection then in the criteria box type something like:

=[startDate] And <=[endDate]

This will display a pop-up box to the user. The prompt will be whatever
you
put in the brackets, so [Please enter Start Date] or [What is the end
date?]
would give the user a meaningful prompt without the need to get into input
forms or code to parse dates, etc.

If you would rather throw everything onto the report and use that for
summing, that is still easy to do, but a little longer to explain, which
I'm
afraid I don't have time for right now. There is enough in the online
help
to show you how to create summed fields on a report though.

Hope that helps,

Steve C

Thanks Steve,

I am summing in the query and then just reporting it. This is where I am
stuck. The way my query is now, I have TruckA = 168 hours (7 days x 24
hours)
TruckB = 168 hours

If I add the date field to my query I get:
TruckA = 24
TruckA = 24 (total of 7 times)
TruckB = 24
TruckB = 24 etc.

I am not sure how to filter and sum at the same time.

I am new to the database world.

 

[Chad Cameron]

nevermind, I figured it out.

I was using the Group By command instead of the Where command in my query

Thanks
Chad

OK, so you either need to filter in the query before you total, so you
only
sum the relevant results, or pass everything to the report, filter the
report
(so you must include the date field), then create a calculated control on
the
report.

You can get parameters into a query very quickly. Include a date field in
the selection then in the criteria box type something like:

=[startDate] And <=[endDate]

This will display a pop-up box to the user. The prompt will be whatever
you
put in the brackets, so [Please enter Start Date] or [What is the end
date?]
would give the user a meaningful prompt without the need to get into input
forms or code to parse dates, etc.

If you would rather throw everything onto the report and use that for
summing, that is still easy to do, but a little longer to explain, which
I'm
afraid I don't have time for right now. There is enough in the online
help
to show you how to create summed fields on a report though.

Hope that helps,

Steve C

Thanks Steve,

I am summing in the query and then just reporting it. This is where I am
stuck. The way my query is now, I have TruckA = 168 hours (7 days x 24
hours)
TruckB = 168 hours

If I add the date field to my query I get:
TruckA = 24
TruckA = 24 (total of 7 times)
TruckB = 24
TruckB = 24 etc.

I am not sure how to filter and sum at the same time.

I am new to the database world.

 

[Bob Quintal]

Either I am not explaining myself, or I just don't get it.

Before I added the date field, the query summed up all the hours
for each peice of equipment. So, I would have 1 entry for each
peice of equipment. Which makes sense and that is what I think I
need..

I added the date field and put in the date filters. If I look at
the query's data, it shows my entries in that specific date range.
So, say my range is over 7 days, I have 7 entries for each peice
of equipment. Which also makes sense. I am using the sum
command. It sums up for each day, which also makes sense but, I
want to sum up for the entire date range for each piece of
equipment. This is where I am stuck. I am missing a step to sum
up all the entries for each piece of equipment for a specified
time frame.

Chad

When you added the date and filters into the totals query, Access
defaulted the Total: row to Group By. Change that to Where. (It's the
bottom choice in the combo box.) and make sure that the checkbox
under your date in hte show row is NOT checked.


Bob Q

OK, so you either need to filter in the query before you total,
so you only
sum the relevant results, or pass everything to the report,
filter the report
(so you must include the date field), then create a calculated
control on the
report.

You can get parameters into a query very quickly. Include a date
field in the selection then in the criteria box type something
like:

=[startDate] And <=[endDate]

This will display a pop-up box to the user. The prompt will be
whatever you
put in the brackets, so [Please enter Start Date] or [What is the
end date?]
would give the user a meaningful prompt without the need to get
into input forms or code to parse dates, etc.

If you would rather throw everything onto the report and use that
for summing, that is still easy to do, but a little longer to
explain, which I'm
afraid I don't have time for right now. There is enough in the
online help
to show you how to create summed fields on a report though.

Hope that helps,

Steve C

Thanks Steve,

I am summing in the query and then just reporting it. This is
where I am stuck. The way my query is now, I have TruckA = 168
hours (7 days x 24 hours)
TruckB = 168 hours

If I add the date field to my query I get:
TruckA = 24
TruckA = 24 (total of 7 times)
TruckB = 24
TruckB = 24 etc.

I am not sure how to filter and sum at the same time.

I am new to the database world.
message
Hi, Chad.

Without seeing your query it is difficult to give a complete
answer, but
yes, you will certainly need a date field to filter on! Are
you doing the
summing on the report, or in the query and just displaying the
result? You
will have to make sure your filter is applied before you sum -
ie if you
produce the sum result in the query then just display it on
the report, adding the date filter to the report will make no
difference.

One other aside: when using dates in parameters, surround them
with the #
symbol - this tells Access it is handling a date:

DoCmd.OpenReport Form_MainForm.ReportName, acPreview, , "Date
Between #" &
txtFromDate & "# and #" & txtToDate & "#"

Hope that's food for thought.

Steve C


:

Hi All,

I have a query called DataSum. In this query I am summing
all the hours
that a peice of equipment is used. In my report, it displays
a summary
of
each piece of equipment and the total hours used in the DB.

I created a form so that the user can specify the date range
to use. I have
2 variables txtFromDate & txtToDate.

Now I want to filter my report by these 2 dates. It doesn't
work, but I
am
just guessing here. Could someone point me in the right
direction.

Thanks
Chad

DoCmd.OpenReport Form_MainForm.ReportName, acPreview, , "Date
Between " &
txtFromDate & " and " & txtToDate

I think my problem is that I don't have a date field in my
query. When I
do, my query no longer sums properly. I think it sums for
each day, which I
don't want. I guess I need to know how to filter the data
without have
the
date field in there.