Multi field search returning nil value (1 Viewer)

vegemite

Registered User.
Local time
Today, 22:42
Joined
Aug 5, 2019
Messages
64
Hey experts! I have a form that I want the user to be able to search for relevant jobs with a number of fields. I want to use unbound text boxes for project name and client then (at this stage while I am testing) get a query to return relevant records.
I've used wildcards in each of two fields with these criteria - Like "*" & [Forms]![MyFormName]![Thenameoftheunboundbox] & "*"
When I run the associated query with nothing in my search boxes I get all records, the minute I enter something in the form... even the letter "a" I get no records?

This was supposed to only be the first stage of this, ideally, I might have some drop-down boxes to help the user refine the records and show a subform then go to the selected record.... I cant even nail this first step!
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:42
Joined
May 21, 2018
Messages
8,463
Do not use textboxes use comboboxes set to limit to list. No need to enter things that do not exist. This should help as an example using both a pop up and filters on the form. Use a form filter, do not try to do this in a query.
 

Attachments

  • MajP SimpleSearch V2.zip
    89.7 KB · Views: 113

vegemite

Registered User.
Local time
Today, 22:42
Joined
Aug 5, 2019
Messages
64
Do not use textboxes use comboboxes set to limit to list. No need to enter things that do not exist. This should help as an example using both a pop up and filters on the form. Use a form filter, do not try to do this in a query.

Thanks for that! Can I still use wildcards though as I expect users wont always know the full name and the dataset is very big
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:42
Joined
May 21, 2018
Messages
8,463
You may also find this approach interesting. This uses the built in filter to do some powerful filtering.
 

Attachments

  • FormFilterControl.zip
    38.2 KB · Views: 118

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:42
Joined
May 21, 2018
Messages
8,463
Take a look at the Find as you type options in this. May make more sense for very big files with partial knowledge.
 

Attachments

  • FuzzyFind5.zip
    605.4 KB · Views: 113

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:42
Joined
May 21, 2018
Messages
8,463
Yes you could use wildcards and textboxes
Example
Code:
  If Not Trim(Me.cmboCountry & " ") = "" Then
    strCountry = "Country = '" & Me.cmboCountry & "'" & AndOr
  End If
Change to
Code:
  If Not Trim(Me.txtCountry & " ") = "" Then
    strCountry = "Country Like '*" & Me.txtCountry & "*'" & AndOr
  End If
 

vegemite

Registered User.
Local time
Today, 22:42
Joined
Aug 5, 2019
Messages
64
Take a look at the Find as you type options in this. May make more sense for very big files with partial knowledge.
Thats nice!!! Now I just have to work out how to add multiple fields so they can sesarh on a number. Thanks! I like that
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:42
Joined
May 21, 2018
Messages
8,463
just remember formats for searches
Numbers = 1
string = 'Some String'
dates = #mm/dd/yyyy# 'must be in that format regardless of regional settings.
 

vegemite

Registered User.
Local time
Today, 22:42
Joined
Aug 5, 2019
Messages
64
just remember formats for searches
Numbers = 1
string = 'Some String'
dates = #mm/dd/yyyy# 'must be in that format regardless of regional settings.
Oops sorry meant a number of fields. So they can choose either project name or client (mind you client could be a drop down then limit to the project names for that client) and project title could be that nice "fuzzy" ex you gave
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:42
Joined
May 21, 2018
Messages
8,463
Now I just have to work out how to add multiple fields so they can sesarh on a number.
The first example showed multiple fields. You can also find as you type within a combo.
It sounds like you also want to cascade your combos? (limit the choices based on the first combo)

Can you really clearly explain all your controls you want to use, how they work together, and features they will need.
 

vegemite

Registered User.
Local time
Today, 22:42
Joined
Aug 5, 2019
Messages
64
The first example showed multiple fields. You can also find as you type within a combo.
It sounds like you also want to cascade your combos? (limit the choices based on the first combo)

Can you really clearly explain all your controls you want to use, how they work together, and features they will need.
Thanks @MajP .
I am anticipating a few ways the user might like to search for jobs. Client Name, Local Government Area (LGA) or Project Title.
A lot of the times the client is the LGA so I expect they would do one of the other of client name or LGA then see a list of Projects (however some times there is a consultant who works across many LGAs that might be the client - hence the need to give both options) ... OR
alternatively, if they don't know the client or LGA they might want to search for a partial project title.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:42
Joined
May 21, 2018
Messages
8,463
So in the first example I showed multiple controls chained together it was like 6 fields. So the filter would be like

ClientName = 'john smith' or LGA = 123 'those could be likes and will handle 1 or both
or you could then use the FAYT on project title

The FAYT uses a class module so really does not need any code changes. Can you demo out your form and post a screen shot/
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:42
Joined
May 21, 2018
Messages
8,463
I tried to pair it down to what you described. Searching by name or project where you might have partial name information
Code:
Public FAYT_List As FindAsYouTypeListBox
Private SearchField As String
Private Sub Form_Load()
  Set FAYT_List = New FindAsYouTypeListBox
  FAYT_List.InitializeList Me.lstAllRecords, Me.txtSearch, "Full_Name", AnywhereInString
End Sub

Private Sub frmFields_AfterUpdate()
  Select Case frmFields
    Case 1
     SearchField = "Full_Name"
    Case 2
     SearchField = "ProjectName"
  End Select
  Me.txtSearch = ""
  FAYT_List.unFilterList
  FAYT_List.FieldToSearch = SearchField
  FAYT_List.SortList (SearchField)
End Sub
1. You need a form level variable for the class
Public FAYT_List As FindAsYouTypeListBox
2. In the on load you need to intantiate
Set FAYT_List = New FindAsYouTypeListBox
FAYT_List.InitializeList Me.lstAllRecords, Me.txtSearch, "Full_Name", AnywhereInString
The arguments are your listbox, your textbox, the field to search, and how you want to search.

And that is it. The miracle of custom classes

FYI there are 10k records and as you can see you can find anything pretty instantaneously.
 

Attachments

  • SearchClientProj.zip
    586.7 KB · Views: 109

vegemite

Registered User.
Local time
Today, 22:42
Joined
Aug 5, 2019
Messages
64
I just broke something. Oops. I was going to send a pared down version. Yes I totally want to go with your frmSearchFAYT. The verison I have of yours lets me see the macro but not the form design to ensure I have the right naming conventions.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:42
Joined
May 21, 2018
Messages
8,463
I just realized that is a listbox demo and you may want to do it with a form instead. Or you can do it with a listbox and once you find the correct record double click to open a form to the correct record.
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:42
Joined
May 21, 2018
Messages
8,463
Here is a form version with two fields to choose from (or search any field). 10k and still can find anything you wan pretty immediate with partial information.
 

Attachments

  • FindAsYouTypeForm.zip
    664.5 KB · Views: 108

GinaWhipp

AWF VIP
Local time
Today, 07:42
Joined
Jun 21, 2011
Messages
5,901
Hmm, occurs to me this might be helpful...
 

MajP

You've got your good things, and you've got mine.
Local time
Today, 07:42
Joined
May 21, 2018
Messages
8,463
@vegemite
I find sometimes it can get confusing when other persons post additional examples without any context.
1. Post #17 is the exact same approach as I did in post #2 so there is nothing new to see, but the explanation may be more thorough than mine. My code is a little more robust with the ability to make it an AND or OR query.
2. In post #18 @isladogs example the approach is different than any I posted. You asked about modifying the query, and he is modifying the whole sql string of the recordsource. I am guessing he would agree with me that you either modify the recordsource like he did or the filter like I do, but not building a query with control references inside. IMO putting control references inside the query works for something simple, but once you get past one control it gets very cumbersome. Far easier to debug and code in vba.
 

isladogs

MVP / VIP
Local time
Today, 11:42
Joined
Jan 14, 2017
Messages
18,186
2. In post #18 @isladogs example the approach is different than any I posted. You asked about modifying the query, and he is modifying the whole sql string of the recordsource. I am guessing he would agree with me that you either modify the recordsource like he did or the filter like I do, but not building a query with control references inside. IMO putting control references inside the query works for something simple, but once you get past one control it gets very cumbersome. Far easier to debug and code in vba.
Yes I agree completely. For complex filters, my preference is for building the sql string for the record source so I can build up the code one step at a time. I tend to avoid using form control references in queries.
 

Users who are viewing this thread

Top Bottom