Parameter Query Help

  • Thread starter Thread starter mark
  • Start date Start date
M

mark

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
 
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

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
Click to expand...


.
Click to expand...
 
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
 
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 said:
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

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
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top