Extracting characters

[Guest]

I have fields containing the following examples:

Cpart junk
126-698-95P-X87- 126-689-95P-
126-698-95P- 126-689-

I have the following expression in my query:
BestStatusDate: IIf([Status]="6",DLookUp("[LTDate]","QueryA","[CPart] = '" &
[junk] & "' AND [Status] = '1'"),[LTDate])

The expression in the field "junk" is: junk: Left([CPart],Len([CPart])-4)

This works great as long as CPart has 3 characters then a dash, but a lot of
my CPart fields have a different number of characters between the dashes like
569-98-99S-6I66-

I'm looking for a way to rewrite the expression in the "junk" field that
will extract the last dash and all the characters before the previous dash OR
maybe there's a way to rewrite my BestStatusDate expression without using the
"junk" field and using only CPart.

Thank you. I appreciate any help you can give -

 

[margaret bartley]

I would write a VBA function, and call that.

So the expression junk would be
Junk: GetJunk(Cpart)

and GetJunk would be a function that starts at the end, and moves to the
front until it gets the penultimate dash.


Function GetJunk(strCPart$) As String
Dim strJunk$, i%
For i = Len(strCPart) - 1 To 0 Step -1
If Mid(strCPart, i, 1) = "-" Then Exit For
Next i

If i > 0 Then
strJunk = Left(strCPart, i)
Else
strJunk = ""
End If
GetJunk = strJunk
End Function

 

[Guest]

Thanks so much Margaret -

This works pretty well except that for every record in the query I get the
error message "Invalid Procedure call or argument. When I debug, it
highlights If Mid(strCPart, i, 1) = "-" Then.

Also, for the fields that only contain data to the left of a dash (like 999-
or 7654-) I'm getting #error in the field. Is there a way to write the
function that will leave it null?

I'm so new to writing functions. I know I've written many query expressions
that are better suited for functions, but I'm just not sure what I'm doing
yet. Thanks for you help.

I would write a VBA function, and call that.

So the expression junk would be
Junk: GetJunk(Cpart)

and GetJunk would be a function that starts at the end, and moves to the
front until it gets the penultimate dash.


Function GetJunk(strCPart$) As String
Dim strJunk$, i%
For i = Len(strCPart) - 1 To 0 Step -1
If Mid(strCPart, i, 1) = "-" Then Exit For
Next i

If i > 0 Then
strJunk = Left(strCPart, i)
Else
strJunk = ""
End If
GetJunk = strJunk
End Function

I have fields containing the following examples:

Cpart junk
126-698-95P-X87- 126-689-95P-
126-698-95P- 126-689-

I have the following expression in my query:
BestStatusDate: IIf([Status]="6",DLookUp("[LTDate]","QueryA","[CPart] = '" &
[junk] & "' AND [Status] = '1'"),[LTDate])

The expression in the field "junk" is: junk: Left([CPart],Len([CPart])-4)

This works great as long as CPart has 3 characters then a dash, but a lot of
my CPart fields have a different number of characters between the dashes like
569-98-99S-6I66-

I'm looking for a way to rewrite the expression in the "junk" field that
will extract the last dash and all the characters before the previous dash OR
maybe there's a way to rewrite my BestStatusDate expression without using the
"junk" field and using only CPart.

Thank you. I appreciate any help you can give -