String Search

[Steve@GIHS]

Hi

We have a list of alpha-codes for families in a recordset that needs to be reduced given an alpha-code range, for example 'B' through 'M' or 'CA' to 'CS' or 'SAC' to SAX' or combinations of these three options. The user can use up to three characted for both the start and end parameters.

The current code is in VB for applications in Access 97 :
Set trst = db.OpenRecordset("SELECT * " & _
"FROM tbl_Display " & _
"WHERE ((tbl_Display.QCode)>=[Forms]![frm_Settings]![txt_Start_Char] " & _
"AND (tbl_Display.QCode)<=[Forms]![frm_Settings]![txt_End_Char])", dbOpenDynaset)

If the users enters Start Character = 'A' and End Character = 'A' the recordset returns no records even thought there may be 6 records starting with 'A'. If the start and end are 'A' and 'B' it returns only the 'A' records etc.

Any suggestions of how to code or why would be appreciated.

[Norby mahoney]

Hi

We have a list of alpha-codes for families in a recordset that needs to be reduced given an alpha-code range, for example 'B' through 'M'. Additionally, the user can use up to three characted for both the start and end parameters.

The recordset should now return all family codes in the set range.

Any suggestions of how to code this in VBA for applications would be appreciated.

Examples:
SELECT * FROM family WHERE alpha_code >= 'B' AND alpha_code <= 'M'

SELECT * FROM family WHERE
(alpha_code >= 'A' AND alpha_code <= 'C' ) OR
(alpha_code >= 'F' AND alpha_code <= 'I' ) OR
(alpha_code >= 'X' AND alpha_code <= 'Z' )

This what you mean?? Not sure if this is ANSI SQL, but it worked MS SQL Server.

[olivthill]

Hi Steve@GIHS

Now, with your example, I understand the problem.
When the users enters Start Character = 'A' and End Character = 'A', your query is equivalent to

SELECT * FROM tbl_Display
 WHERE tbl_Display.QCode >= 'A'
 AND tbl_Display.QCode <= 'A'

and you would rather need

SELECT * FROM tbl_Display
 WHERE tbl_Display.QCode >= 'A'
 AND tbl_Display.QCode <= 'AZZZZZZZZZZZZZZZZZZZ'

There is an easy way to do this with the "LIKE" SQL statement.

In order to retrieve all the rows starting by A, you have

SELECT * FROM tbl_Display
 WHERE tbl_Display.QCode LIKE 'A*'

"LIKE" is used with a wildcard, meaning that you need one or several characters. This wildcard is a star, "*", in MS-Access, and is a percent, "%", in other databases.

In VB, you wrap the query above like that:

Set trst = db.OpenRecordset("SELECT * " & _
        "FROM tbl_Display " & _
       "WHERE ((tbl_Display.QCode) LIKE '[Forms]![frm_Settings]![txt_Start_Char] " & "*'" & _
     ")", dbOpenDynaset)

In order to retrieve all the rows from "B" to "M", you need the following query (already shown by Norby mahoney)

SELECT * FROM tbl_Display
 WHERE tbl_Display.QCode >= 'B'
 AND tbl_Display.QCode <= 'M'