Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    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

  2. #2
    Star Lounger
    Join Date
    Aug 2001
    Location
    Seattle, Washington, USA
    Posts
    50
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Randy,

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

    HTH,

    Tom

  3. #3
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

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

    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
    Regards,

    Gary
    (It's been a while!)

  4. #4
    Star Lounger
    Join Date
    Feb 2002
    Posts
    74
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

    Thanks for all of the help!

  5. #5
    Star Lounger
    Join Date
    Dec 2001
    Location
    Fredensborg, Denmark
    Posts
    86
    Thanks
    0
    Thanked 0 Times in 0 Posts

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

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

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

  6. #6
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

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

    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?
    Regards,

    Gary
    (It's been a while!)

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •