HOW TO CREATE A SEARCH TOOL IN THE DATABASE? 1

Giovanni · Mar 14, 2000

i have a MS Acess database (tables with data) 

i have a not working form that i wuold like to MAKE

work and search for entries (records) within the database. 

 

search should be based on : 

document type (agenda, minutes, etc.) 

date of publication 

document unique number 

 

How can I do it? 

I'd be very grateful for any help! 

 

THANK YOU!!!

DougP · Mar 14, 2000

Well one way is to make a query. 
Thats its purpose, to ask questions of your data. 
to Create a new query Click the Queries TAB 
then add your table 
Next drag just the columns you are interested in down to the bottom. 
Click the (Red Exclamation mark) 
You will see all of the records in your table. 
Look in the "date of publication" column and find a example date. Say 1/3/00 
Click the design button(Cyan colored Triangle) which will bring you back to design mode. 
Next put a criteria in "date of publication" column 1/3/00 
Run your query again. 
I wil return less records matching those that are for that date. 
Now you can match other column too 
Just put a criterai in each one and when you run the query it will bring back less and less records. 
 
OK Thats just a start. 
Good luck. 
 
 DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

Giovanni · Mar 14, 2000

Well, 
I kinda know how to do the query search. It's easy, just adding a criteria. 
 
I have lots of records in the table though. The thing I wanted to do, but can't solve the problem yet, is that I would like to create the FORM that searches for specific record and gives me the output in a report form. 
 
Let's say 
I want <type of document> - contract 
<date of publication> - between 12/12/1998 and 06/01/1999 
 
So I want the database to display all matching records. 
 
PLEEEAASEE

) Ho can I do that?

JimHorton · Mar 14, 2000

Giovanni, 
You need to decide how complex you want the search...I've done forms that can allow the user to search on any field in any table in the db, but this takes time to build. To start with, you should just experiment with building simple SQL statments over one table on the fly, and expand from there. FOr instance, on a button on your form you could have it search for Customers, and have textboxes txtCustCith and txtCustName 
 
dim sq as string,qd as querydef 
sq = "Select * From Customers WHERE " 
sq = sq & " CustCity = """ & me!txtCustCity & """" 
If not isnull(me!txtCustName) then 
     sq = sq & " AND custname like ""*" & me!custname & "*""" 
end if 
set qd = currentdb.querydefs("tempquery&quot

qd.sql = sq 
docmd.openquery qd.name 
 
Then the sky's the limit--you can add acombo box to select the table name, basing the box on MsysObjects, other listboxes can be field with the field names from the selected tables, you can create controls on the fly after the user selects a table and fields, so you can have new fields that match the name and type of the fields chosen, etc. I've gotten crazy with these...but if this is for end users always remember--they always ask for and demand more than they need or will use, so it's best to keep things simple!

--Jim

Giovanni · Mar 14, 2000

THANKS

) 
 
I'm afraid I'm too unexperienced in MS Access -- SQL statements is something over my head now. Gees, it's killing me. Where do I start?

) 
 
 
Let me try, and I know I'll be back in few minutes

THANK YOU VEEEERRRRRRRRYYYYYYYY MUCH!

Giovanni · Mar 14, 2000

Here is the query SQL statement 
 
SELECT [Main Table].*, Availability.*, [Point of Contact].*, [Bounding Coordinates].* 
FROM [Bounding Coordinates] INNER JOIN ([Point of Contact] INNER JOIN (Availability INNER JOIN [Main Table] ON Availability.ID = [Main Table].Availability) ON [Point of Contact].ID = [Main Table].[Point of Contact]) ON [Bounding Coordinates].ID = [Main Table].[Spatial Reference]; 
 
HELP

(Jim, will i ever get smart in this stuff?)

DougP · Mar 14, 2000

Well you are half way there. 
If you want to use the query Gird to get your feet wet. Then put the code you posted right in the "date of publication" column criteria box. 
Copy and paste this right out of here to your query. 
the whole line "Between and all" 
 
between 12/12/1998 and 06/01/1999 
 
Run it (click the Red Exclamtion mark) 
It will return just those records. 
Now as you get used to it, Create your query in the Grid then Click the SQL button and have a look at what Access is really doing. 
Which is the code like JimHorton displayed. 
 
 DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

Giovanni · Mar 14, 2000

Oops, that's was the wrong part of code 
 
Thanks Doug! 
 
I'll try 
 
it's so overwhelming 
 
 
SELECT [Main Table].[Document Type], [Main Table].Title, [Main Table].[Originator: Department/ Agency Name], [Main Table].Abstract, [Main Table].Purpose, [Main Table].Availability, [Main Table].[Point of Contact], [Main Table].[Spatial Reference], [Main Table].[Control Identifier], [Main Table].[Date of Publication], [Main Table].[Date of Last Modification], [Main Table].[link to file], [Main Table].[name of the file], * 
FROM [Bounding Coordinates] INNER JOIN ([Point of Contact] INNER JOIN (Availability INNER JOIN [Main Table] ON Availability.ID = [Main Table].Availability) ON ([Point of Contact].ID = Availability.ID) AND ([Point of Contact].ID = [Main Table].[Point of Contact])) ON ([Bounding Coordinates].ID = Availability.ID) AND ([Bounding Coordinates].ID = [Main Table].[Spatial Reference]) 
WITH OWNERACCESS OPTION;

Giovanni · Mar 14, 2000

Yep, it works... But I'm so much far from the result that is needed... 
I hate to be somebody's pain in a neck. 
 
statement "between and " worked fine 
 
but how do i make the db search for particular thing 
 
i just have no clue where and how to enter that part of the code Jim gave me 
 
oh', i'm so dumb

Giovanni · Mar 14, 2000

http://csc.lsu.edu/~project/db1.mdb

DougP · Mar 14, 2000

I think you're confused by Jim's SQL code 
and when and where to use it. 
you don't have to use it at all. 
Thats what the Grid is for to make it simple to find your data. 
Let Access take care of the SQL behing the scene's 
Like your SQL you pasted 
I'm sure you did not know the first thing about it. 
And that's OK 
So just use the Grid and put in what you want and run it and forget the SQL for now. 
You can use Access and not even worry about the SQL code. 
It there but you don't have to know or understand it to use Access. 
 
 DougP <a href=mailto: dposton@universal1.com> dposton@universal1.com</a> <a href= > </a>

Giovanni · Mar 14, 2000

Did you have a chance to take a look the form in the database? This is how the end product should work. You might get the idea from looking at the FORM1 in the database. 
The user should have the acceess to GUI only and all the tables won't be seen as they are now. 
 
you enter the data and click search, the database should give you the result with a link to the file.

JimHorton · Mar 14, 2000

Giovanni, 
It sounds like you're on your way...A couple things to keep in mind (it may be too late now, but if you can change the db to this suggestion, it's helpful): 
 
Never use spaces in table names or field names. Access allows this, but I have seen (and I'm not kidding) complete sentences as table and field names, including some punctuation! This makes these sql statements look much more confusing than they are, additionally Access puts in [] brakets by default, and they're needed when you have spaces. When you write sql in code you can avoid these brackets if you avoid spaces (use under_scores to separate words if needed) Keep table/field names within a good balance of descriptiveness and length, but in any case, don't use spaces. 
 
--Jim

Giovanni · Mar 14, 2000

Thank you for your help Jim. 
Look forward to hearing from you. 
 
jtairov@hotmail.com

Mervis · Mar 14, 2000

A different way to try would be to put the following in your query in the order that you want your data displayed: In the query criteria type [Please enter the data you are looking for] This will cause a dialog box to appear asking for the data. When the user types the data in the box it will return only what matches exactly what is typed. You can also put: Like[Please enter the data you are looking for] and have the user type an * before and/or after a partial spelling of the data. This returns all data that is like what has been typed. Ex: Aug* would return August, Augusta and Augment and *ness would return enything ending in ness. Keying just the * would return all data in that field. You can do this in more than one field and it will act like the filter by selection option drilling down until you have only the data you are looking for. This was an easy way for me to get data when I was learning to use Access.

Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

HOW TO CREATE A SEARCH TOOL IN THE DATABASE? 1

Giovanni

Technical User

DougP

MIS

Giovanni

Technical User

JimHorton

Programmer

Giovanni

Technical User

Giovanni

Technical User

DougP

MIS

Giovanni

Technical User

Giovanni

Technical User

Giovanni

Technical User

DougP

MIS

Giovanni

Technical User

JimHorton

Programmer

Giovanni

Technical User

Mervis

MIS

Similar threads

Part and Inventory Search

Sponsor