If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Parameter Query Help
hello all
i am attempting to create a parameter query, which is where you would put [command] in the "criteria" section in order to have access prompt you with a text box when you run the query (right??). with the form i am using, i have one "major" field, which can be identified by several other fields i have in my table. i have several locations, which are all uniquely numbered, that can be searched in a number of ways by 5 fields. i want to create the parameter query that will allow me to input any of the 5 fields and narrow down the table i have. for example... the "seconday" fields i have are "road", "direction", "mile marker", "county", and "town". one time that i run the query, i would like to search using the road and direction, but not the others...while another time, i would like to search by county, but not the others. is there a way i can create this query such that it will narrow down my fields using the information i provide in the parameter dialogue boxes, without having to input something into every box? sorry this has been so long and disjointed...im fairly new with access. cheers mark |
#2
|
|||
|
|||
Parameter Query Help
Build a SQL string in VB via a form with Option Boxes
"mark" wrote in message ... hello all i am attempting to create a parameter query, which is where you would put [command] in the "criteria" section in order to have access prompt you with a text box when you run the query (right??). with the form i am using, i have one "major" field, which can be identified by several other fields i have in my table. i have several locations, which are all uniquely numbered, that can be searched in a number of ways by 5 fields. i want to create the parameter query that will allow me to input any of the 5 fields and narrow down the table i have. for example... the "seconday" fields i have are "road", "direction", "mile marker", "county", and "town". one time that i run the query, i would like to search using the road and direction, but not the others...while another time, i would like to search by county, but not the others. is there a way i can create this query such that it will narrow down my fields using the information i provide in the parameter dialogue boxes, without having to input something into every box? sorry this has been so long and disjointed...im fairly new with access. cheers mark |
#3
|
|||
|
|||
Parameter Query Help
newbie,
i too am a newbie...and i have no idea what an sql string or vb is. any way you could type a quick tutorial in "english"?? cheers mark -----Original Message----- Build a SQL string in VB via a form with Option Boxes "mark" wrote in message ... hello all i am attempting to create a parameter query, which is where you would put [command] in the "criteria" section in order to have access prompt you with a text box when you run the query (right??). with the form i am using, i have one "major" field, which can be identified by several other fields i have in my table. i have several locations, which are all uniquely numbered, that can be searched in a number of ways by 5 fields. i want to create the parameter query that will allow me to input any of the 5 fields and narrow down the table i have. for example... the "seconday" fields i have are "road", "direction", "mile marker", "county", and "town". one time that i run the query, i would like to search using the road and direction, but not the others...while another time, i would like to search by county, but not the others. is there a way i can create this query such that it will narrow down my fields using the information i provide in the parameter dialogue boxes, without having to input something into every box? sorry this has been so long and disjointed...im fairly new with access. cheers mark . |
#4
|
|||
|
|||
Parameter Query Help
Hi Mark,
Can sympathize with you - wish I had Visual Basic skills myself. If you're using the standard query design grid, you can try this. For the criteria under each field that could be tested, word your parameter command like this: Like "*"&[Enter search string]&"*". Use the identical text each time, and make sure the criteria are in different rows for each tested field, making it an OR test rather than an AND test. If you use the same text inside the square brackets each time, Access will see it as a single parameter and prompt the user only once; it will then use the user's input to test each of the fields for the existence of the user's string. The output will include all records where the string occurs in ANY of the tested fields. You may get some unwanted records because the string may occur in more than one field. The more specific the string, the smaller the output. For example, if you input "Washington" you'll get records for Washington county, Washington city, Washington highway, etc. If this result (extra records) is not acceptable, then you'll have to prompt with different parameter text for each field. Keep in mind though, that if the user responds with a blank return to any of these prompts (supplies no text in response to the prompt) that all records will be returned. Would like to know if this works for you. Can give more discussion if I missed your point or if am not entirely clear. hcj -----Original Message----- hello all i am attempting to create a parameter query, which is where you would put [command] in the "criteria" section in order to have access prompt you with a text box when you run the query (right??). with the form i am using, i have one "major" field, which can be identified by several other fields i have in my table. i have several locations, which are all uniquely numbered, that can be searched in a number of ways by 5 fields. i want to create the parameter query that will allow me to input any of the 5 fields and narrow down the table i have. for example... the "seconday" fields i have are "road", "direction", "mile marker", "county", and "town". one time that i run the query, i would like to search using the road and direction, but not the others...while another time, i would like to search by county, but not the others. is there a way i can create this query such that it will narrow down my fields using the information i provide in the parameter dialogue boxes, without having to input something into every box? sorry this has been so long and disjointed...im fairly new with access. cheers mark . |
#5
|
|||
|
|||
Parameter Query Help
Scenario
I have a table with 4 fields and I want to be able to search on any combination with the result set appearing in a report. 1. Create a new form with 4 check boxes, 4 textboxes and a command button call the checkboxes chkStreet, chkDirection, chkMileStone, chkCounty call the textboxes txtStreet, txtDirection, txtMileStone, txtCounty call the command button btnSearch 2. Create a normal query - do not specify any criteria eg SELECT Table2.FieldA, Table2.FieldB, Table2.FieldC, Table2.FieldD FROM Table2; 3. Create a report and base it on the query Go back to the form created previously in the OnClick event of the command button put the following code - you will probably have to alter it slightly to match your field/table names strSQL = "" If Me.chkStreet = True Then strSQL = "FieldA = '" & Me.txtStreet & "'" End If If Me.chkCounty = True Then If Not strSQL = "" Then strSQL = strSQL & " AND FieldB = '" & Me.txtCounty & "'" Else strSQL = "FieldB = '" & Me.txtCounty & "'" End If End If If Me.chkDirection = True Then If Not strSQL = "" Then strSQL = strSQL & " AND FieldC = '" & Me.txtDirection & "'" Else strSQL = "FieldC = '" & Me.txtDirection & "'" End If End If If Me.chkMileMarker = True Then If Not strSQL = "" Then strSQL = strSQL & " AND FieldD = '" & Me.txtMileMarker & "'" Else strSQL = "FieldD = '" & Me.txtMileMarker & "'" End If End If DoCmd.OpenReport "rptResults", acViewPreview, , strSQL You will probably want to put some validation on the form so that the text boxes can't be empty if the corresponding check box is ticked eg Private Sub chkStreet_AfterUpdate() Select Case chkStreet Case Is = True Me.txtStreet.Enabled = True Me.txtStreet.Value = "" Case Is = False Me.txtStreet.Enabled = False Me.txtStreet.Value = "" End Select HTH "Mark" wrote in message ... newbie, i too am a newbie...and i have no idea what an sql string or vb is. any way you could type a quick tutorial in "english"?? cheers mark -----Original Message----- Build a SQL string in VB via a form with Option Boxes "mark" wrote in message ... hello all i am attempting to create a parameter query, which is where you would put [command] in the "criteria" section in order to have access prompt you with a text box when you run the query (right??). with the form i am using, i have one "major" field, which can be identified by several other fields i have in my table. i have several locations, which are all uniquely numbered, that can be searched in a number of ways by 5 fields. i want to create the parameter query that will allow me to input any of the 5 fields and narrow down the table i have. for example... the "seconday" fields i have are "road", "direction", "mile marker", "county", and "town". one time that i run the query, i would like to search using the road and direction, but not the others...while another time, i would like to search by county, but not the others. is there a way i can create this query such that it will narrow down my fields using the information i provide in the parameter dialogue boxes, without having to input something into every box? sorry this has been so long and disjointed...im fairly new with access. cheers mark . |
Thread Tools | |
Display Modes | |
|
|