Identifying and replacing numbers in string

[chathag]

I have a table containing names and addresses. I need to replace the house numbers of the addresses with asterisks-

35 John Road
891 Gray Street

Update to

* John Road
* Gray Street

How would I achieve this?

 

[chathag]

I have a table containing names and addresses. I need to replace the house numbers of the addresses with asterisks-

35 John Road
891 Gray Street

Update to

* John Road
* Gray Street

How would I achieve this using an update query?

 

[Atomic Shrimp]

You could do something like this with the 'allowonly' function I posted here:
http://www.access-programmers.co.uk/forums/showthread.php?t=12510&highlight=allowonly

The function doesn't replace things with a *, but you could concatenate one onto the start of your string after running it through the function, or just add one on dynamically when you display the value in your forms or reports.

I'd suggest choosing something other than an asterisk as the marker though, because the asterisk has meaning as a wildcard in Access SQL.

The other thing to note is that once you've performed the update query, there's no undo - the house numbers are gone from your table for good - are you sure that's what you want?

 

[DCrake]

You would need to use a simple function to do this. Intially I thought a simple Instr() looking for the first space but if the address did not start with a number then it would strip out the first word.

Public Function StripNumbers(AnyAddress As String) As String

Dim intSpace As Integer

'Step 1 - is there a number in the first character?

If IsNumeric(Left(AnyAddress,1)) = False Then
   StripNumbers = AnyAddress
   Exit Function
End If

'Step 2 - is there a space in the address

IntSpace = InStr(AnyAddress," ")

If IntSpace = 0 Then
   StripNumbers = AnyAddress
   ExitFunction
End If

'Prefix an asterisk to the main body of the address 
StripNumbers = "*" & Mid(AnyAddress,IntSpace)

End Function

Next create a query and add a column

Address:StripNumbers([YourAddressField])

You can then use this field to update your table.

David

 

[DCrake]

This question is a double post I answered in duplicate post. since then I have mergeed the posts and warned the user.