Right and Left function

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a field that has different length numbers entered. If the length is =
32 then I need the program to get rid of the first 16 digits and the last 4
digits. If the length is different then 32 then follow the validation rule.
The code below is what I tried, but it didn't work. What am I doing wrong?

I could setup the validation in the code. What is better?

---------This is the validation rule---------
Like "1Z????????????????" Or Like "???????????" Or Like "????????????" Or
Like "??????????????????????" Or Like "????" Or Like "????????????????"
------------end validation rule--------------

--------start code-----------
Private Sub TrackingNo_BeforeUpdate(Cancel As Integer)
Dim strRTTrackNo As String
Dim strFinalTrackNo As String

If Len(Me.TrackingNo) = 32 Then
Right(Me.TrackingNo, 4) = strRTTrackNo
Left(strRTTrackNo, 16) = strFinalTrackNo
Me.TrackingNo = strFinalTrackNo
End If

End Sub
-------------end code------------
 
I think this is what you want:

If Len(Me.TrackingNo) = 32 Then
Me.TrackingNo = Mid(Me.TrackingNo, 16, 12)
End If
Ie., you want 12 characters rom the string starting with the 18th character.
 
Dim strRTTrackNo As String
Dim strFinalTrackNo As String
Dim TrackingNo As String

TrackingNo = "12345678901234567890123456789012"
If Len(TrackingNo) = 32 Then
strFinalTrackNo = Mid(TrackingNo, 17, Len(TrackingNo) - 20)
'20 is 16 + 4 so mid will leave out the last 4
TrackingNo = strFinalTrackNo
End If

so your code should look like this:

Private Sub TrackingNo_BeforeUpdate(Cancel As Integer)
Dim strRTTrackNo As String
Dim strFinalTrackNo As String

If Len(Me.TrackingNo) = 32 Then
strFinalTrackNo = Mid(Me.TrackingNo, 17, Len(Me.TrackingNo) - 20)
'20 is 16 + 4 so mid will leave out the last 4
Me.TrackingNo = strFinalTrackNo
End If

good luck!
 
Thank you for your help. It gets the correct digits, but there is an error
when it tries to save the string. I have even gotten rid of the validation
rule that I previously had entered. This is the error:

Run-time error' -2147352567 (80020009)':

The macro or function set to the BeforeUpdate or Validation Rule property
for this field is preventing PackageLog 2005 from saving the data in the
field.
 
Back
Top