Parameter Using InStr Function

[croy]

Somewhere in this group, I picked up the trick of using the
InStr function to allow a user to pick more than one value
in a parameter query. It works very well, except for one
glich: the field in question is a Text field, but some of
the entries are numbers, and some of those have decimal
fractions.

When a single value of 11.2 is entered, the query returns
all records with values of "11.2" *and* "2"!

I tried putting quotes around the value as entered in the
parameter box, but that didn't seem to change anything.

At first, I thought the function was treating the decimal
point (actually a period, here) as a comma, but if that were
the case, the query would return records with values of "11"
and "2", not "11.2" and "2".

Any illumination appreciated.

 

[John W. Vinson]

Somewhere in this group, I picked up the trick of using the
InStr function to allow a user to pick more than one value
in a parameter query. It works very well, except for one
glich: the field in question is a Text field, but some of
the entries are numbers, and some of those have decimal
fractions.

When a single value of 11.2 is entered, the query returns
all records with values of "11.2" *and* "2"!

I tried putting quotes around the value as entered in the
parameter box, but that didn't seem to change anything.

At first, I thought the function was treating the decimal
point (actually a period, here) as a comma, but if that were
the case, the query would return records with values of "11"
and "2", not "11.2" and "2".

Any illumination appreciated.

Please post the SQL of your query.


John W. Vinson JVinson *at* Wysard Of Info *dot* com

 

[Dale Fye]

Croy,

You are right, it is a trick, not good programming technique. It can be
accomplished, but as often as not it will return results that you do not
expect.

It is far better to use a listbox on a form or have a continuous form with a
Yes/No field that you can setup as a checkbox on your form to select the
values you want to query on.

Dale

 

[croy]

Please post the SQL of your query.

Thanks for the reply, John.

Here's the SQL:

*****
SELECT tblIvSurv.*, tblGeoLoc.GeoLocName,
tblGeoLoc.GeoLocDE, tblWeather.WeatherDescr,
IIf(Weekday([SurveyDate])=1 Or
Weekday([SurveyDate])=7,"1","2") AS WkEnd
FROM tblWeather RIGHT JOIN (tblGeoLoc INNER JOIN tblIvSurv
ON tblGeoLoc.GeoLocId = tblIvSurv.GeoLocId) ON
tblWeather.WeatherId = tblIvSurv.WeatherId
WHERE (((tblIvSurv.SurveyDate) Between [Type the beginning
date:] And [Type the ending date:]) AND ((InStr([Enter short
(usually two-letter) abbreviation(s) for river access,
separated by commas],[GeoLocDE]))>0) AND
((tblIvSurv.IvTypeId)=2))
WITH OWNERACCESS OPTION;
*****

I think I now understand why InStr is returning what it
does: It's looking for each record's value in the string
that is typed in response to the parameter. Unfortunately,
that doesn't work well in this situation.

 

[croy]

Croy,

You are right, it is a trick, not good programming technique. It can be
accomplished, but as often as not it will return results that you do not
expect.

It is far better to use a listbox on a form or have a continuous form with a
Yes/No field that you can setup as a checkbox on your form to select the
values you want to query on.

Thanks Dale.

Do you know of any good examples for doing this? I'm ok up
until the multiple values per field part, then the wheels
come off.

 

[John W. Vinson]

SELECT tblIvSurv.*, tblGeoLoc.GeoLocName,
tblGeoLoc.GeoLocDE, tblWeather.WeatherDescr,
IIf(Weekday([SurveyDate])=1 Or
Weekday([SurveyDate])=7,"1","2") AS WkEnd
FROM tblWeather RIGHT JOIN (tblGeoLoc INNER JOIN tblIvSurv
ON tblGeoLoc.GeoLocId = tblIvSurv.GeoLocId) ON
tblWeather.WeatherId = tblIvSurv.WeatherId
WHERE (((tblIvSurv.SurveyDate) Between [Type the beginning
date:] And [Type the ending date:]) AND ((InStr([Enter short
(usually two-letter) abbreviation(s) for river access,
separated by commas],[GeoLocDE]))>0) AND
((tblIvSurv.IvTypeId)=2))
WITH OWNERACCESS OPTION;
*****

I think I now understand why InStr is returning what it
does: It's looking for each record's value in the string
that is typed in response to the parameter. Unfortunately,
that doesn't work well in this situation.

I'm wondering if your problem is in the table design. If GeoLocDE can contain
multiple two letter abbreviations, that's clearly wrong!

What values will this field contain?
What values will users typically enter in the prompt?
What results do you want to see?

John W. Vinson [MVP]

 

[Jamie Collins]

Somewhere in this group, I picked up the trick of using the
InStr function to allow a user to pick more than one value
in a parameter query. It works very well, except for one
glich <<snipped>>

Suggested alternative approach: Create a permanent scratch table. Put
the search values plus session ID into the scratch table. Join the
scratch table to the table(s) being searched. When done, clean up by
clearing down the scratch table values for the session ID, ready for
next time (this could also be the first step in the process).

Jamie.

--

 

[croy]

SELECT tblIvSurv.*, tblGeoLoc.GeoLocName,
tblGeoLoc.GeoLocDE, tblWeather.WeatherDescr,
IIf(Weekday([SurveyDate])=1 Or
Weekday([SurveyDate])=7,"1","2") AS WkEnd
FROM tblWeather RIGHT JOIN (tblGeoLoc INNER JOIN tblIvSurv
ON tblGeoLoc.GeoLocId = tblIvSurv.GeoLocId) ON
tblWeather.WeatherId = tblIvSurv.WeatherId
WHERE (((tblIvSurv.SurveyDate) Between [Type the beginning
date:] And [Type the ending date:]) AND ((InStr([Enter short
(usually two-letter) abbreviation(s) for river access,
separated by commas],[GeoLocDE]))>0) AND
((tblIvSurv.IvTypeId)=2))
WITH OWNERACCESS OPTION;
*****

I think I now understand why InStr is returning what it
does: It's looking for each record's value in the string
that is typed in response to the parameter. Unfortunately,
that doesn't work well in this situation.

I'm wondering if your problem is in the table design. If GeoLocDE can contain
multiple two letter abbreviations, that's clearly wrong!

What values will this field contain?
What values will users typically enter in the prompt?
What results do you want to see?

Each record in tblGeoLoc is a geographical location.
GeoLocDE is the data-entry code for each location, and that
is what the users enter in the prompt.

What I want to see is a report showing certain information
for all the locations the user has entered in the parameter
prompt. This is actually for a report, most of which is
pulled from another table, where GeoLocId is a foreign key.

It's easy to design the parameter for one location only, but
the boss wants to be able to make a report that covers any
number of, or combination of, locations.

 

[croy]

Suggested alternative approach: Create a permanent scratch table. Put
the search values plus session ID into the scratch table. Join the
scratch table to the table(s) being searched. When done, clean up by
clearing down the scratch table values for the session ID, ready for
next time (this could also be the first step in the process).

Thanks, Jamie. I've been wondering if I should do something
like that. I was actually thinking of adding a field to one
of the existing tables to use as an "inclusion" (scratch)
field. It's good to know that someone else is at ease with
a similar approach. I'll experiment with it now.

 

[John W. Vinson]

It's easy to design the parameter for one location only, but
the boss wants to be able to make a report that covers any
number of, or combination of, locations.

What you might want to do in that case is create a multiselect Listbox on your
form to allow the user to select the desired locations. You can adapt this VBA
code to construct a query using the IN() clause to select multiple criteria,
without the ambiguities of the InStr() method:

Private Sub cmdProcess_Click()
' Comments : Update the AnimalCondition table based on the selections in
' the unbound multiselect listbox lstHealthIssues.
' Newly selected rows will be added to the table, newly
cleared
' rows will be deleted.
' Parameters: None
' Modified : 01/29/02 by JWV
'
' --------------------------------------------------
' Populate the AnimalCondition table with the selected issues
On Error GoTo PROC_ERR

Dim iItem As Integer
Dim lngCondition As Long
Dim db As DAO.Database
Dim rs As DAO.Recordset

' save the current record if it's not saved
If Me.Dirty = True Then
Me.Dirty = False
End If
Set db = CurrentDb
' Open a Recordset based on the table
Set rs = db.OpenRecordset("AnimalCondition", dbOpenDynaset)
With Me!lstHealthIssues
' Loop through all rows in the Listbox
For iItem = 0 To .ListCount - 1
lngCondition = .Column(0, iItem)
' Determine whether this AnimalID-HealthID combination is
currently
' in the table
rs.FindFirst "[AnimalID] = " & Me.AnimalID & " AND " _
& "[HealthIssueID] = " & lngCondition
If rs.NoMatch Then ' this item has not been added
If .Selected(iItem) Then
' add it
rs.AddNew
rs!AnimalID = Me.AnimalID
rs!HealthIssueID = lngCondition
rs.Update
End If ' if it wasn't selected, ignore it
Else
If Not .Selected(iItem) Then
' delete this record if it's been deselected
rs.Delete
End If ' if it was selected, leave it alone
End If
Next iItem
End With
rs.Close
Set rs = Nothing
Set db = Nothing
Me.subAnimalCondition.Requery

PROC_EXIT:
Exit Sub

PROC_ERR:
MsgBox "Error " & Err.Number & " in cmdProcess_Click:" _
& vbCrLf & Err.Description
Resume PROC_EXIT

End Sub



John W. Vinson [MVP]

 

[croy]

Thanks, Jamie. I've been wondering if I should do something
like that. I was actually thinking of adding a field to one
of the existing tables to use as an "inclusion" (scratch)
field. It's good to know that someone else is at ease with
a similar approach. I'll experiment with it now.

Well, I tried my extra field approach... now I'm trying your
extra table approach ;-) .


Session Id? Where do I find this, and why include it
(clueless)?

[...] clearing down the the scratch table values...

Hmmm. I'm trying this with a detete query, triggered by the
unload event, but thinking there might be a better way...