Results 1 to 4 of 4
  1. #1
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    Edmonton, Alberta, Canada
    Posts
    326
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does any one know how to search for the Asterisk?

    According to the online help (search for help on Like Operator), you need to put square brackets around the asterisk, i.e. Like "[*]".

  2. #2
    2 Star Lounger
    Join Date
    Apr 2001
    Location
    Wellington, New Zealand
    Posts
    173
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Does any one know how to search for the Asterisk?

    My solution may not be the most elegant, but it works

    1. In the table that contains the data create a new 'yes/no' field (called it something like 'FLAG')

    2. Create a new module

    3. Add the following Code to the module (replace the <your data table name>, <your flag field name> and <your search field name> with the names of the table you wish to search and the field you wish to search and use as the flag.

    4. Run this routine. VIOLA, all the fields that have an * at the end of them are CHECKED TRUE in the flag field!

    Sub SearchAst()
    Dim rstSearchTable As Recordset
    Dim strFieldSearch As String
    Dim strFieldFlag As String

    Set rstSearchTable = CurrentDb.OpenRecordset("<your data table name>", dbopentable)
    strFieldSearch = "<your search field name>"
    strFieldFlag = "<your flag field name>"

    With rstSearchTable
    Do While Not .EOF
    If Right(.Fields(strFieldSearch), 1) = "*" Then
    .edit
    .Fields(strFieldFlag) = True
    .Update
    End If
    .MoveNext
    Loop
    End With
    End Sub

  3. #3
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Does any one know how to search for the Asterisk?

    Here's a routine you could use to clean up those offending values:

    <pre>Public Function ExtractNumFromStr(ByVal strIn As String, _
    Optional intStartPos As Integer) As Variant
    'Created 2/16/2000 by Charlotte Foust
    On Error GoTo ExtractNum_err
    Dim varNum As Variant
    Dim strNum As String
    Dim strTemp As String
    Dim intLoop As Integer

    ' Get the number portion of the string

    'if they didn't pass in a start position
    'start at position 1

    If intStartPos = 0 Then
    intStartPos = 1
    End If ' intStartPos = 0

    'grab the portion of the string to use
    strTemp = Right(strIn, 1 + Len(strIn) - intStartPos)

    'if the string is all numeric
    'return the whole string

    If IsNumeric(strTemp) Then
    strNum = strTemp

    'otherwise, extract the numbers
    'from the string

    Else 'IsNumeric(strTemp)

    'step through the string and test
    'each character, only returning the numbers

    For intLoop = intStartPos To Len(strIn)

    If IsNumeric(Mid(strIn, intLoop, 1)) Then

    strNum = strNum & Mid(strIn, intLoop, 1)
    End If 'IsNumeric(Mid(strIn, intLoop, 1))
    Next intLoop ' = intStartPos To Len(strIn)

    'if no numbers were found, return zero
    If Len(strNum) = 0 Then

    strNum = "0"
    End If 'Len(strNum) = 0
    End If 'IsNumeric(strTemp)

    varNum = clng(strNum)
    ExtractNum_exit:
    On Error Resume Next
    ExtractNumFromStr = varNum
    Exit Function
    ExtractNum_err:
    Resume Next
    End Function 'ExtractNumFromStr(ByVal strIn As String, _
    Optional intStartPos As Integer) As Variant
    </pre>

    Charlotte

  4. #4
    roberte
    Guest

    Does any one know how to search for the Asterisk?

    Here is my problem:
    I received a database extract from a mainframe system containing a large (800,000) set of records. The format for all of the columns is text.

    However some of the columns (currency) contain non-standard ASCII characters. (-, *) in the format (314.15* ) or (1234.54-) .

    I need to create a query that flags all of these so that they can be manually edited before we put the database into a different production system.

    My problem is that when I try to search for the asterisk character

Posting Permissions

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