edit a string or use a wildcard in a search function

[rmy634878]

Access 2007

I can’t figure out how to replace a period that is in the middle of a string and end up with 10 digits. For example 55.5555 would be 5500005555. I can use replace() but the tricky part is I have to end up with 10 digits.

Ultimately what I’m trying to do is… when a user enters 55.5555, 555.5, 5.5 or any variation they will be able to find the corresponding record. So a wildcard for the search or the replacement of the “.” with enough zeros for 10 digits. I’m just no sure how to proceed. HELP J

:banghead:

Here is what I'm using now - i making them enter the full 10 digit number but would like to give them the ability to use the period in place of the zeros.

Function Search()
Dim lssql As String
Dim lsSn As Recordset
Dim db As Database
Dim lsMessage As String
Dim sMsg As String
Dim vRetVal As Variant
Set db = CurrentDb()

If IsNull(txtSearch) Then
' Initialize string.
sMsg = "You must enter a 10 digit Loan Number or Project Name."
vRetVal = MsgBox(sMsg, vbOK, "Incomplete Search Criteria!")
Exit Function
ElseIf Not IsNull(txtSearch) Then
lssql = "select * from qry_ProjectSelector where "
lssql = lssql & "LoanNumber = '" & Me!txtSearch & "' Or fld_ProjectName like '" & "*" & txtSearch & "*" & "' "
End If

Set lsSn = db.OpenRecordset(lssql, dbOpenSnapshot)
If lsSn.EOF Then
MsgBox lsMessage, 48, "No Record Found"
Else
Me.RecordSource = lssql
Me.Refresh
Me!LoanNumber.SetFocus
End If

End Function

 

[SOS]

You could use

Dim strIn As String
 
strIn = Me.txtSearch
If Len(strIn) < 10 And Instr(1, strIn, Chr(46)) > 0 Then
    strIn = Replace(Replace(strIn,Chr(46), Space(10-Len(strIn)+1)), Chr(32), 0)
End If

It looks first to see if the text is less than 10 and if there is a period. Then it replaces that period with the appropriate number of spaces and then replaces those spaces with zeros.

 

[SOS]

Edited my original code to make it +1 instead of -1. My tests indicate it will work