Dynamic Multi Search (1 Viewer)

Infinite

More left to learn.
Local time
Yesterday, 20:30
Joined
Mar 16, 2015
Messages
402
Well CJ, That has been something that has annoyed me for quit some time. I would like to thank you VERY much for helping me and answering my question, being patient, correcting my wrongs. Thank you much!

Stop trying to hide what you actually want to do.
:eek: I am sorry I was confusing, truly, I am, that was NOT what I was at all trying to do, I was just trying to give you examples. Sorry again if what I have said has confused you. Did not mean for it to look like I was hiding something from you. Thanks once again!
 

Mihail

Registered User.
Local time
Today, 06:30
Joined
Jan 22, 2011
Messages
2,373
@Infinite
What about this ?

In attachment you have a DB.
Search the word "Smith"
 

Attachments

  • SplitNames.mdb
    260 KB · Views: 40

CJ_London

Super Moderator
Staff member
Local time
Today, 04:30
Joined
Feb 19, 2013
Messages
14,689
We got there in the end.
Did not mean for it to look like I was hiding something from you.
there are real benefits in providing things like real field and table names, the full sql you are using, sample data of what you have and what you want to see from the query, and use of correct terminology (is a 'name' a field name or a control name for example) etc

Benefits are
1. one or more of these tell a much clearer story
2. it sometimes uncovers other things like use of reserved words, spaces and non alpha numeric characters in names which can have unexpected results and incorrect solutions are proposed
3. a vague question gets suggestions, not answers
4. you will get a much quicker answer
5. and most importantly you will often be able to simply copy and paste the answer provided

This thread took twenty two posts and five days to answer for a very basic requirement . If you had just provided a copy of your sql, at the beginning, it would have been answered in hours rather than days and probably one post. So an extra couple of minutes on your original thread would have save everyone several hours.

The other thing to bear in mind is that without a clearer explanation

1. many responders will think 'I don't understand the question and I don't have the time to clarify - so I won't bother'
2. as you start to get a reputation for poorly worded questions and not reading the solutions provided properly (this is the second time I have provided you with a correct solution early on and the thread has continued with many more posts and many more days because you hadn't read it properly), responders won't even look, they'll just ignore you
 

Infinite

More left to learn.
Local time
Yesterday, 20:30
Joined
Mar 16, 2015
Messages
402
I believe my problem is, I keep thinking that you guys are reading this from my prospective. If I was to read it myself, I would understand it, but, I dont think about someone else reading it, and not understanding it. I need to get better at proof reading, and because im not very good at it, people dont understand my questions (not there fault, all of its mine) so it takes, 22 post and a couple of days. And from now on, If I can remember it, I will post the query SQL for query's, and the data base I am working on for any questions I have to do with forms or tables. Thanks once again!
 

Infinite

More left to learn.
Local time
Yesterday, 20:30
Joined
Mar 16, 2015
Messages
402
I have a quick question, when using the search code you gave me, something is not working. I have the items in my form, and I have it so when I double click, it opens up another form to edit it. But, if I have John Doe Smith and I search for Doe, and double click John Doe Smith, it opens up a blank record. But, if I search for John and double click it, it opens up John Doe Smiths record. Any reason why it would be doing this?


FYI: It only does that with a record that has more then 3 names to it. So if I did the same thing with only John Smith, it would work searching for Smith.
 

CJ_London

Super Moderator
Staff member
Local time
Today, 04:30
Joined
Feb 19, 2013
Messages
14,689
none at all - would need to see sample data, the sql for your form and the code that opens the other form and an explanation of which field is doubleclicked. If it is a combo, would also need to see the combo properties - rowsource, bound column, columncount, column widths
 

Infinite

More left to learn.
Local time
Yesterday, 20:30
Joined
Mar 16, 2015
Messages
402
Code:
SELECT tblItems.Model, tblItems.Price, tblItems.OnlineShow, tblItems.Category, tblStatus.Status AS StatusID, 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 tblStatus INNER JOIN ((tblItems LEFT JOIN tblItemSizes ON tblItems.ModeIID = tblItemSizes.Model) LEFT JOIN tblItemCutOutLength ON tblItems.ModeIID = tblItemCutOutLength.Model) ON tblStatus.StatusID = tblItems.Status
WHERE (((tblItems.Model) Like [Forms]![frmItems]![txtSearchI] & "*") AND ((tblStatus.Status)=[Forms]![frmItems]![Frame16])) OR (((tblItems.Model) Like [Forms]![frmItems]![txtSearchI] & "*") AND (([Forms]![frmItems]![Frame16]) Is Null)) OR (((tblItems.Category) Like [Forms]![frmItems]![txtSearchI] & "*") AND ((tblStatus.Status)=[Forms]![frmItems]![Frame16])) OR (((tblItems.Category) Like [Forms]![frmItems]![txtSearchI] & "*") AND (([Forms]![frmItems]![Frame16]) Is Null)) OR (((tblStatus.Status)=[Forms]![frmItems]![Frame16]) AND ((tblItems.Status) Like [Forms]![frmItems]![txtSearchI] & "*")) OR (((tblItems.Status) Like [Forms]![frmItems]![txtSearchI] & "*") AND (([Forms]![frmItems]![Frame16]) Is Null)) OR (((tblStatus.Status)=[Forms]![frmItems]![Frame16]) AND ((tblItems.[2ndModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*")) OR (((tblItems.[2ndModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*") AND (([Forms]![frmItems]![Frame16]) Is Null)) OR (((tblStatus.Status)=[Forms]![frmItems]![Frame16]) AND ((tblItems.[3rdModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*")) OR (((tblItems.[3rdModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*") AND (([Forms]![frmItems]![Frame16]) Is Null)) OR (((tblStatus.Status)=[Forms]![frmItems]![Frame16]) AND ((tblItems.[4thModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*")) OR (((tblItems.[4thModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*") AND (([Forms]![frmItems]![Frame16]) Is Null)) OR (((tblStatus.Status)=[Forms]![frmItems]![Frame16]) AND ((tblItems.[5thModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*")) OR (((tblItems.[5thModelNames]) Like [Forms]![frmItems]![txtSearchI] & "*") AND (([Forms]![frmItems]![Frame16]) Is Null)) OR (((tblItems.OnlineShow) Like [Forms]![frmItems]![txtSearchI] & "*") AND ((tblStatus.Status)=[Forms]![frmItems]![Frame16])) OR (((tblItems.OnlineShow) Like [Forms]![frmItems]![txtSearchI] & "*") AND (([Forms]![frmItems]![Frame16]) Is Null))
ORDER BY tblItems.Category DESC;

That is the SQL for it. If you need the data base, just say the word.
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 04:30
Joined
Feb 19, 2013
Messages
14,689
OK - just need the items in red.

would need to see sample data, the sql for your form and the code that opens the other form and an explanation of which field is doubleclicked.

if you had used real names rather than 'John Smith', I might have been able to have worked it out but I cannot relate this name to 'model', 'status' etc.

I don't want another 22 posts, either provide the info as requested or start a new thread - and I don't need the db
 

Infinite

More left to learn.
Local time
Yesterday, 20:30
Joined
Mar 16, 2015
Messages
402
The code that opens it is a macro. The macro is in the picture.

Just look at the pics, should explain what you want.


sample data
Not sure what you mean by that, sorry :(
 
Last edited:

CJ_London

Super Moderator
Staff member
Local time
Today, 04:30
Joined
Feb 19, 2013
Messages
14,689
I think your problem is to do with normalisation and I'm guessing you are using lookups in your table design which could be causing the problem.

I think you would be better off starting a new thread. Sorry I can't help any further

good luck with your project
 

Users who are viewing this thread

Top Bottom