A Function Question (A2K SR1)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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.

Reply | Quote

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?

Reply | Quote

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’)));

Reply | Quote

Gary,

I created a table with a field ResCode, and entered a lot of values, with repetitions, starting with a digit or not, “AFE” etc. or not, and containing “*” and “-” or not, randomly.

Your SQL seems to do exactly what you want: filter out names starting with a digit or “AFE” etc., and truncating strings containing “-” or “*”, and weeding out duplicates in what remains. No complaints about “*”.

I have to exit the Lounge for today. Hopefully somebody else will be able to help you.

Regards,
Hans

Reply | Quote

Thanks for you help. I will try to determine why I am getting the ambiguous message.

Reply | Quote

Gary,
Is this working for you ?

Function SubC(strMyString As String) As String
If InStr(strMyString, "-") > 0 Then strMyString = Left$(strMyString, InStr(strMyString, "-") - 1)
If InStr(strMyString, "*") > 0 Then strMyString = Left$(strMyString, InStr(strMyString, "*") - 1)
SubC = strMyString
End Function

I have test it on some data but I have not your table.
The query was the same of your

Reply | Quote

Francois,

Thanks, Works like a charm.

Now, If only I could figure out why the other function does not work…..

Reply | Quote

This time it was the right answer on the right post

If you post the code of the other function like you have it now, I’ll take a look at it.

Reply | Quote

Actually, I just checked the code from your original post & it works just fine:

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) = “-” Or 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

Reply | Quote

That’s odd…. I get an error message on my machine…

However, I have converted to the function that works and will press onward…..

Thanks again.

Reply | Quote

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,chr(42))-1)

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

Cecilia

Reply | Quote

Why write a function when it exist in access ?

Replace(Replace("YourString", "-", ""), "*", "")

Reply | Quote

Please tell me…

This is available in 97???

Cecilia

Reply | Quote

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 20455 by Charlotte.

Reply | Quote

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

Reply | Quote

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)

Reply | Quote

Yes, that’s much shorter

Reply | Quote

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.

Reply | Quote

You could always use chr(42) instead of “*”

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

Look up Character Codes in Access Help.

Reply | Quote

Thanks Cecilia,

I put this in a function and it works great.

Reply | Quote

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

Reply | Quote