|
Subject:
|
Search tool.
|
|
Posted By:
|
Svenni
|
Post Date:
|
12/14/2006 3:19:14 AM
|
Hey there, I dont know if this has been asked about befor but here goes.
I was wondering if there was by any chance a program that works like a search function for Office access, My boss has been nagging me to make one and i'm kind of out of ideas.
I would appriciate all the help I can get :)
|
|
Reply By:
|
leehambly
|
Reply Date:
|
12/14/2006 6:38:56 AM
|
To search the entire Access db? Or just a table/query within a db?
Both are possible, but not sure what you are after? Kinda depends on your database really.
|
|
Reply By:
|
Svenni
|
Reply Date:
|
12/14/2006 7:16:03 AM
|
to search within a table/query for keywords :)
or simply "BSI" under "projects"
|
|
Reply By:
|
leehambly
|
Reply Date:
|
12/14/2006 8:21:05 AM
|
Svenni,
This is just my approach, I hope it is helpful, sorry to be blunt if I am!
Without trying to teach you to suck eggs, there are a myriad of ways to do a "search", it totally depends on the development you already have and also by what you consider to be a search (this is defined by your users, and you: searching is one thing, doing something after the search has occurred is an entirely different thing). This is a fairly high-level approach, I hope you understand... the work needed to provide what you need may be small, stolen, gifted, large, cumbersome etc... regardless of the source and its complexity: it NEEDS to be useful to the user. So some thought about your data, and how the user interacts with it has to occur.
EG:
Dependant on how you display your data, you could use a filter. Is the field, you are looking for data in, a text field, or is a referenced (lookup) field? Personally, if it is a lookup, which I think it may be (from your Field heading) then it could be preferable to provide a combobox of the Projects in your db, and then filter the data based on the selection - the main benefit of this is that your user (your Boss!) isnt searching for things that don't exist.
Of course, this may not be suitable, but it just gives you an example of the different angles to approach the development from... it should be user requirement driven.
You may find a generic find function out there, or you could write one... to iterate through all your tables and identify any instances of the search string in any of the fields in the table but I doubt it will be of any particular use without a considerable piece of code to ensure the right actions are then taken to put the "found record" into a useful state, ie: viewable via the correct form for the context of the data required, which may be unknown, to the db anyway!
So, frankly my suggestion would be to ask your Boss... which form (s)he would like the search on, and then which fields in the form. Then you need to look at the datatype of the fields required and decide what is the best approach... either filtering or finding. Personally, I would then build individual routines for each type of filter or find... you may be able to group some of these into generic ones, but often the extra time spent in understanding the commutivity of the methods is more than "just doing it!"
To be totally honest with you, this is development.. making these decisions based on your user requirements.
Hope this helps? Happy to assist more if needed...
Lee
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/14/2006 8:22:05 AM
|
Create a query and pass parameters to it, then display the results in a continuous form, and give the user the option to open the record.
Pass parameters by creating your query, then adding criteria to the appropriate fields like this:
Criteria:
Like "*" & [Type the phrase of letters you are looking for:] & "*"
This will pop up a parameter dialog box, and pass the string and search the field you indicate.
This works on one field at a time. If you want to do all fields in a table, let me know and I will do the button code.
HTH
mmcdonal
|
|
Reply By:
|
Svenni
|
Reply Date:
|
12/14/2006 8:26:00 AM
|
hehe, thanks for all your help.
My boss decided to cancel my "search" for a "search" function, and put me on another assignment.
So again, thanks for all the trouble :P
|
|
Reply By:
|
leehambly
|
Reply Date:
|
12/14/2006 8:40:17 AM
|
Result!
|
|
Reply By:
|
Svenni
|
Reply Date:
|
12/14/2006 11:44:30 AM
|
HARRR, despite my boss's decicion I myself want to finish this :P
so far
Expr1: "*" & [Type the phrase of letters you are looking for:] & "*"
int he first Field I have about 20 tables I want it to search through, any help :P ?
|
|
Reply By:
|
mmcdonal
|
Reply Date:
|
12/14/2006 1:12:36 PM
|
This wouldn't be an expression. You would make this the criteria you want to search for in a named field. Put it on the line that says "Criteria" in the designer.
It must have this syntax:
Like "*" & [Type the phrase of letters you are looking for:] & "*"
If you want to search the whole database, you can do that with code and an input box.
The thing is, why would you want to search for a single string in 20 tables? How denormalized is this database? Are they different iterations of the same data? If so, string them together and then search them.
mmcdonal
|
|
Reply By:
|
Svenni
|
Reply Date:
|
12/15/2006 3:18:17 AM
|
hmm, this database consists of about 20 tables all catagorized the same way, so basicly by searching in the "Projectnumber VS" colum I can search through the whole database for the "Project number"
numbers are
"VS000701"
"VS000702"
etc..
so, 20 tables that contain Project numbers and the column that contains them are all named the same in each table:)
|
|
Reply By:
|
leehambly
|
Reply Date:
|
12/15/2006 4:55:49 AM
|
Svenni,
Dunno where to start, without re-designing your db, you are going to struggle.
You need a bit of code which will cycle through the 20 tables. You then need a bit of code to search for your text string in the given field, or possibly another piece of code which cycles through the fields in the tables and then a piece of code that does the actual searching.
Q: Given that you have 20 tables, what do you want to happen when it finds a match? Does each table have its own form? Would you expect a different action from one table to the next?
Q: Why have you got 20 tables? If we could sort that out, we should be able to provide a fairly simple solution, but I reckon this is a far bigger issue.
I'm gonna suggest using the Ctrl-F shortcut when in the column on the table you want to search, this will give you a "Find" dialog box. Don't take it the wrong way if you already know about it, just realised I hadn't mentioned it already!
|
|
Reply By:
|
Svenni
|
Reply Date:
|
12/15/2006 5:14:19 AM
|
hmmm, I know this database is flawed in it's way :P
We are a construction company and we have diffrent tables for diffrent towns and projects conserning those towns :)
But if you could make something for me that searches through just one table it would be wonderfull :)
"the people who work here are to lazy to actualy open the table and lick ctrl + f" :P
|
|
Reply By:
|
leehambly
|
Reply Date:
|
12/15/2006 5:39:06 AM
|
Sorry to be a bit piecemeal on this:
1. So, given that your tables have a project number in them, the data is already identifyable by Project number, so only the town to worry about.
2. If you add the town to your table, could you then union all the tables together into one big table? If so, do it.
3. Now you have one big table with all your data on, you would obviously need to change whatever queries you already have set up and then add in the town field to ensure that the same data is still identified in your queries... this is all besides the point, but I would feel bad if I didnt mention it!
4. Cool, now we can search your data table for your search string, and we don't have to worry about what action to take at the end of the search, since there is only one table.
5. Build a query, use your table and add the fields you want to see in your results, you dont have to add all of them.
6. Add in the field you want to search and in the Criteria "area?" in the query, type:
Like "*" & [Type the phrase of letters you are looking for:] & "*"
7. Now run the query... you should get a dialog box with the text "Type the phrase of letters you are looking for:" and a space for you to enter the phrase... enter something you know will be in the field, for testing.
8. That should sort you out. You can do this without adding all the tables together, as MMcDonal mentioned. Tell your users to use the query whenever they want to find a specific project, town, name, etc...
Hope it helps, but you really need to sort the data out too.
Lee
|
|
Reply By:
|
Svenni
|
Reply Date:
|
12/15/2006 5:57:11 AM
|
hmmmm, even is I use
"*" & [Type the phrase of letters you are looking for:] & "*"
too search inside just one table all it gives me is an empty colum :/
Field: Fieldname
Table: Tablename
Sort: Ascending
Show: ^^ ofc
Criteria:"*" & [Type the phrase of letters you are looking for:] & "*"
then I have 2 other colums just diplaying other info for the search outcome :S
any thoughs ?
|
|
Reply By:
|
Svenni
|
Reply Date:
|
12/15/2006 5:59:45 AM
|
hmmm, seems like if I just type in the projects name into "Criteria" field it findsit right away, but not with
"*" & [Type the phrase of letters you are looking for:] & "*"
|
|
Reply By:
|
Svenni
|
Reply Date:
|
12/15/2006 6:03:59 AM
|
fixed it ^^
[Type the phrase of letters you are looking for:]
just this works wonders :P
|
|
Reply By:
|
leehambly
|
Reply Date:
|
12/15/2006 6:08:52 AM
|
It is not:
"*" & [Type the phrase of letters you are looking for:] & "*"
it is:
Like "*" & [Type the phrase of letters you are looking for:] & "*"
Using just:
[Type the phrase of letters you are looking for:]
will return records with the entire field matched, whereas with Like "*"" etc you will get any records with a partial match.
Up to you which is more suitable.
Lee
|
|
Reply By:
|
Svenni
|
Reply Date:
|
12/15/2006 6:16:34 AM
|
arf, ok last question :P
Is it possible to search one field with [Type the phrase of letters you are looking for:]
and if it is "ok"'d blank it will ask for the next one :O
|
|
Reply By:
|
leehambly
|
Reply Date:
|
12/15/2006 6:22:47 AM
|
what do you mean by "ok'd"?
|
|
Reply By:
|
Svenni
|
Reply Date:
|
12/15/2006 6:25:54 AM
|
hehe, I was reading over what I wrote and i'm gona try to epxlain this a litle better :P
so, first I prompt for a word to search for in "field1"
is there anyway I can make the query prompt you for multiple fields and only return those who actualy have something writen in them.
or make it prompt multible and then search for the first on who you dont turn in blank ?
|
|
Reply By:
|
leehambly
|
Reply Date:
|
12/15/2006 6:34:25 AM
|
This is where you start getting into it now...
How many criteria (criterium?) do you have?
Maybe a form for the user to enter the data on would be preferable, then they wont have to keep entering data into the little dialog boxes.
You can reference the fields on a form in the same way as you are with the [Type the phrase of letters you are looking for:], you are currently using.
The format is:
Forms!<formName>!<formControl>
So, if you have a form named: frmSearch
And on that form you have a textbox named: txtProjectName
You would use: Forms!frmSearch!txtProjectName in your query.
So, you need to open the form first and then enter the data in the text box and then run the query.
You could, I suppose, then run 20 queries (against each of your tables) with all of the queries pointing tot he same textbox - I still suggest you combine all your data though!
The way to get around the user not putting any data in the textbox and still wanting the query to show the results is to put the following, though this may start confusing you:
Forms!frmSearch!txtProjectName or Like Forms!frmSearch!txtProjectName is null
It needs both parts of the statements, including the or and the like and the is null!
Food for thought, and I am skimming over so much, I think you should invest in a book... I'm not on commission!
|
|
Reply By:
|
leehambly
|
Reply Date:
|
12/15/2006 6:40:43 AM
|
Sorry, should have answered your question...
Yes it is possible to do more than one. Simply put in more than one. If the criteria are on the same horizontal line then you will be looking for both criteria (AND). If you want one or the other of the criteria to be true, then you should put them on different lines (OR).
Note: each of your criteria will need a different phrase, or it will take the input data for both!
I think a form is a better solution though!
|
|
Reply By:
|
Svenni
|
Reply Date:
|
12/15/2006 6:45:12 AM
|
sorry for troubling you :P
WHERE ((([PROJECTNAME].FIELD1) Like "*" & [Type the phrase of letters you are looking for:] & "*") AND (([PROJECTNAME].FIELD2) Like "*" & [Type the phrase of letters you are looking for:] & "*"))
I this ****er is only missing just a small fix somewhere to make the second one prompt incase the first oen is empty,
Can I make variables or "if" sentences in this programing language :S ?
|
|
Reply By:
|
Svenni
|
Reply Date:
|
12/15/2006 6:46:04 AM
|
ohh, sorry late reading your answer leehamb :P love <3
|
