Hi All

I have a filter form that I have created to filter/sort data by several ways. There are Combo boxes from which I select specific criteria with related command buttons to open the display form and show the records. On this form I also have from - to date controls in which to enter the From - To date range that I want to be able to better filter the records. So, I select the criteria that I want to filter by from one of the combo boxes and then enter the date range in the From-To date controls and then click on the command button next to the combo box and the display form should open and display only the records in that specified date range. In the one app if I want to see all records for a specific date range I can enter the date criteria in the From-To controls and click the Command button and see only the records for that specific date range. However, that is not what is happening with two of the filter forms in the forms in question. I have this set up on other filter forms and it works as expected. But, I can't seem to figure out why the same code with the form name changed will not work in the two filter forms which are identical to the ones in which it does work.
~~~~~~~~~~~~~~~~~~~~~~~~~~~
Here is the code for the form that does not work:

Private Sub cmdActionDate_Click()
On Error GoTo Err_cmdActionDate_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmApptRec"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdActionDate_Click:
Exit Sub

Err_cmdActionDate_Click:
MsgBox Err.Description
Resume Exit_cmdActionDate_Click

End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

And here is the code for the one that does from the other app. with a different form name.

Private Sub cmdActionDate_Click()
On Error GoTo Err_cmdActionDate_Click

Dim stDocName As String
Dim stLinkCriteria As String

stDocName = "frmCheckRegister"
DoCmd.OpenForm stDocName, , , stLinkCriteria

Exit_cmdActionDate_Click:
Exit Sub

Err_cmdActionDate_Click:
MsgBox Err.Description
Resume Exit_cmdActionDate_Click

End Sub
~~~~~~~~~~~~~~~~~~~~~~~~~~~~

The last one is from a different app, and I have spent days trying to figure out why one works and the other doesn't. Perhaps there is another way to get the results I need, but, this has worked before. I am always open to learning new things, but, I don't know of another way to do it.

Any assistance would be very much appreciated.

Jan

Jan -

If I want to filter data based on one or more criteria I use the method found here. It may look a bit intimidating at first, but start out with one criteria and then add additional ones. Once you get the hang of it I think it will work a treat for you... I usually have the criteria as combo boxes/text boxes on a form with a subform. When the user clicks the Show Results button the subform shows the results of the users selections....

hth,
Jack

You say it doesn't work, but what does it do instead? Do you get an error message?

Hi Jack,

You're right...it does look very intimidating, and confusing A bit beyond my VBA knowledge level to a good degree, but, I will study it and see if I can get my head around it. :-)

I prefer to have a separate full form instead of a subform, as there can be a large number of records to review depending on the subject and date range needed.

Thank you very much for your time to help, I truly appreciate it.

Jan

Jan -

Be stout of heart as I know you can conquer this...because I was able to...and if I can do it there is no doubt in my mind that you can!!!

You can have a separate form for the user to select their criteria because the code creates a QueryDef (saved query) and you can then open a form based on that saved query...

Take your time, create a single criteria form, get it to work then add another criteria and before you know it you will have a very slick 'search' form for your users....

Hang in there!!!

Jack

The 2 posted Subs do not have any filtering action in the code. You must have the filtering actions somewhere else.

Have you checked the RecordSource of the 2 Forms for the (possibly) different criteria / WHERE clauses?

Hi projecttoday,

I get no error message, it just does not filter/sort by the date range as it should, it displays all dates. The way it is supposed to work is;

1.

Click on the down arrow of the combo box and select an item, )e.g., provider name, prescription name, etc.)
Enter the date range = 01/01/09 in the From date control (TxtDate1) and enter 12/31/09 in the To control (TxtDate2)
Click on the command button next to the combo box that I selected the item in
The display form opens and displays all the records for the item I selected between the date range I entered

2.

Enter a date range in the From - To boxes (01/01/09 and 12/31/09)
Click on the command button that relates only to the dates entered in the From-To boxes
The display form opens and displays all the records for all items during that date range

3.

To view all the records without date filtering, I just click on the command button next to the date controls without entering any date range information
The display form opens and displays all the records

These 3 means of filtering on the one form are very time saving when I need to sort in one of these 3 ways. The form works ok, expect for the date filtering.

Here is the SQL from the qry that I have tried for the Appointments filtering:

SELECT tblAppointments.ApptDate, tblAppointments.ApptTime, tblAppointments.ApptWith, tblAppointments.Dept, tblAppointments.ApptFor, tblAppointments.ApptLocation, tblAppointments.ApptStatus, tblAppointments.Comments
FROM tblAppointments
WHERE (((tblAppointments.ApptDate) Between [Forms]![frmApptsFilter]![TxtDate1] And [Forms]![frmApptsFilter]![TxtDate2])) OR ((([Forms]![frmApptsFilter]![TxtDate1]) Is Null)) OR ((([Forms]![frmApptsFilter]![TxtDate2]) Is Null));

However, this will not allow the query to be opened, as I do get an error when I try to do that. It says one or more components of the expression is too long or complicated and I should simplify the parts or variables. But, it does not produce any errors when using the filter form.

Thank you for your time to help, it is appreciated.

Oh I'm strout of heart...it's the mind that does not always step to the plate. :-) But, I will try.

Hi Van,

I have posted the SQL for the query here in my response to projecttoday.


Thanks for the additional input.

Did you try the "#9999-12-31#" method I posted in one of your earlier questions???

Jan -

I know you can do this. Go for the gusto!!! Using the baseball analogy...start swinging away...

Jack

Sorry, Van, but, I guess I lost track of that one. Which message was that posted in?

So you have 1 form with 2 buttons. Both buttons work except one of them won't filter by date.

SELECT tblAppointments.ApptDate, tblAppointments.ApptTime, tblAppointments.ApptWith, tblAppointments.Dept, tblAppointments.ApptFor, tblAppointments.ApptLocation, tblAppointments.ApptStatus, tblAppointments.Comments
FROM tblAppointments
WHERE (((tblAppointments.ApptDate) Between [Forms]![frmApptsFilter]![TxtDate1] And [Forms]![frmApptsFilter]![TxtDate2])) OR ((([Forms]![frmApptsFilter]![TxtDate1]) Is Null)) OR ((([Forms]![frmApptsFilter]![TxtDate2]) Is Null));

is the query that doesn't filter. Can you post the query that does filter?

Also, can you try hardcoding a date to see what it does?

Between #03/07/2010# And #03/09/2010#

Click to view attachmentClick to view attachment[attachment=37
787:FilterForm.png]

Thank you, I will try your suggestion. Here is the SQL for the form that does work. However, it is from a different app and process, but, the date part does work as expected.

PARAMETERS [Forms]![frmCheckingRecFilter]![TxtDate1] DateTime, [Forms]![frmCheckingRecFilter]![TxtDate2] DateTime;
SELECT T.BeginBal, T.CheckNo, T.TransactionDate, T.Transaction, T.CheckDBTAmt, T.DepositAmt, T.InterestAmt, T.TransactionSign, T.TransactionType, T.Comment, (SELECT SUM(Nz(DepositAmt, 0) - Nz(CheckDBTAmt, 0) +Nz(InterestAmt,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.TransactionDate DESC;


Just for clarification of what I have on the current filter form in question, I have attached a snippet of the form. Perhaps it will give a better idea of what I am trying to do. There are 4 combo boxes and four related command buttons, then the date controls and the related command button.

Not sure I entered it correctly, it idid not filter the records according to those dates. Where should this be entered?

In your UA Topic Command Button Does Not Work Properly, Office 2007 and you wrote that you were going to try the method I suggested.

I am sure you are aware than UA asks members not to post multiple Threads/Topics on the same question.

Hi Van,

That post was in regards to the "View All ......" command button on the form that gave an error message when clicked and would not work. It did was not regarding the dates filtering issue which is the basis of this post.

Thank you for the reminder of that information, I am sorry that I did not get back to you on that. My father, who is 86 and a disabled American Veteran, has been in and out of the hospital the past few weeks, and as his primary caregiver I have had to be there with him a good deal of the time. I apologize that I have not had time to work with that issue further, but, I will try the suggestion and let you know if it works or not.

A different app and process????????????? What?

SELECT tblAppointments.ApptDate, tblAppointments.ApptTime, tblAppointments.ApptWith, tblAppointments.Dept, tblAppointments.ApptFor, tblAppointments.ApptLocation, tblAppointments.ApptStatus, tblAppointments.Comments
FROM tblAppointments
WHERE (((tblAppointments.ApptDate) Between #03/01/2010# And #03/31/2010#)) OR ((([Forms]![frmApptsFilter]![TxtDate1]) Is Null)) OR ((([Forms]![frmApptsFilter]![TxtDate2]) Is Null))

Assuming I've coded the constants correctly, and I have been working on SQL Server today and that's different, then this should only return records for March 2010. If that's not the case then you not even hitting it.

I have responded to your post on that message.

Thank you.

The other query SQL is for a check registry app, and query is more complicated, but, the Where part regarding the date filtering is what I was mainly referring to.

I have copy/pasted the SQL you have posted here into the related query and when I open the query in datasheet view it does filter the records by the date range you provided, but, when I select any thing from any one of the combo boxes and click the command button on the filter form all records are displayed. Is this expected behavior?

Not if it's hitting the same query.

I replaced the SQL in the same query. I put the SQL into a new query to test, and when I opened the new query all the records were displayed, not just those on the set date range. What is the expected behavior with this? Perhaps I am not doing the correct steps to test it properly

I replaced the SQL in the same query. I put the SQL into a new query to test, and when I opened the new query all the records were displayed, not just those on the set date range. What is the expected behavior with this? Perhaps I am not doing the correct steps to test it properly

I suggest you stick to one thread rather than concurrent threads that confuse potential respondents. I will stop responding to this thread.

The topics of my two threads, although involving the same database, are two different issues, involving two different forms, two different command functions using two different queries. That they both involve command buttons should not be a disqualifying factor of posting separate threads for the different problems. I don't understand the confusion between the two being different topics and problems, but, I will consider this thread closed.

Thanks for to all for your time and help.