Extracting Characters

[Guest]

The following function 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 -

 

[Ken Snell [MVP]]

That function allows i to go to 0. Mid will choke if you tell it to start at
the 0 location in the text string. Change the loop to end at 1, not 0.

Please provide more details about the #Error issue. Field? or control? shows
this error? What is the control source of the control if it is a control?

--

Ken Snell
<MS ACCESS MVP>

The following function 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 -

 

[John Vinson]

The following function 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.

I'd write the Dim statements a bit differently: try

Function GetJunk(strCPart As String) As String
Dim strJunk As String
Dim i As Integer
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

If you have Access 2000 or later, there's a InStrRev function which
finds the *last* instance of a character (just as InStr finds the
first): in a Query containing a field CPart you could put

Junk: Left([CPart], InStrRev([CPart], "-") - 1)

with no code at all.

John W. Vinson[MVP]

 

[Guest]

Changing the loop to and at 1 got rid of the error messages. It was fields
in the query that contained only one dash after some characters like 9874- or
69- or 9824- that were returning #error. After changing the loop to and at 1
those fields are now blank, which will work for me.

Now I have to create another expression in a new query column that will
return only charcters leading up to the second to last -. For example:

Cpart
5977-9325-48-693-

The first function you helped me with will return 5977-9325-48-
I need a new function to return 5977-9325-

Thank you for your help. Writing functions is so foreign to me. I need to
take a class or read a book or something to get me started. Thanks again.

That function allows i to go to 0. Mid will choke if you tell it to start at
the 0 location in the text string. Change the loop to end at 1, not 0.

Please provide more details about the #Error issue. Field? or control? shows
this error? What is the control source of the control if it is a control?

--

Ken Snell
<MS ACCESS MVP>

The following function 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 -

 

[Guest]

Duh - I just copied and pasted GetJunk and changed a few names and was able
to capture the new data I need. Thank you

Changing the loop to and at 1 got rid of the error messages. It was fields
in the query that contained only one dash after some characters like 9874- or
69- or 9824- that were returning #error. After changing the loop to and at 1
those fields are now blank, which will work for me.

Now I have to create another expression in a new query column that will
return only charcters leading up to the second to last -. For example:

Cpart
5977-9325-48-693-

The first function you helped me with will return 5977-9325-48-
I need a new function to return 5977-9325-

Thank you for your help. Writing functions is so foreign to me. I need to
take a class or read a book or something to get me started. Thanks again.

That function allows i to go to 0. Mid will choke if you tell it to start at
the 0 location in the text string. Change the loop to end at 1, not 0.

Please provide more details about the #Error issue. Field? or control? shows
this error? What is the control source of the control if it is a control?

--

Ken Snell
<MS ACCESS MVP>

The following function 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 -