Form filter & macro

[LJS]

I have a form with an unbound text box field [Enter Month] formatted to mmm
yy. I have similar 4 append queries such as
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
HAVING (((Format([Date],"mmm yy"))=[forms]![PostingToGL].[Enter Month]) AND
((ExpenseLedger.Posted)=No));

I cannot get this filter to work from the HAVING statement when I operate
the command button linked to the macro OpenQuery.

Any suggestions? Thanks

 

[Allen Browne]

There are several issues here.

The first is that, regardless of how you format it, a Date/Time value is a
date, complete with a day. You can format it so the day is hidden, but it
still has one, and you need to take that into account. (Details below.)

The next issue is that when you use the Format() function, the output is
text (not a date.) It will therefore cause problems if you try to assign the
result to a Date/Time field.

The next issue is that Date is a reserved word both in queries and in code,
so not a good choice for a field name. Access is likely to misunderstand it
as the system date, and give wrong results.

To get around this, I suggest you:
- format the text box as General Date (so you can see the first of the
month);
- declare the parameter (so Access understands it as a date);
- try something like this:

PARAMETERS [forms]![PostingToGL]![txtStartDate] DateTime;
INSERT INTO LedgerAccounts ( Type, AccDate, AcctNo, Debit, Credit )
SELECT ExpenseLedger.Type,
DateSerial(Year([AccDate]), Month([AccDate]),1) AS MonthStart,
ExpenseLedger.AcctNo,
Sum(ExpenseLedger.Debit) AS SumOfDebit,
Sum(ExpenseLedger.Credit) AS SumOfCredit
FROM ExpenseLedger
WHERE ((AccDate >= [forms]![PostingToGL]![txtStartDate])
AND (AccDate < DateAdd("m", 1, [forms]![PostingToGL]![txtStartDate]))
AND (Posted = False))
GROUP BY ExpenseLedger.Type,
DateSerial(Year([AccDate]), Month([AccDate]),1),
ExpenseLedger.AcctNo;

 

[LJS]

Allen,
Thanks for the help.
I had to change LedgerAccounts Field name to “AccDate†in mmm yy format.
The txtStartDate field on the PostingToGL form has no event attached. Is
this OK?
When running the query from the form or as a query the Enter Parameter box
appears requesting the “AccDate’. When entered the query runs with 2 small
problems.
1. The sum values pick up the wanted month (Oct) plus unwanted (Nov) data.
Need date range to be limited to only one month.
2. The AccDate data transferred to the LedgerAccounts tbl shows Jan 07 not
Oct 07.
Could I seek your help again?
Thanks

John

There are several issues here.

The first is that, regardless of how you format it, a Date/Time value is a
date, complete with a day. You can format it so the day is hidden, but it
still has one, and you need to take that into account. (Details below.)

The next issue is that when you use the Format() function, the output is
text (not a date.) It will therefore cause problems if you try to assign the
result to a Date/Time field.

The next issue is that Date is a reserved word both in queries and in code,
so not a good choice for a field name. Access is likely to misunderstand it
as the system date, and give wrong results.

To get around this, I suggest you:
- format the text box as General Date (so you can see the first of the
month);
- declare the parameter (so Access understands it as a date);
- try something like this:

PARAMETERS [forms]![PostingToGL]![txtStartDate] DateTime;
INSERT INTO LedgerAccounts ( Type, AccDate, AcctNo, Debit, Credit )
SELECT ExpenseLedger.Type,
DateSerial(Year([AccDate]), Month([AccDate]),1) AS MonthStart,
ExpenseLedger.AcctNo,
Sum(ExpenseLedger.Debit) AS SumOfDebit,
Sum(ExpenseLedger.Credit) AS SumOfCredit
FROM ExpenseLedger
WHERE ((AccDate >= [forms]![PostingToGL]![txtStartDate])
AND (AccDate < DateAdd("m", 1, [forms]![PostingToGL]![txtStartDate]))
AND (Posted = False))
GROUP BY ExpenseLedger.Type,
DateSerial(Year([AccDate]), Month([AccDate]),1),
ExpenseLedger.AcctNo;

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

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

I have a form with an unbound text box field [Enter Month] formatted to
mmm
yy. I have similar 4 append queries such as
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
HAVING (((Format([Date],"mmm yy"))=[forms]![PostingToGL].[Enter Month])
AND
((ExpenseLedger.Posted)=No));

I cannot get this filter to work from the HAVING statement when I operate
the command button linked to the macro OpenQuery.

Any suggestions? Thanks

 

[Allen Browne]

Is AccDate a Date/Time field in your table?
(The Format in the table is irrelevant: probably best left blank.)

If Access pops up a parameter box even when the form is open, then the name
you have in your query does not match the actual name of the form and text
box, or the form is not open, so no: that's not working correctly.

If it's still not right:
a) Tell me the name and data type of the date field (when you open the table
in design view.)

b) Tell me the name of the form, and the name of the date text box on your
form.

c) Switch your query to SQL View (View menu, in query design), copy the
whole statement, and paste it here.

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

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

Allen,
Thanks for the help.
I had to change LedgerAccounts Field name to “AccDate†in mmm yy format.
The txtStartDate field on the PostingToGL form has no event attached. Is
this OK?
When running the query from the form or as a query the Enter Parameter box
appears requesting the “AccDate’. When entered the query runs with 2 small
problems.
1. The sum values pick up the wanted month (Oct) plus unwanted (Nov) data.
Need date range to be limited to only one month.
2. The AccDate data transferred to the LedgerAccounts tbl shows Jan 07 not
Oct 07.
Could I seek your help again?
Thanks

John

There are several issues here.

The first is that, regardless of how you format it, a Date/Time value is
a
date, complete with a day. You can format it so the day is hidden, but it
still has one, and you need to take that into account. (Details below.)

The next issue is that when you use the Format() function, the output is
text (not a date.) It will therefore cause problems if you try to assign
the
result to a Date/Time field.

The next issue is that Date is a reserved word both in queries and in
code,
so not a good choice for a field name. Access is likely to misunderstand
it
as the system date, and give wrong results.

To get around this, I suggest you:
- format the text box as General Date (so you can see the first of the
month);
- declare the parameter (so Access understands it as a date);
- try something like this:

PARAMETERS [forms]![PostingToGL]![txtStartDate] DateTime;
INSERT INTO LedgerAccounts ( Type, AccDate, AcctNo, Debit, Credit )
SELECT ExpenseLedger.Type,
DateSerial(Year([AccDate]), Month([AccDate]),1) AS MonthStart,
ExpenseLedger.AcctNo,
Sum(ExpenseLedger.Debit) AS SumOfDebit,
Sum(ExpenseLedger.Credit) AS SumOfCredit
FROM ExpenseLedger
WHERE ((AccDate >= [forms]![PostingToGL]![txtStartDate])
AND (AccDate < DateAdd("m", 1, [forms]![PostingToGL]![txtStartDate]))
AND (Posted = False))
GROUP BY ExpenseLedger.Type,
DateSerial(Year([AccDate]), Month([AccDate]),1),
ExpenseLedger.AcctNo;

I have a form with an unbound text box field [Enter Month] formatted to
mmm
yy. I have similar 4 append queries such as
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
HAVING (((Format([Date],"mmm yy"))=[forms]![PostingToGL].[Enter Month])
AND
((ExpenseLedger.Posted)=No));

I cannot get this filter to work from the HAVING statement when I
operate
the command button linked to the macro OpenQuery.

Any suggestions? Thanks

 

[LJS]

Problem solved. Thank you for your help Allen.
I forgot to change the date field name in the query source table. Your SQL
statement runs perfectly. Thank you.

John

Is AccDate a Date/Time field in your table?
(The Format in the table is irrelevant: probably best left blank.)

If Access pops up a parameter box even when the form is open, then the name
you have in your query does not match the actual name of the form and text
box, or the form is not open, so no: that's not working correctly.

If it's still not right:
a) Tell me the name and data type of the date field (when you open the table
in design view.)

b) Tell me the name of the form, and the name of the date text box on your
form.

c) Switch your query to SQL View (View menu, in query design), copy the
whole statement, and paste it here.

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

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

Allen,
Thanks for the help.
I had to change LedgerAccounts Field name to “AccDate†in mmm yy format.
The txtStartDate field on the PostingToGL form has no event attached. Is
this OK?
When running the query from the form or as a query the Enter Parameter box
appears requesting the “AccDate’. When entered the query runs with 2 small
problems.
1. The sum values pick up the wanted month (Oct) plus unwanted (Nov) data.
Need date range to be limited to only one month.
2. The AccDate data transferred to the LedgerAccounts tbl shows Jan 07 not
Oct 07.
Could I seek your help again?
Thanks

John

There are several issues here.

The first is that, regardless of how you format it, a Date/Time value is
a
date, complete with a day. You can format it so the day is hidden, but it
still has one, and you need to take that into account. (Details below.)

The next issue is that when you use the Format() function, the output is
text (not a date.) It will therefore cause problems if you try to assign
the
result to a Date/Time field.

The next issue is that Date is a reserved word both in queries and in
code,
so not a good choice for a field name. Access is likely to misunderstand
it
as the system date, and give wrong results.

To get around this, I suggest you:
- format the text box as General Date (so you can see the first of the
month);
- declare the parameter (so Access understands it as a date);
- try something like this:

PARAMETERS [forms]![PostingToGL]![txtStartDate] DateTime;
INSERT INTO LedgerAccounts ( Type, AccDate, AcctNo, Debit, Credit )
SELECT ExpenseLedger.Type,
DateSerial(Year([AccDate]), Month([AccDate]),1) AS MonthStart,
ExpenseLedger.AcctNo,
Sum(ExpenseLedger.Debit) AS SumOfDebit,
Sum(ExpenseLedger.Credit) AS SumOfCredit
FROM ExpenseLedger
WHERE ((AccDate >= [forms]![PostingToGL]![txtStartDate])
AND (AccDate < DateAdd("m", 1, [forms]![PostingToGL]![txtStartDate]))
AND (Posted = False))
GROUP BY ExpenseLedger.Type,
DateSerial(Year([AccDate]), Month([AccDate]),1),
ExpenseLedger.AcctNo;

I have a form with an unbound text box field [Enter Month] formatted to
mmm
yy. I have similar 4 append queries such as
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
HAVING (((Format([Date],"mmm yy"))=[forms]![PostingToGL].[Enter Month])
AND
((ExpenseLedger.Posted)=No));

I cannot get this filter to work from the HAVING statement when I
operate
the command button linked to the macro OpenQuery.

Any suggestions? Thanks