?
Solved

Search specific characters in a field

Posted on 2003-02-21
8
Medium Priority
?
147 Views
Last Modified: 2010-04-06
I am using ASP and MS Access.
I have a field in a database that consists of a numbering scheme like this. 101-45801A 101-45600 I want to search for the last record in the database with the 5th through 7th numbers being 458 using a drop down box that has all of the three digit combinations. Numbers in drop down (EX. 454 456 458)
0
Comment
Question by:Gabe_mst
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 4

Expert Comment

by:whammy
ID: 7996523
"SELECT * FROM tablename where WHATEVERyourFIELDnameIS LIKE '%101-" & yourSelectOption & "%'"

:-)
0
 
LVL 2

Expert Comment

by:ActiveMedia
ID: 7996876
Whammy, you'd be better removing the first %.
It should be ".... LIKE '101-" & yourSelectOption & "%'" because the 101- must be at the beginning of the string (not just anywhere in the string).

Actually, the Gabe_mst doesn't say that every value in this field always begins with 101- it's just that his example uses 101.  

SO Gabe_mst, can you confirm if it always begins with 101 or do we need code that ignores the first four characters and looks only at characters 5 through 7?

The question also asks for the LAST row that contains the required values.  For that, when the recordset RS has been created, the script should execute an:  RS.moveLast instruction.
 
HTH
0
 
LVL 2

Expert Comment

by:ActiveMedia
ID: 7996880
Whammy, you'd be better removing the first %.
It should be ".... LIKE '101-" & yourSelectOption & "%'" because the 101- must be at the beginning of the string (not just anywhere in the string).

Actually, the Gabe_mst doesn't say that every value in this field always begins with 101- it's just that his example uses 101.  

SO Gabe_mst, can you confirm if it always begins with 101 or do we need code that ignores the first four characters and looks only at characters 5 through 7?

The question also asks for the LAST row that contains the required values.  For that, when the recordset RS has been created, the script should execute an:  RS.moveLast instruction.
 
HTH
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 4

Expert Comment

by:whammy
ID: 7996947
You're right, I shouldn't have put the first '% in there, but it would still return the right records, although it might be a few milliseconds slower. :-)

In the case that it doesn't begin with 101, that will provide a partial solution.

P.S. Gabe_mst - you aren't perhaps looking for the record you JUST inserted, are you?
0
 
LVL 15

Accepted Solution

by:
gladxml earned 200 total points
ID: 7997876
Gabe_mst,

if I understood correctly in your dropdown you have....
i.e.

otpion1 value 454
option2 value 456
option3 value 458

If this is the case you can try to use this

"SELECT * FROM tablename where Fieldname LIKE '%& " request.form("ListBoxName") & "%'"


After executing the select statement do

Do while not(rs.eof)
'assuming that that XXX-XXXXXX pattern
arraycode = split(rs("FIELDNAME"),"-")
tmpnumber = left(arraycode(UBOUND(arraycode)),3)

if tmpnumber = request.form("ListBoxName") then

response.write rs("FIELDNAME")

end if

rs.movenext
loop

Just replace the necessary tablename, filename and listboxname base on your existing db and form respectively...

Hope this help...

Happy programming...



0
 

Author Comment

by:Gabe_mst
ID: 7999266
The string will always start with 101- and I want the last record in the database with the criteria.  No I am not looking for the one I just entered.  I want to see the last one that was entered before I enter a new record.
0
 

Author Comment

by:Gabe_mst
ID: 7999282
The string will always start with 101- and I want the last record in the database with the criteria.  No I am not looking for the one I just entered.  I want to see the last one that was entered before I enter a new record.
0
 

Author Comment

by:Gabe_mst
ID: 8025597
Thanks for the concise answer.  Having the code layed out like this really helps rookies like me understand what is going on.

Gabe
0

Featured Post

On Demand Webinar - Networking for the Cloud Era

This webinar discusses:
-Common barriers companies experience when moving to the cloud
-How SD-WAN changes the way we look at networks
-Best practices customers should employ moving forward with cloud migration
-What happens behind the scenes of SteelConnect’s one-click button

Question has a verified solution.

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

Article by: Matthew
I am a very big proponent of technology compliance standards and strive to meet such criteria in all of my work. That includes my site, which is 100% XHTML 1.0 compliant as determined by the World Wide Web Consortium. https://www.matthewstevenkel…
I will show you how to create a ASP.NET Captcha control without using any HTTP HANDELRS or what so ever. you can easily plug it into your web pages. For Example a = 2 + 3 (where 2 and 3 are 2 random numbers) Session("Answer") = 5 then we…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn the basics of jQuery, including how to invoke it on a web page. Reference your jQuery libraries: (CODE) Include your new external js/jQuery file: (CODE) Write your first lines of code to setup your site for jQuery.: (CODE)
Suggested Courses

752 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