Me and Mid

[Guest]

Hi guys,
I'm having some issues with Mid, I was trying to remove 22 from cases that
start like this 22###-* , I have query that is already doing this but I
would like to remove it as the user enters case number and not after.
These cases usually look like this 22060-01531-01 , or 22060-01531a, or
22060-01531 or 22060-01531a-04 but they also could be like this 060-2350a or
060-2350 or 0622-CC00215 or 0622-AC00218-03
What I do now is strip suffix, like -01, -02 etc. on the end using this:
Dim strlen As Integer
strlen = Len(CaseNo)
If Mid(CaseNo, (strlen - 2), 1) = "-" Then
Me.CaseNo = Left(CaseNo, strlen - 3)
End If
This works fine stripping suffix just as the case is being entered; I need
to be able to do the same thing with removing 22 in front.
I have query that will do the same but I need to do it within form;
UPDATE tblfileloc SET tblfileloc.CaseNo = Mid([Caseno],3)
WHERE (((tblfileloc.CaseNo) Like "22###-*"));

I guess I'm having truble declaring this 22###-* since it will never pick it
up, here is the code I have;
If Me.CaseNo.Value = "22###-*" Then
CaseNo = Mid(CaseNo, 3)
End If
It seems to strip first two characters if I remove IF statement...but I need
IF to work.

Thank you

 

[Rob Parker]

If Left(Me.CaseNo,2) = "22" Then ...

HTH,

Rob

 

[Marshall Barton]

I'm having some issues with Mid, I was trying to remove 22 from cases that
start like this 22###-* , I have query that is already doing this but I
would like to remove it as the user enters case number and not after.
These cases usually look like this 22060-01531-01 , or 22060-01531a, or
22060-01531 or 22060-01531a-04 but they also could be like this 060-2350a or
060-2350 or 0622-CC00215 or 0622-AC00218-03
What I do now is strip suffix, like -01, -02 etc. on the end using this:
Dim strlen As Integer
strlen = Len(CaseNo)
If Mid(CaseNo, (strlen - 2), 1) = "-" Then
Me.CaseNo = Left(CaseNo, strlen - 3)
End If
This works fine stripping suffix just as the case is being entered; I need
to be able to do the same thing with removing 22 in front.
I have query that will do the same but I need to do it within form;
UPDATE tblfileloc SET tblfileloc.CaseNo = Mid([Caseno],3)
WHERE (((tblfileloc.CaseNo) Like "22###-*"));

I guess I'm having truble declaring this 22###-* since it will never pick it
up, here is the code I have;
If Me.CaseNo.Value = "22###-*" Then
CaseNo = Mid(CaseNo, 3)
End If

If you are using wildcards,, you must use LIKE instead of =

If Me.CaseNo.Value Like "22###-*" Then

 

[Guest]

Thanx guys,
They both work just great, and as always I really appreciate your help and
your diligence to help others in need.

I'm having some issues with Mid, I was trying to remove 22 from cases that
start like this 22###-* , I have query that is already doing this but I
would like to remove it as the user enters case number and not after.
These cases usually look like this 22060-01531-01 , or 22060-01531a, or
22060-01531 or 22060-01531a-04 but they also could be like this 060-2350a or
060-2350 or 0622-CC00215 or 0622-AC00218-03
What I do now is strip suffix, like -01, -02 etc. on the end using this:
Dim strlen As Integer
strlen = Len(CaseNo)
If Mid(CaseNo, (strlen - 2), 1) = "-" Then
Me.CaseNo = Left(CaseNo, strlen - 3)
End If
This works fine stripping suffix just as the case is being entered; I need
to be able to do the same thing with removing 22 in front.
I have query that will do the same but I need to do it within form;
UPDATE tblfileloc SET tblfileloc.CaseNo = Mid([Caseno],3)
WHERE (((tblfileloc.CaseNo) Like "22###-*"));

I guess I'm having truble declaring this 22###-* since it will never pick it
up, here is the code I have;
If Me.CaseNo.Value = "22###-*" Then
CaseNo = Mid(CaseNo, 3)
End If

If you are using wildcards,, you must use LIKE instead of =

If Me.CaseNo.Value Like "22###-*" Then