SQL query search by beginning letter

- MSAccess
- Microsoft SQL Server  2000 - 8.00.760 (Intel X86)
- FrontPage 2003

I have two tables: tblLOCAL and tblNATIONAL. Each table has the same fields and have no relationships. Tables have to remain separate for updating purposes.

I am trying to generate a report using a form searching for ALL Titles begning with a selected letter (A-Z or 0-9). i am using the UNION ALL to join the 2 tables. I want all records from both tables even if duplicates. I am modifying code that is working for a keyword search. The keyword search is searching for a character string (WHERE Title LIKE '%" & StrKeyword & "%'").

In this case I want to select only records (from either table) where the TITLE begins with a selected letter.

Code attached is from 2 different files. First part is the code which will give the user the option to select the letter (default.asp). The 2nd part of the code (AlphaResults.asp) is the SQL statement selecting the records.

This is the last problem with this project. Thanks for your help!
<!-- Following is the drop-down ALPHABET option to search for title beginning with -->
	<FORM METHOD="POST" name="AlphaList" ACTION="AlphaResults.asp">
	Select <b>LETTER</b> to list titles beginning with: 

	<select name = "AlphaList">
		<option value=''></option>
		<option value=''>List All</option>
		<option value='0-9'>Nos.</option>
		<option value='A'>A</option>
		<option value='B'>B</option>
		<option value='C'>C</option>
		<option value='X'>X</option>
		<option value='Y'>Y</option>
		<option value='Z'>Z</option>

	<input type="submit" value="Select Letter">&nbsp;
	<input type="reset" value="Clear">


Dim objRS, StrAlphaList, sql
	StrAlphaList = request.form("AlphaList")

sql = "SELECT * FROM (SELECT * FROM tblNational " & _
		"UNION ALL " & _
		"SELECT * FROM tblLocal) AS T1 "
		If StrAlphaList <> "" then
                  Sql = sql & "WHERE Title LIKE 'StrAlphaList & "%'" & _
                  " AND ActiveTitle = 'Yes' "
            End If
		If Request.querystring("sort") = "" then
			sql = sql & "ORDER BY Title"
 			sql = sql & "ORDER BY " & Request.querystring("sort")
 		End If			

'Diagnostic for sql statement errors
'response.write "SQL statement:<font color='red'>" &  sql & "</font><p>"
	Set objRS = Server.CreateObject("ADODB.Recordset")
	objRS.Open sql, objConn
'Determine if there are results
	If objRS.EOF then
		'If no records
		Response.Write "There are no individuals listed for the " & _
			Ucase(prog) & "AlphaList"

While Not objRS.EOF


Open in new window

Who is Participating?
DoveTailsConnect With a Mentor Commented:
Try this code:
                  sql = sql & "WHERE Title LIKE " & Chr(39) & stralphalist & "%" & Chr(39) & _
                  " AND ActiveTitle = 'Yes' "

result should be:
SELECT * FROM (SELECT * FROM tblNational UNION ALL SELECT * FROM tblLocal) AS T1 WHERE Title LIKE 'a%' AND ActiveTitle = 'Yes'

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
are you getting errors or no results? also, which database are you executing the query on? can you give an example of the sql being generated?
JLohmanAuthor Commented:
That's the problem. I am not getting anything, just a PAGE CANNOT DISPLAY!

I had the code working for just ONE letter and the only thing I've changed is the SQL statement.
Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Big MontySenior Web Developer / CEO of ExchangeTree.org Commented:
try turning off friendly http error messages (assuming IE is being used):

Tools-->Properties-->Advanced and then uncheck the option

How many of your records have ActiveTitle = 'Yes'
JLohmanAuthor Commented:

I am using that same filtering criteria in other forms and it does work.

Line 35, the usage of the double and single quotes seems to be incorrect.
What's the distinction between the first two options - they both appear to be zero length strings?
@DoveTails is correct.

  Sql = sql & "WHERE Title LIKE 'StrAlphaList & "%'" & _
should be more like:

Sql = sql & "WHERE Title LIKE  '" & StrAlphaList & "%' "  & _
JLohmanAuthor Commented:
The 1st one is probably superfluous. The 2nd one should retrieve ALL records.
In the meantime, try this:

Sql = sql & "WHERE Title LIKE 'StrAlphaList & "%'" & _

         ' Response.Write "DEBUG: " & SQL & "<HR>"

what do you have.

I will be gone for awhile.

I am sure capable hands will continue.
sammySeltzerConnect With a Mentor Commented:
sorry instead this:
' Response.Write "DEBUG: " & SQL & "<HR>"

it should be this:
 Response.Write "DEBUG: " & SQL & "<HR>" ' --- I had to uncomment it

JLohmanAuthor Commented:
The string is being transferred from the default.asp page.

The SQL statement is:
sql = "SELECT * FROM (SELECT * FROM tblNational " & _
            "UNION ALL " & _
            "SELECT * FROM tblLocal) AS T1 "
            If StrAlphaList <> "" then
                  Sql = sql & "WHERE Title LIKE 'StrAlphaList & %'" & _
                  " AND ActiveTitle = 'Yes' "
            End If

The DEBUG statement is:

DEBUG: SELECT * FROM (SELECT * FROM tblNational UNION ALL SELECT * FROM tblLocal) AS T1 WHERE Title LIKE 'StrAlphaList & %' AND ActiveTitle = 'Yes' ORDER BY Title

JLohmanAuthor Commented:
That works great for the LETTER options. If the option for numbers is selected
                 <option value='0-9'>

No records are retrieved. The DEBUG statement:
DEBUG: SELECT * FROM (SELECT * FROM tblNational UNION ALL SELECT * FROM tblLocal) AS T1 WHERE Title LIKE '0-9%' AND ActiveTitle = 'Yes' ORDER BY Title
How is the numeric value of title stored on the database?

I mean '0-9'?

I know it is character value but are they stored as '0', '1', etc?
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.