Wildcard & Like XL97 to Access

[amac]

I am trying to set up a wildcard search into a field from XL97 to Access97, using DAO 3.5 The code is something like this:<br><br>Dim CritStr as string<br>Dims etc<br>...<br><br>' trying to find station name Portland<br>' using this fragment of name supplied by user<br>CritStr = &quot;ort&quot;<br>...<br><br>Set DB1 = database etc.<br>Set RS1 = DB1.OpenRecordset(Name:=&quot;SELECT lngStnID, &quot; & _<br> &quot;strCountry, strStation, &quot; & _<br> &quot;sngLatitude, sngLongitude &quot; & _<br> &quot;FROM tblDataStations &quot; & _<br> &quot;WHERE [strStation] = Like &quot;*&quot; &&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;&nbsp;CritStr & &quot;*&quot; &quot; & _<br> &quot;ORDER BY tblJones_Stns.lngStnID&quot;, _<br> Type:=dbOpenDynaset)<br><br>I have tried many other WHERE lines with no success. The like operator does not seem to work using an XL to Access parameter query either.<br><br>Can anyone help please?<br><br><br>Thanks<br>Alan

 

[DreewToo]

...<br><br>I may be way off on this, but perhaps :<br><br>&quot;WHERE [strStation] Like '*[&quot; & CritStr & &quot;]*'&quot; & _<br>&quot;ORDER BY tblJones_Stns.lngStnID&quot;, _<br>Type:=dbOpenDynaset)<br><br>Drew<br><br>

 

[amac]

About my own question to the forum.
This works. (no doubt improvements are needed)
Cheers, Amac

PURPOSE
The basic idea is to be able to quickly search for
data station info using part strings in two
worksheet text boxes.

I got some good info from the article
&quot;Everything About Using Parameters&quot; by Mike Gunderloy,
which I found by searching for &quot;parameter query&quot; on
Microsoft's Website.


Dims as required
...
...

' get the search strs from worksheet textboxes
CntryStr = Worksheets(&quot;StnList&quot.txtCountry.Text
StnStr = Worksheets(&quot;StnList&quot.txtStation.Text

'check if text exists or exit SUB
...

Set DB1 = OpenDatabase(&quot;TestData1.MDB&quot

' break up the SQL string into parts for convenience
' the parameters sent to Access need to have data type set
' (if passing an integer as a parameter then it would be
' [Param1] Integer; etc )
XLQryPart1 = &quot;PARAMETERS [Param1] Text, [Param2] Text; &quot; & _
&quot;SELECT lngStnID, strCountry, strStation &quot;
&quot;FROM tblDataStations&quot;

' deal with WHERE section
' note double quotes around the asterisks
XLQryPart2 = &quot;WHERE [strCountry] like &quot;&quot;*&quot;&quot; & [Param1] & &quot;&quot;*&quot;&quot; &quot; & _
&quot; AND [strStation] like &quot;&quot;*&quot;&quot; & [Param2] & &quot;&quot;*&quot;&quot; &quot;

' part 3
XLQryPart3 = &quot;ORDER BY [strCountry], [strStation] ASC&quot;

' make up complete SQL string
XLQryStr = XLQryPart1 & XLQryPart2 & XLQryPart3

' this is valid if at least one query exists,
' otherwise need to do .CreateQueryDef
Set QRY1 = DB1.QueryDefs(&quot;XLGetStn&quot
QRY1.Sql = XLQryStr

' this is the key!!
' let Access know about your user input sources
' (the parameters collection is zero based.)
QRY1.Parameters(0) = CntryStr
QRY1.Parameters(1) = StnStr

Set RS1 = QRY1.OpenRecordset(dbOpenDynaset, Options:=dbReadOnly)

' the dataset is now available to dump on the worksheet
' in one hit
With Worksheets(&quot;YourWorkSheet&quot
.Range(&quot;a2&quot.CopyFromRecordset RS1

' other ops. ...
End With