Dynamic Multi Search (1 Viewer)

Infinite

More left to learn.
Local time
Yesterday, 19:06
Joined
Mar 16, 2015
Messages
402
http://www.access-programmers.co.uk/forums/showthread.php?t=188663

That post has the ability to search for things in a list box, and it is VERY helpful, now, what I want to do (or know of its possible) is to have it search for anything in it. Like, I have items named 1Item, and 2Item. I want to search for Item, and get 1Item, and 2 Item. Is it possible? And if so, how? Thanks!
 

Mihail

Registered User.
Local time
Today, 04:06
Joined
Jan 22, 2011
Messages
2,373
Just an idea (I am not sure if you like to use OR or AND in your search):
Put the items in a table.
Use the previous code in order to automatically filter the list (iterative).
At the end clean the table.

Hope this help you.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 02:06
Joined
Feb 19, 2013
Messages
16,553
set your listbox to multiselect (in the properties other tab). Note this is not the same as multivalue. Then google 'access multiselect listbox' to find out ways of coding to set your criteria

here is an example

http://allenbrowne.com/ser-50.html
 

Infinite

More left to learn.
Local time
Yesterday, 19:06
Joined
Mar 16, 2015
Messages
402
set your listbox to multiselect (in the properties other tab).

I see the multiselect options, but, I have 2 options, one is Simple, and the other is Extended. Which do I choose?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 02:06
Joined
Feb 19, 2013
Messages
16,553
simple means you can click on many different choices, one at a time

extended provides the option of say clicking the 2nd option, then holding down the shift key and clicking on the 5th option to select all options in between - works much the same way as the windows directory

so it really depends on what you want for your users
 

Infinite

More left to learn.
Local time
Yesterday, 19:06
Joined
Mar 16, 2015
Messages
402
Ok CJ, I have looked and done some things of what you said, and I think, your not understanding me. With Multi Select, I and select multiple items, and that is useless for opening them seeing as it errors. I Dont want that, I just want Multi search :) Just to do what I said. I have shows that would go like [City name]Festival. But I want to search for any show that has the words Festival and get all of those. That is JUST a example.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 02:06
Joined
Feb 19, 2013
Messages
16,553
But I want to search for any show that has the words Festival and get all of those
in that case you want as your criteria something like

WHERE showname like "*" & forms!myform!searchtxt & "*"
 

Infinite

More left to learn.
Local time
Yesterday, 19:06
Joined
Mar 16, 2015
Messages
402
I keep doing

Code:
WHERE Show Name like "*" & [Forms]![frmEvents]![txtSearchS] & "*"
under neath were I had Like "*" & [Forms]![frmEvents]![txtSearchS] & "*" and I was able to search and it worked.

But I go the the form, refresh, do a search, it brings up no results, and when I go back into the query there is a new field named "WHERE Show Name"
with a criteria of Like "*" & [Forms]![frmEvents]![txtSearchS] & "*"
Am I placing it on the wrong place?
 

CJ_London

Super Moderator
Staff member
Local time
Today, 02:06
Joined
Feb 19, 2013
Messages
16,553
show name should be in square brackets - it has a space in the name and you should know by now that this is bad practice and always requires square brackets (same for reserved words and non alpha numeric characters). i.e.

[show name]

Am I placing it on the wrong place?
I don't know - are you?

you talk about a query and refreshing the form - is the query the recordsource to your form?

With queries, everybody generally communicates using SQL. With this comment

when I go back into the query there is a new field named "WHERE Show Name"
implies you are posting it into the query grid.

I cannot guess what you are actually doing - and providing only some of the code doesn't help - post the full sql or a screenshot of the query grid.

And if you struggle with converting pseudo code to use the real names of fields and tables, provide the real names.
 

Infinite

More left to learn.
Local time
Yesterday, 19:06
Joined
Mar 16, 2015
Messages
402
Ok, picture one is pasting the code, picture 2 is me closing the qryAdminEvents and then refreshing frmEvents. And picture 3 is the qryAdminEvents AFTER I have refreshed it.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 02:06
Joined
Feb 19, 2013
Messages
16,553
in picture 1, remove the words 'where show name' from the criteria line

or in picture 3, put showname (no spaces) instead of 'where show name', the table row should then be populated with tblshows

Once you have it working, go to the SQL view and see how your query grid looks like in SQL code.

Do this for all your queries and any new ones you create and you will start to understand how the query grid builds the sql code. Then when responders send yo sql code, you will know what this looks like in the query grid
 

Infinite

More left to learn.
Local time
Yesterday, 19:06
Joined
Mar 16, 2015
Messages
402
Ok, I was looking at what you said, and I wasn't sure why you said that, but now im thinking, that if I do what you say, I will have exactly what I have already. I want to put what im going to say next in caps locks, but then ill like stupid. I know how to make a text box, that sorts the list box when I type stuff. I can do that just fine.


Didnt you read what I wanted? If not, here is ANOTHER example. Items 123 and items 321. I want you to give me some code, link, example date base that find me items 123 and 321 if I type 2. Read it again, I can search for the first name of a item easly, but I want to search for the rest of the name, like, with item 123 and 321, 1 and 3 are the "first" name, and 2 and 2 is the "2nd" name. If you understand (and you have been) then something is wrong, because the text your trying to have my input is the same has I already have.
 

Mihail

Registered User.
Local time
Today, 04:06
Joined
Jan 22, 2011
Messages
2,373
I always use a external VBA function, of type boolean, for tasks like this.
The function can be named IncludeRecord as example and should return True if all the conditions are satisfied.
It is slower than SQL but, in my opinion, infinite flexible.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 02:06
Joined
Feb 19, 2013
Messages
16,553
Sorry, clear as mud

to clarify what you want

first post
Like, I have items named 1Item, and 2Item. I want to search for Item, and get 1Item, and 2 Item.

fourth post
I have shows that would go like [City name]Festival. But I want to search for any show that has the words Festival and get all of those

last post
with item 123 and 321, 1 and 3 are the "first" name, and 2 and 2 is the "2nd" name
so is this separate columns?
firstname..middlename..lastname
1.............2...................3
3.............1...................2
4.............5...................6

if not, provide a decent example of the data you have, what you want to search for and the required result
 

Infinite

More left to learn.
Local time
Yesterday, 19:06
Joined
Mar 16, 2015
Messages
402
No, I guess your not understand me...My fault mostly. I have the name of JohnSmith and FrankSmith. Those names are in field Name. There in my query, so there in my list box. I have the search, if I search for Smith, I want both of them to show up. JohnSmith isnt 2 names, its ONE name, with the first name and last name in one name. So, do you under stand yet? Trying to clean up the mud...
 

CJ_London

Super Moderator
Staff member
Local time
Today, 02:06
Joined
Feb 19, 2013
Messages
16,553
So now we have moved from show names to peoples names. Stop trying to hide what you actually want to do.

From your latest post to make sure I understand

I have the name of JohnSmith and FrankSmith. Those names are in field Name.
means

you have a field called name in a table or query and this has values such as JohnSmith, FrankSmith, HarryJames, FredBrown.

if I search for Smith, I want both of them to show up
means

you want to search for all records that have 'smith' in it and return the records with JohnSmith and FrankSmith.

If that is the case the suggestion way back would work (modified from showname to name)

WHERE Name like "*" & [Forms]![frmEvents]![txtSearchS] & "*"

If it is not the case, provide some real data and your sql for the query you are using at the moment.
 

Mihail

Registered User.
Local time
Today, 04:06
Joined
Jan 22, 2011
Messages
2,373
@CJ
Is this query Case Sensitive or not ?
Thank you.
 

Infinite

More left to learn.
Local time
Yesterday, 19:06
Joined
Mar 16, 2015
Messages
402
Ok, here is the query SQL.


Code:
SELECT tblItems.Model, tblItems.Price, tblItems.OnlineShow, tblItems.Category, tblItems.Status, tblItems.FitPerSheet, tblItems.Wholesale, tblItemSizes.ModelWidth, tblItemSizes.ModelHeigth, tblItemSizes.ModelArea, tblItemSizes.ModelPerimiter, tblItems.ModelPicture, tblItemCutOutLength.DetailInch, tblItemCutOutLength.LogoInch, tblItemCutOutLength.TriggerInch, tblItems.[2ndModelNames], tblItems.[3rdModelNames], tblItems.[4thModelNames], tblItems.[5thModelNames], tblItemCutOutLength.TimeToCutOut, tblItems.ModeIID
FROM (tblItems LEFT JOIN tblItemSizes ON tblItems.ModeIID = tblItemSizes.Model) LEFT JOIN tblItemCutOutLength ON tblItems.ModeIID = tblItemCutOutLength.Model
WHERE (((tblItems.Model) Like [Forms]![frmItems]![txtSearchI] & "*")) OR (((tblItems.Category) Like [Forms]![frmItems]![txtSearchI] & "*")) OR (((tblItems.Status) Like [Forms]![frmItems]![txtSearchI] & "*")) OR (((tblItems.[2ndModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*")) OR (((tblItems.[3rdModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*")) OR (((tblItems.[4thModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*")) OR (((tblItems.[5thModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*")) OR (((tblItems.OnlineShow) Like [Forms]![frmItems]![txtSearchI] & "*"))
ORDER BY tblItems.Category DESC;

But I would rather know how to do it, so I can do it for more query's and such. And just to have that knowledge.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 02:06
Joined
Feb 19, 2013
Messages
16,553
@infinite

where you have for example

...(((tblItems.Model) Like [Forms]![frmItems]![txtSearchI] & "*"))...

per post way back when and my last post, you should have

...(((tblItems.Model) Like "*" & [Forms]![frmItems]![txtSearchI] & "*"))...



@mihail - No! Access sql is not case sensitive unless you make a binary comparison e.g. instr(tblItems.Model,[Forms]![frmItems]![txtSearchI],0) or strcomp(tblItems.Model,[Forms]![frmItems]![txtSearchI],0) where 0 is vbBinaryCompare
 

Users who are viewing this thread

Top Bottom