Page 1 of 2 12 LastLast
Results 1 to 15 of 23
  1. #1
    Silver Lounger GARYPSWANSON's Avatar
    Join Date
    Aug 2001
    Location
    Frederick, Maryland, USA
    Posts
    1,788
    Thanks
    0
    Thanked 2 Times in 2 Posts

    A Function Question (A2K SR1)

    The following function returns all letters of a string up to where the value of "-" may be present. For Example, if a string is e345bc-, the function returns e345bc. If a "-" does not exist, the the entire trimmed string is returned.

    However, I just noticed that some string values contain a literal *, that is the string can contain a "-" or "*".
    Is there an easy way to modify the function to evaluate for the "-" or "*" so it does the same thing. For example e345- or e345* would return e345?


    Function SubC(txt As String) As String
    Dim x As Integer
    Dim y As Integer

    'Test for "-" in string
    'If "-" Exists, Return String to space prior to "-", i.e., ABL- is ABL
    'If "-" does not exist, Get entire Trimmed String

    For x = 1 To Len(Trim(txt))

    If Mid(txt, x, 1) = "-" Then ' Does "-" exist
    y = x 'Set Y = to placeholder of "-"
    x = Len(txt) 'Set X to end of loop
    SubC = Left(txt, y - 1) 'Return SubC Value
    Exit For
    Else:
    SubC = Left(txt, x) ' If "-" does not exist, return trimmed txt

    End If
    Next x

    End Function
    Regards,

    Gary
    (It's been a while!)

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A Function Question (A2K SR1)

    I'm sure there are other methods, but if you replace

    If Mid(txt, x, 1) = "-" Then

    by

    If Mid(txt, x, 1) = "-" Or Mid(txt, x, 1) = "*" Then

    I think it'll do what you want.

  3. #3
    Gold Lounger
    Join Date
    Feb 2001
    Location
    Sint Niklaas, Belgium
    Posts
    2,778
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Function Question (A2K SR1)

    Why write a function when it exist in access ?
    <pre>Replace(Replace("YourString", "-", ""), "*", "")</pre>

    Francois

  4. #4
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Function Question (A2K SR1)

    How about
    If Mid(txt, x, 1) = "-" or Mid(txt,x,1)="*"

    Here is what I do if I want to strip a character out of a string, you might want to modify it....


    intPos = InStr(strMyString, "_")
    Do Until intPos = 0
    strMyString = Left$(strMyString, intPos - 1) & " " & Mid$(strMyString, intPos + 1)
    intPos = InStr(strMyString, "_")
    Loop

  5. #5
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Function Question (A2K SR1)

    Please tell me...

    This is available in 97???

    Cecilia :-)

  6. #6
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A Function Question (A2K SR1)

    Hi Francois,

    Gary's function doesn't replace "-", but truncates the string at the first occurrence of "-".
    So "Francois-Caron" becomes "Francois".
    My and Cecilia's suggestions do that; your function would return "FrancoisCaron".

    I suppose Gary can decide what he really needs.

    Regards,
    Hans

  7. #7
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A Function Question (A2K SR1)

    No, Replace doesn't exist in Access 97. If you do a search on this forum, you'll find code you can use in A97 instead of it.

    Link added later

    For example, <!post=Post 20455, 20455>Post 20455<!/post> by Charlotte.

  8. #8
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Function Question (A2K SR1)

    Actually, if all he wants is to truncate, this should work quickly and easily, then there's only two lines of code...

    If instr(strMyString,"-")>0 then strMyString=Left$(strMyString,instr(strMyString,"-")-1)
    If instr(strMyString,"*")>0 then strMyString=Left$(strMyString,instr(strMyString,"* ")-1)

    Cecilia :-)
    (Sign me: jealous of those with nifty built in functions like Replace)

  9. #9
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A Function Question (A2K SR1)

    Yes, that's much shorter <img src=/S/clapping.gif border=0 alt=clapping width=19 height=23>

  10. #10
    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: A Function Question (A2K SR1)

    Thanks everyone, however, close, but no cigar....

    The * causes the query that uses the function to provide an ambiguous name error message.
    I believe this is because * is a wildcard.
    I have tried, ="*", ="[*]" and other combinations but it still provides the error.

    Any other ideas?? In the meantime, I will look at the other suggestions.
    Regards,

    Gary
    (It's been a while!)

  11. #11
    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: A Function Question (A2K SR1)

    Cecilia,

    Wouldn't the "*" be treated as a wildcard and either produce an error or give the wrong answer??? That is what drove the original question.
    Regards,

    Gary
    (It's been a while!)

  12. #12
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts

    Re: A Function Question (A2K SR1)

    The function - in either of the forms suggested by Francois, Cecilia and me, will work in queries. I tested mine in expressions defining calculated fields and in criteria.

    Can you explain a bit more what you are trying to accomplish with the function?

  13. #13
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Function Question (A2K SR1)

    You could always use chr(42) instead of "*"

    If instr(strMyString,chr(42))>0 then strMyString=Left$(strMyString,instr(strMyString,ch r(42))-1)

    Look up Character Codes in Access Help.

    :-)

  14. #14
    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: A Function Question (A2K SR1)

    A query is extracting data from a table. One field being returned is the Name of the Subcontractor. Call the field Name which is a text field. The Name field may or may not contain a "-" or "*" within the name. If a "-" or "*" exists, then the field is truncated to one space prior to the "-" or "*".

    The function is being called in the query to modify the data. Of course this creates multiple records for the Subcontractor Name. I am eliminating the multiple records by selecting DISTINCT in sql. I am also eliminating any Subcontractors that start with a numeric digit. Ultimately, the query returns all distinct Subcontractor Names which will then be used as the record source for a pull down menu to allow a user to select a Subcontractor.

    The SQL behind the query is shown below:


    SELECT DISTINCT SubC([ResCode]) AS [MPM Sub Code]
    FROM [Resource Library]
    WHERE (((Left([ResCode],1)) Not In ('1','2','3','4','5','6','7','8','9','0')) AND ((Left([ResCode],3)) Not In ('AFE','G&A','FEE','FRI')));
    Regards,

    Gary
    (It's been a while!)

  15. #15
    3 Star Lounger
    Join Date
    Jan 2001
    Location
    San Francisco, California, USA
    Posts
    358
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: A Function Question (A2K SR1)

    This is what happens when you're in flat mode and everyone else is threaded, your mind becomes so boggled that you forget where you're replying....

    How about

    If instr(strMyString,chr(42))>0 then strMyString=Left$(strMyString,instr(strMyString,ch r(42))-1)

    THere's a list of Character Codes in Access Help.

    Cecilia :-)

Page 1 of 2 12 LastLast

Posting Permissions

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