Macro filter on query

[LJS]

I need to run 8 append and update queries using the same date range. I
created a Form with the "Month" field as the filter and a command button to
run the macro to run the Append & Update queries for records in the month
range. I can't get the Apply Filter macro to work on the Query Date field.
Any suggestions?

 

[Ken Snell \(MVP\)]

Apply Filter action will not do what you seek.

Your queries must have a WHERE clause that refers in some way to the textbox
on the form where you've entered the Month value/data that you want to use
in the queries.

To assist you further, you need to post the SQL statements of a few of
queries so that we can see what the queries are intending to do, and tell us
what type of data you type into the Month textbox on the form (month name,
e.g., January, Feb, etc.; or month number, e.g., 1, 2, 3, etc.), and how you
want to use this value in the queries.

 

[LJS]

Daily data in the transaction files has been summed with the query
INSERT INTO LedgerAccounts ( Type, [Date], AcctNo, Debit, Credit )
SELECT ExpenseLedger.Type, Format([Date],"mmm yy") AS Exp1,
ExpenseLedger.AcctNo, Sum(ExpenseLedger.Debit) AS SumOfDebit,
Sum(ExpenseLedger.Credit) AS SumOfCredit
FROM ExpenseLedger
GROUP BY ExpenseLedger.Type, Format([Date],"mmm yy"), ExpenseLedger.AcctNo,
ExpenseLedger.Posted;

3 other similar queries for other daily data have also been produced. Using
a Form with an unbound property field [Enter Month] with a "mmm yy" format
and a text box named "txtQueryFilter" with statement
=Forms![PostingToG/L]![Enter Month]. A command button on the form would run
the 8 queries. I am trying to get the queries to see the Form [Enter Month]
field so that all queries will run by macro using the same [Enter month]
value ie “Oct 07â€

After running the 4 queries I need to run 4 Update queries on the source
tables to confirm the data for the time period has been transferred. The
Update queries are :
UPDATE ExpenseLedger SET ExpenseLedger.Posted = Yes
WHERE (((ExpenseLedger.Posted)=No));

The query needs to know the Form [Enter Month] value that was used for the
Append queries.

What do I have do to get the queries to recognize the [Enter Month] value on
the Form?

Thanks

 

[Ken Snell \(MVP\)]

I've been out of town for the past four days. I'll post a reply later today
or tomorrow; sorry for the delay.

--

Ken Snell
<MS ACCESS MVP>

Daily data in the transaction files has been summed with the query
INSERT INTO LedgerAccounts ( Type, [Date], AcctNo, Debit, Credit )
SELECT ExpenseLedger.Type, Format([Date],"mmm yy") AS Exp1,
ExpenseLedger.AcctNo, Sum(ExpenseLedger.Debit) AS SumOfDebit,
Sum(ExpenseLedger.Credit) AS SumOfCredit
FROM ExpenseLedger
GROUP BY ExpenseLedger.Type, Format([Date],"mmm yy"),
ExpenseLedger.AcctNo,
ExpenseLedger.Posted;

< snipped >

 

[Ken Snell \(MVP\)]

I'm not fully understanding exactly which queries should look to the form's
textbox for the "mmm yy" value, but here is a generic query that would do
this:

SELECT Field1, Field2, Field3
FROM TableName
WHERE Field1 = [Forms]![PostingToG/L]![Enter Month]

Does this help you in identifying how to add this WHERE clause to the
queries?

--

Ken Snell
<MS ACCESS MVP>

Daily data in the transaction files has been summed with the query
INSERT INTO LedgerAccounts ( Type, [Date], AcctNo, Debit, Credit )
SELECT ExpenseLedger.Type, Format([Date],"mmm yy") AS Exp1,
ExpenseLedger.AcctNo, Sum(ExpenseLedger.Debit) AS SumOfDebit,
Sum(ExpenseLedger.Credit) AS SumOfCredit
FROM ExpenseLedger
GROUP BY ExpenseLedger.Type, Format([Date],"mmm yy"),
ExpenseLedger.AcctNo,
ExpenseLedger.Posted;

3 other similar queries for other daily data have also been produced.
Using
a Form with an unbound property field [Enter Month] with a "mmm yy" format
and a text box named "txtQueryFilter" with statement
=Forms![PostingToG/L]![Enter Month]. A command button on the form would
run
the 8 queries. I am trying to get the queries to see the Form [Enter
Month]
field so that all queries will run by macro using the same [Enter month]
value ie "Oct 07"

After running the 4 queries I need to run 4 Update queries on the source
tables to confirm the data for the time period has been transferred. The
Update queries are :
UPDATE ExpenseLedger SET ExpenseLedger.Posted = Yes
WHERE (((ExpenseLedger.Posted)=No));

The query needs to know the Form [Enter Month] value that was used for the
Append queries.

What do I have do to get the queries to recognize the [Enter Month] value
on
the Form?

Thanks

Apply Filter action will not do what you seek.

Your queries must have a WHERE clause that refers in some way to the
textbox
on the form where you've entered the Month value/data that you want to
use
in the queries.

To assist you further, you need to post the SQL statements of a few of
queries so that we can see what the queries are intending to do, and tell
us
what type of data you type into the Month textbox on the form (month
name,
e.g., January, Feb, etc.; or month number, e.g., 1, 2, 3, etc.), and how
you
want to use this value in the queries.