Need to add date sorting to form

[Jan Il]

Hi all Access 2002 XP - W2K

I am in need of adding a date sorting process to an existing filter form, to
sort groups of records by date. I have tried to set this up, however, as
it is not working properly, I don't know if it is in the query setup, in the
form itself, or the command button setup. While I have done a few of these
before, this type of query is a bit different for me, so I'm not really sure
if I am doing things right to not. The query works on it's own perfectly,
it is just in adding the date information that is causing the balk. When I
try to open the query, I get a Parameter Value box for the TxtDate1 and
TxtDate2, and it I click OK, it will open the query. However, when I go to
the filter form and enter dates into the respective date boxes, I get the
same Parameter Value boxes, and after clicking OK on both, the form opens
but there is no data. There are records for that dates entered.

Below is the SQL for the query the information is based on, and I would very
much appreciate it if someone would review the information and let me know
if I have entered something in this query for the date sorting incorrectly.:

SELECT T.BeginBal, T.CheckNo, T.DCP, T.CheckDate, T.Transaction,
T.CheckDCPAmt, T.DepositAmt, T.TransactionType, T.Comment, (SELECT
SUM(Nz(DepositAmt, 0) - Nz(CheckDCPAmt, 0) + Nz(BeginBal,0))
FROM MyCheckRegister T1
WHERE T1.CheckDate <= T.CheckDate) AS RunningBalance
FROM MyCheckRegister AS T
WHERE (((T.CheckDate) Between [Forms]![frmCheckRecFilter]![TxtDate1] And
[Forms]![frmCheckRecFilter]![TxtDate2])) OR
((([Forms]![frmCheckRecFilter]![TxtDate1]) Is Null)) OR
((([Forms]![frmCheckRecFIlter]![TxtDate2]) Is Null))
ORDER BY T.CheckNo, T.DCP, T.CheckDate;


Jan
Smiles are meant to be shared,
that's why they're so contagious.

 

[Lorena]

pruebe lorena

 

[Wayne Morgan]

A couple of things.

1) You may need to define the parameters. Open the query in Design View. On
the menu bar, go to Query|Parameters... Enter the two parameters
([Forms]![frmCheckRecFilter]![TxtDate1] and
[Forms]![frmCheckRecFilter]![TxtDate2]) and give them the Date/Time data
type. You'll have to supply the brackets, they won't be put in automatically
as they are in the query design grid.

2) If you are being prompted for the parameters when you open the query, it
means the query doesn't see the values on the form. There are two possible
reasons for this, the parameter is misspelled or the form isn't open. The
form has to be open for the query to see it, hidden is ok (Visible = False),
just as long as it is open.

 

[Jan Il]

Hi Wayne

A couple of things.

1) You may need to define the parameters. Open the query in Design View. On
the menu bar, go to Query|Parameters... Enter the two parameters
([Forms]![frmCheckRecFilter]![TxtDate1] and
[Forms]![frmCheckRecFilter]![TxtDate2]) and give them the Date/Time data
type. You'll have to supply the brackets, they won't be put in automatically
as they are in the query design grid.

2) If you are being prompted for the parameters when you open the query, it
means the query doesn't see the values on the form. There are two possible
reasons for this, the parameter is misspelled or the form isn't open. The
form has to be open for the query to see it, hidden is ok (Visible = False),
just as long as it is open.

I followed your instructions and this is the current SQL, which does now
work as it should with the data sorting command button. I did make one
change, that being changing the CheckDate to TransactionDate throughout. All
is now working well.

I have just two questions regarding the filter form and the dates:
1. How do I get the date controls to return to empty when returning from the
record form that is opened, so that they are ready blank and for the next
date input. I have the control default value set to Null, but, the previous
entry is still there, it is not clearing when I go back to the filter form.

2. I can't remember how to get the dates to work with the other filter
controls and command buttons;. i.e. when I make a selection of a record
type in one of the controls, then enter the date period I want to review,
and click the associated command button for that record control, how can I
get it to open the record form to display only the records in the date
period I have entered? I have done this before, but, dang if I can remember
the correct steps. )

New SQL

PARAMETERS [Forms]![frmCheckingRecFilter]![TxtDate1] DateTime,
[Forms]![frmCheckingRecFilter]![TxtDate2] DateTime;
SELECT T.BeginBal, T.CheckNo, T.DCP, T.TransactionDate, T.Transaction,
T.CheckDCPAmt, T.DepositAmt, T.TransactionType, T.Comment, (SELECT
SUM(Nz(DepositAmt, 0) - Nz(CheckDCPAmt, 0) + Nz(BeginBal,0))
FROM MyCheckRegister T1
WHERE T1.TransactionDate <= T.TransactionDate) AS RunningBalance
FROM MyCheckRegister AS T
WHERE (((T.TransactionDate) Between
[Forms]![frmCheckingRecFilter]![TxtDate1] And
[Forms]![frmCheckingRecFilter]![TxtDate2])) OR
((([Forms]![frmCheckingRecFilter]![TxtDate1]) Is Null)) OR
((([Forms]![frmCheckingRecFilter]![TxtDate2]) Is Null))
ORDER BY T.CheckNo, T.DCP, T.TransactionDate;

Thank you very much for your time and help, I really do appreciate it.

Jan
Smiles are meant to be shared,
that's why they're so contagious.

--
Wayne Morgan
MS Access MVP

Hi all Access 2002 XP - W2K

I am in need of adding a date sorting process to an existing filter form,
to
sort groups of records by date. I have tried to set this up, however, as
it is not working properly, I don't know if it is in the query setup, in
the
form itself, or the command button setup. While I have done a few of
these
before, this type of query is a bit different for me, so I'm not really
sure
if I am doing things right to not. The query works on it's own perfectly,
it is just in adding the date information that is causing the balk. When I
try to open the query, I get a Parameter Value box for the TxtDate1 and
TxtDate2, and it I click OK, it will open the query. However, when I go
to
the filter form and enter dates into the respective date boxes, I get the
same Parameter Value boxes, and after clicking OK on both, the form opens
but there is no data. There are records for that dates entered.

Below is the SQL for the query the information is based on, and I would
very
much appreciate it if someone would review the information and let me know
if I have entered something in this query for the date sorting
incorrectly.:

SELECT T.BeginBal, T.CheckNo, T.DCP, T.CheckDate, T.Transaction,
T.CheckDCPAmt, T.DepositAmt, T.TransactionType, T.Comment, (SELECT
SUM(Nz(DepositAmt, 0) - Nz(CheckDCPAmt, 0) + Nz(BeginBal,0))
FROM MyCheckRegister T1
WHERE T1.CheckDate <= T.CheckDate) AS RunningBalance
FROM MyCheckRegister AS T
WHERE (((T.CheckDate) Between [Forms]![frmCheckRecFilter]![TxtDate1] And
[Forms]![frmCheckRecFilter]![TxtDate2])) OR
((([Forms]![frmCheckRecFilter]![TxtDate1]) Is Null)) OR
((([Forms]![frmCheckRecFIlter]![TxtDate2]) Is Null))
ORDER BY T.CheckNo, T.DCP, T.CheckDate;


Jan
Smiles are meant to be shared,
that's why they're so contagious.

 

[Wayne Morgan]

1) It depends on "how" you return to the filter form. You will need an
"event" to fire. Setting the default value of a control will give you that
value for a bound control when you go to a new record. These probably are
not, nor need to be, bound controls. Some possible events would be the Close
event of the records form, the Got Focus event of the filter form, the Click
event of a button on the filter form or, perhaps, the Double Click event of
the detail section on the filter form. You would then put code in one of
these events that would set the textboxes to whatever you desire. To refer
to the filter form from the records form, the syntax would be similar to

Forms!frmFilterForm!txtStartDate

2) In the DoCmd.OpenForm call, set the WhereCondition to the date range.

Example:
"[DateField] Between #" & Me.txtStartDate & "# And #" & Me.txtEndDate & "#"

 

[Jan Il]

Hi Wayne

Thank you very much, I'll give these a try and see how the work. The 2)
date reference code very familiar now. I really appreciate your additional
time and help.

Jan

1) It depends on "how" you return to the filter form. You will need an
"event" to fire. Setting the default value of a control will give you that
value for a bound control when you go to a new record. These probably are
not, nor need to be, bound controls. Some possible events would be the Close
event of the records form, the Got Focus event of the filter form, the Click
event of a button on the filter form or, perhaps, the Double Click event of
the detail section on the filter form. You would then put code in one of
these events that would set the textboxes to whatever you desire. To refer
to the filter form from the records form, the syntax would be similar to

Forms!frmFilterForm!txtStartDate

2) In the DoCmd.OpenForm call, set the WhereCondition to the date range.

Example:
"[DateField] Between #" & Me.txtStartDate & "# And #" & Me.txtEndDate & "#"

--
Wayne Morgan
MS Access MVP

Hi Wayne

I have just two questions regarding the filter form and the dates:
1. How do I get the date controls to return to empty when returning from
the
record form that is opened, so that they are ready blank and for the next
date input. I have the control default value set to Null, but, the
previous
entry is still there, it is not clearing when I go back to the filter
form.

2. I can't remember how to get the dates to work with the other filter
controls and command buttons;. i.e. when I make a selection of a record
type in one of the controls, then enter the date period I want to review,
and click the associated command button for that record control, how can I
get it to open the record form to display only the records in the date
period I have entered? I have done this before, but, dang if I can
remember
the correct steps. )