?
Solved

SQL query search by beginning letter

Posted on 2011-05-04
15
Medium Priority
?
977 Views
Last Modified: 2012-05-11
- 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

0
Comment
Question by:JLohman
  • 5
  • 5
  • 2
  • +2
15 Comments
 
LVL 34

Expert Comment

by:Big Monty
ID: 35693219
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
 

Author Comment

by:JLohman
ID: 35693257
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
 
LVL 34

Expert Comment

by:Big Monty
ID: 35693300
try turning off friendly http error messages (assuming IE is being used):

Tools-->Properties-->Advanced and then uncheck the option
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 29

Expert Comment

by:sammySeltzer
ID: 35693312

How many of your records have ActiveTitle = 'Yes'
0
 

Author Comment

by:JLohman
ID: 35693351
1500+

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

0
 
LVL 5

Expert Comment

by:DoveTails
ID: 35693423
Line 35, the usage of the double and single quotes seems to be incorrect.
0
 
LVL 44

Expert Comment

by:GRayL
ID: 35693448
What's the distinction between the first two options - they both appear to be zero length strings?
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 35693596
@DoveTails is correct.

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

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

Author Comment

by:JLohman
ID: 35693597
The 1st one is probably superfluous. The 2nd one should retrieve ALL records.
0
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 35693609
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
 
LVL 29

Assisted Solution

by:sammySeltzer
sammySeltzer earned 400 total points
ID: 35693620
sorry instead this:
' Response.Write "DEBUG: " & SQL & "<HR>"

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

0
 

Author Comment

by:JLohman
ID: 35693832
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
 
LVL 5

Accepted Solution

by:
DoveTails earned 1600 total points
ID: 35694085
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
 

Author Comment

by:JLohman
ID: 35694277
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
 
LVL 29

Expert Comment

by:sammySeltzer
ID: 35695138
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

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

If you’re using QODBC to update QuickBooks data from Microsoft® Access but Access is not showing the updated data, you could have set up QODBC incorrectly.
Windows Explorer let you handle zip folders nearly as any other folder: Copy, move, change, and delete, etc. In VBA you can also handle normal files and folders, but zip folders takes a little more - and that you'll find here.
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

864 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question