Adding DATE filter to Multi Search Form

[fau5tu5]

attached is a template "search4" which allows one to search a db by a combination Text(*), and Number(<=>), selections.. up to 3 fields.

So a 3 variable search/filter with a Report Generating option.

It does so with VBA and a little SQL by allowing the user to select the Catagory (field) in a drop down, which once selected sets the parameter options for the search which they can chose between.. Like ">,<,=" if the field is numeric, or "contains" if its text.

works great... BUT...

BUT..

I need to be able to also search/filter by DATE "Before, After, On".

I have tried to modify the template form to work in such a way and tried a number of methods or syntax to acheive this. To no avail. I believe that perhaps another SQL command is required to identify DATES and then integrate them into the commands after the FIELD variable has been selected.

But with little to no SQL in VBA experience I'm not sure what it is.. I have some (very little,.. a few small projects) VBA experience.

In my modified version of the template, I noted where I had made changes. I did add the appropriate fields on the actual form, as well as DB I believe..

I would really REALLY appreciate help on this..
If someone could get the DATE variable working and be kind enough to jot down a basic explaination of how you did it,.. I will ,.. uh.. ummm.. ??? be really greatful?..

THANKS>>>>>>>

 

[3dman]

Looks like an assignment. I won't tell exactly what the answer is, but I'll guide you there. Anyways, here are some code that may get you going. I haven't tested the codes yet. Also you need to add two more text boxes.

Good Luck!

Code:
'Date example
If Not IsNull(Me.txtStartDate) Then
strWhere = strWhere & "([Date] >= " & Format(Me.txtStartDate, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
strWhere = strWhere & "([Date] < " & Format(Me.txtEndDate + 1, conJetDate) & ") AND "
End If



lngLen = Len(strWhere) -5
If lngLen <= 0 then
msgbox "Please enter a criteria", vbInformation, "Cannot find"
Else
strWhere = Left$(strWhere, lngLen)
Me.Filter = strWhere
Me.FilterOn = True
End if
End Sub

 

[JANR]

conJetDate??

If you use this you must tell access what it is, add this on top of the sub.

Const conJetDate = "\#mm\/dd\/yyyy\#"

JR

edit: this constant is to force dates into US format which VBA must have or else it throws an error.

 

[George145]

Hello,

I had the same issue some months ago, but this format didn't work correctly for me (it filtered wrong dates). It probably is correct in most cases though, as I have already seen it in many forums, but maybe the format also depends on the computer's regional settings.

In case this doesn't work for some of you, you can try formatting with vbGeneralDate for example (this one worked for me), or with vbShortDate, instead of "conJetDate".

 

[fau5tu5]

So before I start trying to implement these suggestions.. I need to make sure of something.

There are to be Multiple (perhaps 6), different DATE formatted fields in the database to chose from, Not just one.

And I need the user to be able to search any of them (by dropdown selection like the other fields) using both, "After" (start date) ,and "Before" (enddate) as well as either individually , or as well as the "ON" ( onDate)

I dont mind adding a second set or even a couple sets of Datefield TextBoxes, but they need to be able to accomomdate the variety of search fields, similar to the present searches.

In otherwords, it needs to be a flexible Date Search, not a PRESET DateSearch based on a single field.

Does that make sense?

Will this code method accomodate that?

Ie. seach Pulldown Choices = initial_receipt_date, close_date, follow_Up_date, etc.
and once selected the option of "Before", "After", "on"

as well as seach/filter in conjunction with the existing filters, not independently.

are we still on track using the method suggested?

 

[fau5tu5]

BTW.. this isnt an Assignment... this is helping the wife with something so her group at work doesnt have to be limited to using a huge and cumbersome EXCEL sheet for tracking patient/client cases.



The sample/template came from another Site,.. http://www.techonthenet.com/access/forms/search4.php

 

[George145]

Well,

In case you want the user to be able to search on 2 or more date fields at the same time, you will need 6*2=12 text boxes (Date1Start, Date1End, Date2Start, ..., Date6End). In this case you just have to repeat the code 3dman proposed 6 times (one for each date field - an alternative and much better way is to include the following code in a loop with the appropriate parameters, for looping through all date textboxes, if you cannot figure out how just ask)

If Not IsNull(Me.txtStartDate) Then
    strWhere = strWhere & "([Date1] >= " & Format(Me.Date1start, conJetDate) & ") AND "
End If

If Not IsNull(Me.txtEndDate) Then 'Less than the next day.
    strWhere = strWhere & "([Date1] < " & Format(Me.Date1End + 1, conJetDate) & ") AND "
End If

Note that if the user wants to search for a single date, he will have to enter this date in both "Start" and "End" fields.

If you want the user to be able to filter only 1 date at a time, you can have 2 text boxes (DateStart and DateEnd) and a listbox or combobox with 2 columns:
Column 1: date field description (visible)
Column 2: date field name (invisible)

 

[fau5tu5]

I would ask, if I may, that you guys, George145, and 3dman, take a quick look at the actual form I attatched to see how it is set up. ( I added the original template "search4" as well as a quick mockup of the desired, and saved them in 2003 in case you werent using 2007 )

The reason I ask that is that the suggestions all seem to be focused on using a generic DATE search using a "Start Date" and "End Date" box with a designated Field.

And not actually code that integrates into the actual form's design, function, and intended interface and use.

a few ways these differ extremely, if I'm not mistaken as such:

Current method: additional, and large numbers of DATE focused fields COULD be added to the TABLE, and only the name of the Field would need to be added to 3-4 places for full functionality.

Suggested method: any additions would require adding 2 more text boxes (each) on the form as well as all the VBA repeated that goes along with them for each additional DATE field on the table.

Current Method: Allows user to select simply "Before" (<#) or "After" (>#) (and if they wanted a between, could do so using two search fields but would not be required)

Suggested Method: Would require a user to input a fictitious future or prior date to achieve an open ended date search, but rather is focused on a "BETWEEN" DATE search.


Current Method allows for even more types of searches with additional and/or future Search FIELD selection without adding text boxes to the form.. Thus maintaining a small and very clean search form.

Suggested method: Simply hard codes any search into a new set of text boxes constantly extending and growing the size of the form.

Current method: Is highly portable and adaptible to other uses and tables/databases, quickly and easily, with simply going through with a quick "Replace/Next" of the TABLE recordsource and then swapping out a Field lists from the new TABLE / Database in the in one location in the VBA, and 2 in the Form Design.

Suggested method: The whole Form might as well be rebuilt as each text box and search funcion is strongly linked to the Table and Database it is with.


The strength of the Current Method goes beyond what I just listed if you take a quick look at it. It is VERY flexible as is, and with DATE functionality would be a SUPERB general search and report generator template to be quickly and easily made functional.

The Goal is to allow for a date search on a variable field (meaning you pick which FIELD you want to search), and variable criteria (meaning you pick "before", "After", "on"), and to Avoid a long and cumbersom list of double text boxes for Date Searches on single dedicated Fields using,"Start Date" and "End Date" only.

 

[George145]

Hello fau5tu5,

Take a look at the attached file, which works as desired.

It seems the problem also was that you used "cboSearchField1.Value" instead of "txtSearchValue1" (where the date should be in the filter string).

I suggest that you also take a look at the other file within the rar file ("search2000.mdb" - created by Allen Browne), which I found some time ago posted in a forum. It is a great sample seacrh form, which i have found very useful for getting ideas on how to create the search forms I use in my programs.

 

[fau5tu5]

THANK YOU!! THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!

I cant believe (of course I can) that was all that was missing... (and incorrect)


LIFESAVER...

And now, I'll be honest, I am intrigued by the "Search2000" you included.

I am going to start taking it apart after I integrate your corrections into the real SeachForm and DB I'm working on for this....

Again... THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!THANK YOU!!

For taking the time to answer my questions... to both you and 3dman..

Any time a stranger spends helping someone is something that speaks great volumes.

THANK YOU!!

 

[George145]

I'm very glad I helped!

Many people have already helped me directly or indirectly and I also try to help as much as I can - in the limited time I can spend. This is what the forums are for anyway!

 

[3dman]

That's great!!!!

I also learnt a few useful things from George145 as well.

 

[fau5tu5]

I was about to add another post (and will )but wanted to ask you guys first since you are both pretty "up to speed" with what I'm trying to achieve.

I would like to add a dropdown (or button/s) between each of the form's search/filters to determine the type of opperation/relationship between them.

So,..
Search /Filter criteria 1. (with field, action, text or date)
......... and..................
Search /Fliter criteria 2. (with field, action, text or date)
........that includes........
Search /Filter criteria 3. (with field, action, text or date)


So for instance...
Search filter 1. results = 5 records including first Name "TOM" (3 x Tom Smith, USA, 2 Tom Jones, MEX)

....... AND.........

Search filter 2. restults= 7 records including last Name "Jones" (2 Tom Jones, USA, 3 Jill Jones USA, 2 Mike Jones, AUS)

(so currently we have an end output of 10 records)

change the operator or relationship to "Includes"

...............Includes..............

Search Criteria 3. Location USA.. = 5 records (3x Tom Jones USA, 2 Jill Jones USA )

 

[George145]

Hi,

Before proceeding to the solution, I would like to make some remarks.

1.
The expression
[First Name] = "Tom" AND [Last Name] = "Jones"
will return ONLY the records with First Name Tom and Last Name Jones, that is 2 records in this case (assuming that 2 {Tom Jones, MEX} and 2 {Tom Jones, USA} refer to the same records and there is a mistake in either MEX or USA).

On the contrary, the expression
[First Name] = "Tom" OR [Last Name] = "Jones"
would indeed return 10 records.

2.
"includes" is not a logical operator. "NOT", "AND", "OR", "XOR" are logical operators. Judging from the desired result, I guess that you meant "AND" in this case.

So, in total, the expression you describe is the following:
([First Name] = "Tom" OR [Last Name] = "Jones") AND [Location]="USA".


An indicative list the Operator ComboBox (let's name OperatorCombo1 the one between search criteria 1 and search criteria 2) might include is:
AND
OR
AND NOT
OR NOT

Now, one important issue you have to think of is the logical operators priority.
As far as I'm concerned,
"NOT" goes first with "Right-to-Left" assigning
"AND" follows with "Left-to-Right" assigning
"OR" is calculated last with "Left-to-Right" assigning also.

I am sure a brief googling will shed more light in this matter if needed.

This means, for example, that in case you didn't include the parentheses in the expression above, it would be calculated as
[First Name] = "Tom" OR [Last Name] = "Jones" AND [Location]="USA"

which in fact means
[First Name] = "Tom" OR ([Last Name] = "Jones" AND [Location]="USA")

producing a different result. You can think of "AND" as a multiplication sign and "OR" as a plus sign, as their arithmetic operator equivalents. Multiplication will be performed first and addition will follow.

In case you would like the calculation to take place with operators' default priority, you have to do the following:

1. Replace

GCriteria = GCriteria & " and " & cboSearchField2.Value & " LIKE '*" & txtSearchValue2 & "*'"

with

GCriteria = GCriteria & " " & ComboOperator1.Value & " " & cboSearchField2.Value & " LIKE '*" & txtSearchValue2 & "*'"

that is, replace every

" and "

with

" " & ComboOperator1.Value & " "

in every line of the second and third condition

You also have to replace the condition:

If Len(cboSearchField2) > 0 And Len(cboSearchOperation2) > 0 And Len(txtSearchValue2) > 0 Then

with

If Len(cboSearchField2) > 0 And Len(cboSearchOperation2) > 0 And Len(txtSearchValue2) > 0 And Len(ComboOperator1) Then

to address the problem of having a second (respectively third) condition without an operator to link it with the previous one.

Now the things get just a little more complicated if you want to alter the priority of the operations, so that they are performed from left to right, irrespectively of the operators' priority. The main concept is that if you have

Condition1 LogicalOperator1 Condition2

you must modify the string as such:

(Condition1 LogicalOperator1 Condition2)

and then

(Condition1 LogicalOperator1 Condition2) LogicalOperator2 Condition3

must be modified to become:

((Condition1 LogicalOperator1 Condition2) LogicalOperator2 Condition3)

and so on.

So, each time you just have to add a parenthesis in the beginning and at the end of the GCriteria string.

Hope this helps,

George

 

[fau5tu5]

Sweet Lord, you have no idea how much this helps... I'm still digesting, but I've been through 3 sets of online tutorials so far, and none has mentioned the left to right or the priority of the operations... You just answered a quandary responsible for a lump on my forehead and a dent in the wall.

And yest the USA/MEX was a mixup on my part,.. you gleamed the essence of my meaning despite my mistake.

I will work with what you have given me an see how far I can get on my own before asking the windfall of questions this possibly brings up/answers.

If you aren't, I will say, you would make a fine teacher. You state things much more rationally and completely than most of the online tutorials I have gone through... and explain them...

Thank you.