Dynamically search multiple fields question

[kigor]

Hello,
I use great form on link:
http://www.access-programmers.co.uk/forums/showthread.php?t=188663

It's make great and fantastic seach. I look way to add addition possiblitiy.
Or better I look way to add time seach.
So to search with enter range of date: start date and end date

But also to made option group what will have two way of filter by
date:
1. list of job of present month (1.4.2011-30.4.2011)
2. list of job of last month (1.3.2011-31.3.2011)

I have long list of work with date and look best way to search it more of "simple" word search.
Any help? Anybody try?

 

[Jeanette Cunningham]

Here are my favorite search screens for dynamic searches:

On this page
http://www.jstreettech.com/cartgenie/pg_developerDownloads.asp
download 'A better record finder'

http://www.allenbrowne.com/AppFindAsUType.html
http://www.allenbrowne.com/ser-62.html

 

[kigor]

Thanks for your replay.
Your linked db have just little possibility. My one have lot more, problem is how to implement it with date criteria

 

[Jeanette Cunningham]

Hope those ideas can help you. Glad you had a look at them.
When you say you have lot more - how does that cause a problem?
Those examples can be changed to cover a lot more fields.
What is about dates that makes it hard?

 

[kigor]

Well I have simple table to search.
It have field about date, job, job comment.
Word search is made for field job and job comment.
And this work perfect.
Then I add new criteria: start date and end date. Both have default value.
With it also "below" list arrive correct.
Problem arrive after after I enter criteria start and end date. Simple double click or simple bottom click not give any data and receive "Run-time error '2185'"
Can ever send db

 

[Jeanette Cunningham]

Probably a good idea to attach the db. Then we can how you are searching and make suggestions.

 

[kigor]

Hello,
find in attach part of my datbase connected with Dynamically search multiple fields.
To original one I add two different data filter.
First one, Scegli le date is to filter jobs between two date, datainizio (start date) and datafine (end date). This work

perfectly.
Second possiblity is to filter by month, Scegli il mese.
Two possiblity: filter by scorso (job of last month) and attuale (this month).
As my knowledge of VBA coding is poor I am looking around and find code below. I made litte change but it give wrong answer.
If in option group select Scorso, so value 1, I receive all job of this year (1.1.2011-today) and not just job between

1.3.2011-30.3.2011.
Also wrong "answer" for Attule, I receive nothing, but one job is enter and have date 1.4.2011.
Can somebody help me?


Private Sub Frame97_AfterUpdate()
'
' Frame97 is the name of the Option Group control (Default Value = 0).
'
' It contains 3 Option Buttons:
'
' - Option_AllDates (Option Value = 0)
' - Option_LastMonth (Option Value = 1)
' - Option_CurrentMonth (Option Value = 2)
'
Dim dteStart As Date
Dim dteEnd As Date
Dim dteLMStart As Date
Dim dteLMEnd As Date

dteStart = DateSerial(Year(Now), Month(Now), 1)
dteEnd = DateAdd("m", 1, dteStart)
dteEnd = DateAdd("s", -1, dteStart)
dteLMStart = DateAdd("m", -1, dteStart)
dteLMEnd = DateAdd("s", -1, dteStart)
Select Case Me.Frame97.Value
Case 0 ' All dates
Me.SearchResults.Requery
Me.SearchResults = ""
Me.SearchFor.SetFocus
Case 1 ' Last month
Me.SearchResults.Requery
Me.SearchResults = "[data] Between #" & Format(dteLMStart, "dd/mm/yyyy") & "# And #" & Format(dteLMEnd,

"dd/mm/yyyy") & "# "
Me.SearchFor.SetFocus
Case 2 ' Current month
Me.SearchResults.Requery
Me.SearchResults = "[data] Between #" & Format(dteStart, "dd/mm/yyyy") & "# And #" & Format(dteEnd, "dd/mm/yyyy")

& "# "
Me.SearchFor.SetFocus
End Select
Me.FilterOn = True
End Sub

 

[Jeanette Cunningham]

Thanks for your patience - I have been away from the forums over Easter.
I looked at your database.
When you put a number or a date in the search box,

InStr(Len(SrchText), SrchText, " ", vbTextCompare)

evaluates to false and your code skips down to the exit sub line.

I use the JStreet Tech example I posted in my previous answer.
It works and I don't have time to spend working out a different way to do a search all fields.

I encourage you to download and use the JStreet Tech sample as the quickest and easiest way for you to get your form to correctly search on all fields. It is tried and tested - so why try to invent your own way? - unless you have plenty of spare time and nothing more important to do?

 

[ChrisO]

Kigor.

Your computer appears to be set to Italian (Switzerland) regional settings.

Try this: -

Create a public const in a standard module…
Public Const conDateFormat As String = "mm\/dd\/yyyy"

Anywhere you have a line of code like this…
Format(dteLMStart, "dd/mm/yyyy")

Replace it with this…
Format(dteLMStart, conDateFormat)

Please make a backup first.

Chris.

 

[Jeanette Cunningham]

Looking at searching by date.
You can set the format property of the 2 date text boxes to short date.

Again, the sample search database by Allen Browne has the code you can copy and use to search by date range. It also shows how to search by a date range where the dates can include dates as well as times.
Have you tried any of the code from the sample databases I recommended?

These sample database show tried and tested ways to search - why try to invent your own ways when there are such great sample databases to learn from.