Searching multiple fields

Results 1 to 2 of 2

Thread: Searching multiple fields

  1. #1
    Join Date
    Dec 1969

    Default Searching multiple fields

    Help! I am a beginner programmer in Access97! I have created a form to contain School information. The form contains the following information: Name of School, address, city, state, zip code, Name of principal,etc. When the user presses a button on this form, I would like another form to pop-up, prompting the user to enter the name of school, address, city, state, zip code. If there are any duplicates in the database, I would like the program to show all instances of the particular record in question to the user on the main form. Basically this function should work like the find button in access, only giving the user the opportunity to enter multiple fields at once. I am at a total loss as to how to do this! Any information regarding this matter would be greatly appreciated! Thanks in advance!

  2. #2
    Join Date
    Dec 1969

    Default RE: Searching multiple fields

    It&#039s not as hard as you might think.<BR><BR>Just put the fields on your form:<BR><BR>&#060;FORM Method=Post ...&#062;<BR>Name of school: &#060;INPUT Name="School" Size=40&#062;<BR>City: &#060;INPUT Name="City" Size=40&#062;<BR>State: &#060;INPUT Name="State" Size=10&#062;<BR>Zip Code: &#060;INPUT Name="ZipCode" Size=15&#062;<BR>...<BR><BR>*********<BR>And then, on the next page you do this:<BR><BR>&#060;%<BR>&#039 for school, double up &#039 in case name is "St. Swithin&#039s" or such<BR>school = Replace( Trim(Request.Form("School")), "&#039", "&#039&#039" )<BR>city = Trim(Request.Form("City"))<BR>state = Trim(Request.Form("State"))<BR>zip = Trim(Request.Form("ZipCode"))<BR><BR>Query = "SELECT * FROM schoolsTable"<BR>Conjunction = " WHERE "<BR><BR>If school &#060;&#062; "" Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;Query = Query & Conjunction & "schoolname LIKE &#039%" & school & "%&#039"<BR>&nbsp;&nbsp;&nbsp;&nbsp;Conjunctio n = " AND "<BR>End If<BR><BR>If city &#060;&#062; "" Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;Query = Query & Conjunction & "city LIKE &#039%" & city & "%&#039"<BR>&nbsp;&nbsp;&nbsp;&nbsp;Conjunctio n = " AND "<BR>End If<BR><BR>If state &#060;&#062; "" Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;Query = Query & Conjunction & "state = &#039" & state & "&#039"<BR>&nbsp;&nbsp;&nbsp;&nbsp;Conjunction = " AND "<BR>End If<BR><BR>If zip &#060;&#062; "" Then<BR>&nbsp;&nbsp;&nbsp;&nbsp;Query = Query & Conjunction & "zipcode = &#039" & zip & "&#039"<BR>&nbsp;&nbsp;&nbsp;&nbsp;Conjunction = " AND "<BR>End If<BR><BR>... and then use that Query to find matching records ...<BR><BR>*****************<BR><BR>I did "LIKE" for school and city, so the user could just enter part of the name (e.g., Angel for city to get Los Angelese...but also then San Angelo). But I used exact match for state (should be exact 2-char abbrev.) and zip code. Change to suit yourself.<BR><BR>Anyway, the point of this is that any field the user does *NOT* fill in on the prior page is also NOT used in making the query. So if the user filled in no fields at all, he/she would get back every record in the database! (Okay, you probably don&#039t want to allow that. So you figure out how you want to restrict this. Maybe they *must* at least put in a state? Or at least one field? Or ...) Filling in just the State info would get all schools in the state. Filling in City and State would get...<BR><BR>Well, you get the idea.<BR><BR>My "trick" with the "Conjunction" is a simple one: The first selection clause, *whatever it is*, is preceded by WHERE but all other selections are preceded with AND...exactly as you need!<BR><BR>Give it a shot.<BR><BR>

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts