Microsoft Access 2000 Expression Needed for Criteria

[ollie1]

I have a table that contains fields that have spacesbetween the data. eg A field containing mobile numbers has been entered as 0123 456 789.
I want to setup an expression in the criteria of a query that prompts the user to enter the mobile number they are searching for. Inevitably the user will enter 0123456789 without the spacing. I have tried the following :

[Type in the mobile number] - this doesn't work if the user does not include the spaces.

Like[Type in the mobile number]&"*" - this doesn't work if the user does not include the spaces.

Because the table has some 2500 records changing the data in the table not to have spaces is not feasible.

I am a fairly new user to Access so any assistance is greatly appreciated

 

[GeekGirlau]

Copy this function into a module in your database.

Public Function StripSpaces(varData As Variant) As Variant
Dim strData As String


If IsNull(varData) Then
StripSpaces = Null

Else
strData = varData

Do Until InStr(1, strData, " &quot = 0
strData = Left(strData, InStr(1, strData, " &quot - 1) & Mid(strData, InStr(1, strData, " &quot + 1)
Loop

StripSpaces = strData
End If
End Function

Your query can now compare StripSpaces([MobileField]) against StripSpaces([SearchText]).

 

[ollie1]

Thanks for your reply. Unfortunately I am REALLY new to Access and Visual Basic stuff. The only part I understood was copying the info into a module, which I have done.
What do I need to do now ???
I know it must be frustrating trying to explain something to a total novice . I really appreciate your assistance

Ollie

 

[xlbo]

Right - I have to say that I don't think that code is the solution here. From what you've posted I assume this is working through a query. I would suggest that you create another field in the query (right click on a blank field and choose "Build&quot Create a formula that will strip out the blanks eg
If you have your TelNo field, the formula might be:
=Left([TelNo],4)&Mid([TelNo],6,3)&right([TelNo],3)
This will create a calculated field in the query which will be the telephone number WITHOUT the spaces
Then pop your criteria request in THAT field (ie the =[Please Enter Mobile No.])

HTH Rgds
Geoff
"Some cause happiness wherever they go; others whenever they go."
-Oscar Wilde

 

[ollie1]

Geoff
Thanks HEAPS!!!
Works like a dream!!

Ollie

 

[GeekGirlau]

Hi Ollie,

Just a further note:

Be careful about assuming that users put spaces in exactly the same place in phone numbers. I've seen some weird and wonderful examples!

You might want to look at the Input Mask available in tables. This allows you to force a phone number to LOOK as though the spaces are there, but it actually stores the number WITHOUT any spaces.

Best of luck!