Regex and Form Controls with Select Case (1 Viewer)

[dstahl81]

Hi all,
I am trying to comew up with a way to use regex coupled with a Select Case to validate some data. Here's what I have.

On my form I have an unbound text box that is used to query a single table called address and output to a listbox. If a user inputs a house number e.g. "1111" the list of addresses is for any address begin with that number. Conversely, if the user inputs a road name e.g. "ADAMS AVE" the query will output to the listbox all addresses with "ADAMS AVE" in it. This is currently accoomplished with a regex.test for the text box like so, assume regex declarations have been made:

[
regex.pattern = "\d"
result = the regex code....yes I know this is NOT actual code

If result then
do a query to return address by house number
Else
do a query to return addresses by road name

End If

set rowsoure for listbox
requery listbox
]

What I want to add is the capability to have *1111 ADAMS* and know (using regex) as a validation that this is a search for an address WHICH may be incomplete.

To accomplish this I thought of using a Select Case but am not sure how to pass the single textbox value through a Select Case.

I imagine it somewhat like this:

[declare regex stuff)


Select Case return

Case "\d"
Run Query by House Number

Case "\w+"
Run Query by Address Road Name

Case "d{1,5} \w+"
Run query by House Number and Address Road Name where left (for so many characters is the house number) and remaining right is Address Road Name.

End Select


Any thoughts are VERY welcome!

Daneil

 

[jdraw]

What exactly is the business issue you are trying to solve with your proposed Select Case in simple, plain English?
How many records are involved?
How often do you have to do this "cleansing"?

 

[dstahl81]

Business Issue:

Allow 3 different search abilities from single unbound text box predicated on the information entered.

1. only numbers returns addresses begining with those numbers
2. only road name or partial road name returns addresses with that road name it in.
3. house number and road name returns exact match (if none found, combination of numbers and road name entered).

This is for a 911 call center database, just trying to speed up address searches is all.

 

[jdraw]

??Where are you located??
Have you researched any 911 standards/articles?
I found a few
https://www.fcc.gov/9-1-1-call-centerspsaps
https://www.nena.org/page/OperationsStandards and related links

Perhaps I have misunderstood your intent, but it seems that working at this level in Access is barely scraping the surface of the planning and details involved.

If this was a class project to review /cleanse names etc, I could understand.

I'm not sure what your data looks like, but you might find that Google maps or similar appropriate???

Perhaps you could tell us more about your overall plan and where and how this Access part fits.

 

[dstahl81]

Anyone else???

 

[NauticalGent]

I don’t have time right now to fully engage, but you might give this a look over. I use it to test my strings prior to using them in my application.

https://www.access-programmers.co.uk/forums/showthread.php?t=297155

Good luck!

 

[Mark_]

This is for a 911 call center database, just trying to speed up address searches is all.

Is the back end an SQL server or are you using a split database?

 

[arnelgp]

' say you have an unbound textbox (txtSearch) and a command button (cmdSearch)
' on a form.
'
' first create a simple select query (qryAddress):
'
' select * from table1
'
' table1 is where your address field is, and on this table
' you want to search.
'
' add code to the Click event of your button:
'
' this is simple and does not need to use RegExp
'
Private Sub cmdSearch_Click()
Dim sCriteria As String
Dim sSQL As String
Dim var As Variant
Dim i As Integer
Dim db As DAO.Database
Dim qd As DAO.QueryDef

If Trim(Me.txtSearch & "") = "" Then Exit Sub
var = Split(Me.txtSearch, " ")
For i = 0 To UBound(var)
sCriteria = sCriteria & " or [address] like '*" & var(i) & "*'"
Next
If Len(sCriteria) <> 0 Then
sCriteria = Mid(sCriteria, Len(" or "))
Set db = CurrentDb
Set qd = db.QueryDefs("qryAddress")
sSQL = qd.SQL
sSQL = Replace(sSQL, ";", "")
If InStr(sSQL, "where") <> 0 Then
sSQL = Left(sSQL, InStr(sSQL, "where") - 1)
End If
sSQL = sSQL & " where " & sCriteria
qd.SQL = sSQL
Set qd = Nothing
Set db = Nothing
End If
DoCmd.OpenQuery "qryAddress"

End Sub

 

[dstahl81]

@arnelgp

I go back on shift tomorrow night, in between calls I'll see if this works. I'll report my findings if I'm stuck.

Thank you sir!
Daniel

 

[dstahl81]

@arnelgp

So I got anxious and built a test db at home with fake addresses, setting it up like at work. I found that your code does work! Now, I input "111 Adams Ave" I get nothing. How do I also get anything from that input that is either 111 or Adams or ave?

 

[Mark_]

So you want 111 Adams to return 1111 Adams Ave? Would you also want 1111 ams to return 1111 Adams Ave?

 

[dstahl81]

Yes'ish, I want the db to be "smart" enough in case an operator is in a hurry.

111 Adms

could return:

11 Adams st
111 Adams Ave
1111 Admantium Blvd

These are all possible solutions. Again, this is for 911 operators that might be a tad hasty at the moment.

 

[Mark_]

Are the addresses stored in an SQL server or Access back end? Each would have a different answer for getting a better return value.

 

[arnelgp]

Should work on either of your input.
Tested it before posting.
Remember table1 and [address] is but sample table and field name.
Review the code again maybe u missed something.
Better to copy and paste the inner code and just replace the query name, table name, etc with your own.

 

[dstahl81]

Hey all,
So I tried the code last night at work and realized our version of Access (97) is missing certain functions available in later ones.....split, ubound to name a few.

Daniel

 

[Mark_]

Daniel,

I think Post #4 actually gave you a working answer. Google Maps will do the kinds of partial matches you are looking for.

If you'd answered JDraw we'd be in a better position to give better answers. How many records are you looking at and how quickly are you trying to get a match? Do you have a limit for the number of "possible matches" you are looking to return?

I am going to hazard a guess that you are looking for matching results based on either a location or individual whom you've already been called for and your looking to display previous calls while the dispatcher is still on the line. let me know if this is about the business model you are looking for.

 

[dstahl81]

Mark,
I am limited by current business processes which stipulate we use the current database to query 911 addresses with. This is to validate if the caller is in our jurisdiction, thus if the address is not loaded they may be calling from outside of the county. It doesn't happen often when a caller's address doesn't come up but it's a valid address in the county meaning we need to update the database.

There are no more than 50,000 records currently.

The motto is seconds save lives so the quicker we can search an input, whether a house number (1111) or road name (Adams Ave) and get an output the better. It doesn't have to find the exact match, of course, but that'd be nice too.

I think I'll export what I'm working with, sanitize names/numbers and upload for recommendations.

 

[Mark_]

You may wish to try out Allen Browne's SoundEx code and see if it will do what you need. Down side is if you don't have this done on the back end you will need to go through all 50,000 records to try and find matches.

 

[dstahl81]

So, after some deliberation (waking up) I realized that @arnelgp did provide a solution to my original request. I now have to wait until we can upgrade access to something newer than 97 as the code doesn't work.

It works PERFECT on 2013 though!

Daniel

 

[jdraw]

the code doesn't work.

Error message? Missing reference?
arnelgp said he tested it???

What exactly doesn't work?