• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 158
  • Last Modified:

Search specific characters in a field

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
Gabe_mst
Asked:
Gabe_mst
  • 3
  • 2
  • 2
  • +1
1 Solution
 
whammyCommented:
"SELECT * FROM tablename where WHATEVERyourFIELDnameIS LIKE '%101-" & yourSelectOption & "%'"

:-)
0
 
ActiveMediaCommented:
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
 
ActiveMediaCommented:
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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
whammyCommented:
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
 
gladxmlCommented:
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
 
Gabe_mstAuthor Commented:
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
 
Gabe_mstAuthor Commented:
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
 
Gabe_mstAuthor Commented:
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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

  • 3
  • 2
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now