That darned ***asterisk*** !!! (All )

Topic

New Reply

I had a user call me this morning with what I believed to be a simple question – “how do I search for (and eliminate) asterisks in Access records?” I remembered that somewhere I used [*] to find asterisks (I thought in Access) but this didn’t work. I did find some solutions in code in this forum, and did learn something new, that ~* will find asterisks in Excel, but isn’t there an equally simple method for Access? If not, I wonder why the **** not

Thanks,

Randy

Reply | Quote

Replies

Randy,

It appears there is a way. Well, at least three ways. Please see the KB article here for more.

HTH,

Tom

Reply | Quote

The following function will remove asterisks:

– Create a Module (I called it Asterisk)
– Copy the below code to the module and save it
– Create a query that has the fields with an Asterisk in them. Put SubC(Field) as the data and run the query. The Asterisks will be removed.

HTH

———————————–

Function SubC(txt As String) As String

‘The following function removes all asterisks from fields
‘Programmed by G. Swanson – 9/13/2002

Dim x As Integer ‘ Use to set to length of string being evaluated
Dim temp As String ‘ Use to store field while removing asterisks

‘Test for “[*]” in string

For x = 1 To Len(Trim(txt)) ‘ X is set to the length of the field being evaluated
If Not Mid(txt, x, 1) Like “[*]” Then ‘ If the character is not equal to * then build temp
temp = temp & Mid(txt, x, 1)
Else: ‘ Do Nothing
End If
Next x

‘Reset the value of SubC to be passed to the query
SubC = temp

‘Note – If all data is ***** then function returns a null value

End Function

Reply | Quote

Thanks for all of the help!

Reply | Quote

From A2K and up you can use the builtin Replace function, e.g.

Replace(“aaaa***xxx*yyy'”,”*”,””) evaluates to “aaaaxxxyyy”

Reply | Quote

Thats interesting. I receive an error message when using the replace function as you did using the “*” in A2k.

[*], “[*]”, and every other combination I could think of doesn’t work either. Any ideas on why the replace function on my machine does not work?

Reply | Quote