Command button resetting a query parameter?

[BFish via AccessMonster.com]

Is what I'm trying to do possible? I have a form based off a query which in
turn is based off a totals query which has a date critera set for the current
month. I would like to have a user be able to use a command button to see
the priors months data on click. In other words change the critera in the
totals query to a Between Statement for the prior month and then requery both
queries. Everything I have tried is has either failed to return a result or
given a error message.

Thanks for any help.

Bill Fischer

 

[tina]

have you considered removing the "month" criteria from the query(s), and
instead pulling all the data into the form - then using a command button in
the form to set the form Filter to the subset of data you want?

hth

 

[Duane Hookom]

You should use controls on forms as your criteria in queries. When you want
to update the records displayed in a form, use code like:

Me.Requery

 

[BFish via AccessMonster.com]

Thanks to both for replying.

The first query totals invoice on a daily basis: count, sum of sales and sum
of cost. The second query will then add a count field of records, gross
profit and %, invoice avg $ on the daily record and also a running MTD total
for all fields. I put the month criteria in the first query due to a quick
statement method for the DCount for totaling the running day of the month in
the second query. Also for the number of records I thought the query/forms
would run quicker (approx. 500 to 800 new records a month) if I limited the
records going into calculations.

My first query SQL:

SELECT Count(tblInvoices.InvoiceNumber) AS CountOfInvoiceNumber, tblInvoices.
Date, Sum(tblInvoices.InvoiceSale) AS SumOfInvoiceSale, Sum(tblInvoices.
InvoiceCost) AS SumOfInvoiceCost, DatePart("d",[Date]) AS ADay, DatePart("m",
[Date]) AS AMonth, DatePart("yyyy",[Date]) AS AYear
FROM tblInvoices
GROUP BY tblInvoices.Date, DatePart("d",[Date]), DatePart("m",[Date]),
DatePart("yyyy",[Date])
HAVING (((tblInvoices.Date) Between DateSerial(Year(Date()),Month(Date())-1,1)
And DateSerial(Year(Date()),Month(Date()),0)));

My second query SQL:

SELECT qselInvoiceTotalDate.Date, DCount("*","qselInvoiceWorkDayTotal","[Date]
<=#" & [Date] & "#") AS [Day#], qselInvoiceTotalDate.SumOfInvoiceSale,
qselInvoiceTotalDate.SumOfInvoiceCost, [SumOfInvoiceSale]-[SumOfInvoiceCost]
AS Gross, [Gross]/[SumOfInvoiceSale] AS [Gross %], qselInvoiceTotalDate.
CountOfInvoiceNumber, [SumOfInvoiceSale]/[CountOfInvoiceNumber] AS [Inv Avg $]
, CCur(DSum("SumOfInvoiceSale","qselInvoiceTotalDate","DatePart('d', [Date])
<=" & [ADay] & " And DatePart('m', [Date])=" & [AMonth] & " And DatePart
('yyyy', [Date])=" & [AYear] & "")) AS [MTD Sale], CCur(DSum
("SumOfInvoiceCost","qselInvoiceTotalDate","DatePart('d', [Date])<=" & [ADay]
& " And DatePart('m', [Date])=" & [AMonth] & " And DatePart('yyyy', [Date])="
& [AYear] & "")) AS [MTD Cost], [MTD Sale]-[MTD Cost] AS [MTD Grs Prf], [MTD
Grs Prf]/[MTD Sale] AS [MTD Grs %], DSum("CountOfInvoiceNumber",
"qselInvoiceTotalDate","DatePart('d', [Date])<=" & [ADay] & " And DatePart
('m', [Date])=" & [AMonth] & " And DatePart('yyyy', [Date])=" & [AYear] & "")
AS [MTD # Inv], [MTD Sale]/[MTD # Inv] AS [MTD Inv Avg $]
FROM qselInvoiceTotalDate
ORDER BY qselInvoiceTotalDate.Date;

Is this possible in a form without a slow return on the calculations? This
is the reason I thought two command buttons would be the best route, one for
current month and set as default and second for the prior month.

Thanks again,

Bill Fischer

You should use controls on forms as your criteria in queries. When you want
to update the records displayed in a form, use code like:

Me.Requery

Is what I'm trying to do possible? I have a form based off a query which
in

[quoted text clipped - 11 lines]

Bill Fischer

 

[Duane Hookom]

Grouping on the day, month, and year in the first query isn't necessary.
I would change the first query to:
SELECT Count(tblInvoices.InvoiceNumber) AS CountOfInvoiceNumber,
tblInvoices.Date, Sum(tblInvoices.InvoiceSale) AS SumOfInvoiceSale,
Sum(tblInvoices.InvoiceCost) AS SumOfInvoiceCost
FROM tblInvoices
WHERE (((tblInvoices.Date) Between
DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0)))
GROUP BY tblInvoices.Date;

You could substitute a reference to a date value in a text box on a form for
Date().

I am not sure what you are attempting to do with the second query or what
"qselInvoiceWorkDayTotal" looks like.

It seems that you might be attempting to create a running sum query for the
month. I think there is a better method but don't know for sure what you are
working with. Text boxes on reports can be set to use a Running Sum which
would greatly optimize and simplify your query.

--
Duane Hookom
MS Access MVP
--

Thanks to both for replying.

The first query totals invoice on a daily basis: count, sum of sales and
sum
of cost. The second query will then add a count field of records, gross
profit and %, invoice avg $ on the daily record and also a running MTD
total
for all fields. I put the month criteria in the first query due to a
quick
statement method for the DCount for totaling the running day of the month
in
the second query. Also for the number of records I thought the
query/forms
would run quicker (approx. 500 to 800 new records a month) if I limited
the
records going into calculations.

My first query SQL:

SELECT Count(tblInvoices.InvoiceNumber) AS CountOfInvoiceNumber,
tblInvoices.
Date, Sum(tblInvoices.InvoiceSale) AS SumOfInvoiceSale, Sum(tblInvoices.
InvoiceCost) AS SumOfInvoiceCost, DatePart("d",[Date]) AS ADay,
DatePart("m",
[Date]) AS AMonth, DatePart("yyyy",[Date]) AS AYear
FROM tblInvoices
GROUP BY tblInvoices.Date, DatePart("d",[Date]), DatePart("m",[Date]),
DatePart("yyyy",[Date])
HAVING (((tblInvoices.Date) Between
DateSerial(Year(Date()),Month(Date())-1,1)
And DateSerial(Year(Date()),Month(Date()),0)));

My second query SQL:

SELECT qselInvoiceTotalDate.Date,
DCount("*","qselInvoiceWorkDayTotal","[Date]
<=#" & [Date] & "#") AS [Day#], qselInvoiceTotalDate.SumOfInvoiceSale,
qselInvoiceTotalDate.SumOfInvoiceCost,
[SumOfInvoiceSale]-[SumOfInvoiceCost]
AS Gross, [Gross]/[SumOfInvoiceSale] AS [Gross %], qselInvoiceTotalDate.
CountOfInvoiceNumber, [SumOfInvoiceSale]/[CountOfInvoiceNumber] AS [Inv
Avg $]
, CCur(DSum("SumOfInvoiceSale","qselInvoiceTotalDate","DatePart('d',
[Date])
<=" & [ADay] & " And DatePart('m', [Date])=" & [AMonth] & " And DatePart
('yyyy', [Date])=" & [AYear] & "")) AS [MTD Sale], CCur(DSum
("SumOfInvoiceCost","qselInvoiceTotalDate","DatePart('d', [Date])<=" &
[ADay]
& " And DatePart('m', [Date])=" & [AMonth] & " And DatePart('yyyy',
[Date])="
& [AYear] & "")) AS [MTD Cost], [MTD Sale]-[MTD Cost] AS [MTD Grs Prf],
[MTD
Grs Prf]/[MTD Sale] AS [MTD Grs %], DSum("CountOfInvoiceNumber",
"qselInvoiceTotalDate","DatePart('d', [Date])<=" & [ADay] & " And DatePart
('m', [Date])=" & [AMonth] & " And DatePart('yyyy', [Date])=" & [AYear] &
"")
AS [MTD # Inv], [MTD Sale]/[MTD # Inv] AS [MTD Inv Avg $]
FROM qselInvoiceTotalDate
ORDER BY qselInvoiceTotalDate.Date;

Is this possible in a form without a slow return on the calculations?
This
is the reason I thought two command buttons would be the best route, one
for
current month and set as default and second for the prior month.

Thanks again,

Bill Fischer

You should use controls on forms as your criteria in queries. When you
want
to update the records displayed in a form, use code like:

Me.Requery

Is what I'm trying to do possible? I have a form based off a query
which
in

[quoted text clipped - 11 lines]

Bill Fischer

 

[BFish via AccessMonster.com]

Thanks for responding Duane,

Yes I am creating a running total in the second query by day of month and
also calculating MTD totals for each day in that same query (which all works
and is displayed in a subform in datasheet view with intended command buttons
on main form).

I have/had the first query grouping by day, month and year for the a quick
reference in the second query to utilize for the running total of MTD
calculations.

What I am looking to do is display the current month calculations with
<=DateSerial(Year(Date()),Month(Date()),1), but when needed able to go back
and look at the prior months figures with a Between dates statement.

Hopefully this gives you enough of an idea what I need to do. If this can be
done in a form and simplify the queries I would be very open to change the
process, but my experience in form controls to accomplish this end is very
limited. Can you give me an idea of the method?

If my description is unclear please let me know and I will try to clarify.

Bill Fischer

Grouping on the day, month, and year in the first query isn't necessary.
I would change the first query to:
SELECT Count(tblInvoices.InvoiceNumber) AS CountOfInvoiceNumber,
tblInvoices.Date, Sum(tblInvoices.InvoiceSale) AS SumOfInvoiceSale,
Sum(tblInvoices.InvoiceCost) AS SumOfInvoiceCost
FROM tblInvoices
WHERE (((tblInvoices.Date) Between
DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0)))
GROUP BY tblInvoices.Date;

You could substitute a reference to a date value in a text box on a form for
Date().

I am not sure what you are attempting to do with the second query or what
"qselInvoiceWorkDayTotal" looks like.

It seems that you might be attempting to create a running sum query for the
month. I think there is a better method but don't know for sure what you are
working with. Text boxes on reports can be set to use a Running Sum which
would greatly optimize and simplify your query.

Thanks to both for replying.

[quoted text clipped - 78 lines]

 

[Duane Hookom]

As I stated in an earlier reply:
You could substitute a reference to a date value in a text box on a form for
Date().

SELECT Count(InvoiceNumber) AS CountOfInvoiceNumber,
tblInvoices.Date, Sum(InvoiceSale) AS SumOfInvoiceSale,
Sum(InvoiceCost) AS SumOfInvoiceCost,
Day([Date]) AS ADay, Month([Date]) AS AMonth,
Year([Date]) AS AYear
FROM tblInvoices
WHERE Format([Date],"yyyymm") = Format(Forms!frmDate!txtDate,"yyyymm")
GROUP BY tblInvoices.Date, Day([Date]), Month([Date]),
Year([Date]);

This will allow you to enter any date into a control on a form to choose a
month.

--
Duane Hookom
MS Access MVP
--

Thanks for responding Duane,

Yes I am creating a running total in the second query by day of month and
also calculating MTD totals for each day in that same query (which all
works
and is displayed in a subform in datasheet view with intended command
buttons
on main form).

I have/had the first query grouping by day, month and year for the a quick
reference in the second query to utilize for the running total of MTD
calculations.

What I am looking to do is display the current month calculations with
<=DateSerial(Year(Date()),Month(Date()),1), but when needed able to go
back
and look at the prior months figures with a Between dates statement.

Hopefully this gives you enough of an idea what I need to do. If this can
be
done in a form and simplify the queries I would be very open to change the
process, but my experience in form controls to accomplish this end is very
limited. Can you give me an idea of the method?

If my description is unclear please let me know and I will try to clarify.

Bill Fischer

Grouping on the day, month, and year in the first query isn't necessary.
I would change the first query to:
SELECT Count(tblInvoices.InvoiceNumber) AS CountOfInvoiceNumber,
tblInvoices.Date, Sum(tblInvoices.InvoiceSale) AS SumOfInvoiceSale,
Sum(tblInvoices.InvoiceCost) AS SumOfInvoiceCost
FROM tblInvoices
WHERE (((tblInvoices.Date) Between
DateSerial(Year(Date()),Month(Date())-1,1) And
DateSerial(Year(Date()),Month(Date()),0)))
GROUP BY tblInvoices.Date;

You could substitute a reference to a date value in a text box on a form
for
Date().

I am not sure what you are attempting to do with the second query or what
"qselInvoiceWorkDayTotal" looks like.

It seems that you might be attempting to create a running sum query for
the
month. I think there is a better method but don't know for sure what you
are
working with. Text boxes on reports can be set to use a Running Sum which
would greatly optimize and simplify your query.

Thanks to both for replying.

[quoted text clipped - 78 lines]

Bill Fischer

 

[BFish via AccessMonster.com]

Duane thanks for the help. I was able to get back to this today. Probably a
good thing to step back for a day or two, I got locked into the command
button process and was trying to make your easy approach a lot harder than it
need to be from my earlier posts. I did the text box, made it invisible, two
command buttons one setting to current month the other to prior month to feed
the text box. Works just as you stated with the reference in the query.
Sometimes the old horse just has to learn to drink the water at the trough
instead of running new water from the pump.

Thank you much,

Bill

As I stated in an earlier reply:
You could substitute a reference to a date value in a text box on a form for
Date().

SELECT Count(InvoiceNumber) AS CountOfInvoiceNumber,
tblInvoices.Date, Sum(InvoiceSale) AS SumOfInvoiceSale,
Sum(InvoiceCost) AS SumOfInvoiceCost,
Day([Date]) AS ADay, Month([Date]) AS AMonth,
Year([Date]) AS AYear
FROM tblInvoices
WHERE Format([Date],"yyyymm") = Format(Forms!frmDate!txtDate,"yyyymm")
GROUP BY tblInvoices.Date, Day([Date]), Month([Date]),
Year([Date]);

This will allow you to enter any date into a control on a form to choose a
month.

Thanks for responding Duane,

[quoted text clipped - 54 lines]