Search for and remove an asterisk? (1 Viewer)

[webmeister]

Is there a way in a query to search for an asterisk (*) in a field and if one is found, to then remove it?

I'm aware the asterisk is a wildcard symbol in Access, which is why I'm asking what I'm asking. This dang file I received has data in a field and the last character can be an asterisk at times.

Thanks so much in advance!! Looking forward toyour replies!!!!

 

[KenHigg]

Not the most elegant but how about:

Replace([MyText],Chr(42),"")

 

[alastair69]

Not the most elegant but how about:

Replace([MyText],Chr(42),"")

Sorry for myt stupidity but what does chr(42) mean

 

[ByteMyzer]

chr(42) represents the ASCII (American Standard Code for Information Interchange) character 42, or the * symbol.

 

[webmeister]

Ken,

Thanks for your "not so elegant" suggestion !! Certainly a lot shorter than what I worked with!!

I wrote a bunch of code and then called the code from a query. Here's the code:

Public Function sHead(ByVal psString As String, ByVal psDelimiter As String) As String

On Error GoTo sHeadError

sHead$ = Trim$(Left$(psString$ & psDelimiter, InStr(psString$ & psDelimiter$, psDelimiter$) - 1))

sHeadExit:
DoEvents
On Error GoTo 0
Exit Function

sHeadError:

Resume sHeadExit
Resume

End Function

.....and then in my query, I had something like this:

Update Fieldname to sHead(FieldName,"*")

..... which in effect would "chop" the field at one position before the actual asterisk.

Here is the actual SQL:

UPDATE MyTable SET MyTable.FieldName = sHead([FieldName],"*");


Mine worked, but yours sure takes a lot less effort!!

 

[KenHigg]

Cool - Glad you got it working!