# Thread: A Function Question (A2K SR1)

1. ## 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

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

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

4. ## Re: A Function Question (A2K SR1)

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

This is available in 97???

Cecilia :-)

6. ## 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. ## 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.

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

8. ## 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. ## 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. ## 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.

11. ## 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.

12. ## 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. ## 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. ## 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')));

15. ## 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....

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 Last

#### Posting Permissions

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