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!
default.asp
<!-- 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>
	</select>

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

	</form>
__________________________________________________________
AlphaResults.asp

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

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



While Not objRS.EOF


Response.Write

Open in new window

JLohmanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Big MontyWeb Ninja at largeCommented:
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?
0
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.
0
Big MontyWeb Ninja at largeCommented:
try turning off friendly http error messages (assuming IE is being used):

Tools-->Properties-->Advanced and then uncheck the option
0
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

sammySeltzerCommented:

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

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

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

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

Sql = sql & "WHERE Title LIKE  '" & StrAlphaList & "%' "  & _
0
JLohmanAuthor Commented:
The 1st one is probably superfluous. The 2nd one should retrieve ALL records.
0
sammySeltzerCommented:
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.
0
sammySeltzerCommented:
sorry instead this:
' Response.Write "DEBUG: " & SQL & "<HR>"

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

0
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
                   StrAlphaList=Replace(StrAlphaList,"'","''")
                  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

0
DoveTailsCommented:
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'


0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
0
sammySeltzerCommented:
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?
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
ASP

From novice to tech pro — start learning today.