Mid in query

[DrEvil]

Hi guys,
I feel like I'm posting something every day here.. In any case here is my
problem;
I'm importing table from Excel into Access and that works fine but I need to
do some cleaning up before I start comparing imported cases with currently
active cases, and my problems is in cleaning up cases. I would like to remove
suffix from the case if case has a suffix. Cases could be formatted like this;
22903-02296-01 , 22923-03219 , 0622-AC00441-01 , 0622-AC08276 , 22023-06047-05
What I need to do is just trim the suffix away, -01 , -02 , 03 etc. on the
end.. Hope this is clear enough. In my query I only have field called CaseNo
SELECT tblImport.CaseNo
FROM tblImport;

I'm doing same thing in form as case is scanned in (one at a time) using
this code;
Dim strlen As Integer
strlen = Len(CaseNo)
If Mid(CaseNo, (strlen - 2), 1) = "-" Then
Me.CaseNo = Left(CaseNo, strlen - 3)

But I need to do this en mass using update query (unless there is some
better way) for couple of hundred cases at time. This would be daily function.

 

[KARL DEWEY]

Looking at the sample data you posted you seem to have two types od case
numbers - 4 characters with a dash and then 6 characters - 5 characters with
a dash and then 5 characters. Your suffixes always are 2 characters.
If this is true for all your data then it will be easy. Add a field in your
table for suffix.
Run an update query with this criteria -
WHERE Len([caseno]) > 11
Update [caseno] as Left([caseno] ,11)
Update [suffix] as Right([caseno] ,2)

 

[Dale Fye]

If they are not all formatted the way Karl described, but you can identify
the ones with a suffix as the ones that have two hyphens, you could write
your query like:

UPDATE yourTable
SET [CaseNo] = LEFT([CaseNo], instrrev([CaseNo], "-") - 1)
WHERE LEN([CaseNo]) - LEN(Replace([CaseNo], "-", "")) = 2

The Where clause limits the result set to those records that have two
hyphens in the [CaseNo] field, and the INSTRREV( ) function find the position
of the last hyphen in the [CaseNo] field

HTH
Dale

 

[John W. Vinson]

Hi guys,
I feel like I'm posting something every day here.. In any case here is my
problem;
I'm importing table from Excel into Access and that works fine but I need to
do some cleaning up before I start comparing imported cases with currently
active cases, and my problems is in cleaning up cases. I would like to remove
suffix from the case if case has a suffix. Cases could be formatted like this;
22903-02296-01 , 22923-03219 , 0622-AC00441-01 , 0622-AC08276 , 22023-06047-05
What I need to do is just trim the suffix away, -01 , -02 , 03 etc. on the
end.. Hope this is clear enough. In my query I only have field called CaseNo
SELECT tblImport.CaseNo
FROM tblImport;

I'm doing same thing in form as case is scanned in (one at a time) using
this code;
Dim strlen As Integer
strlen = Len(CaseNo)
If Mid(CaseNo, (strlen - 2), 1) = "-" Then
Me.CaseNo = Left(CaseNo, strlen - 3)

But I need to do this en mass using update query (unless there is some
better way) for couple of hundred cases at time. This would be daily function.

UPDATE Cases
SET CaseNo = Left([CaseNo], Len([CaseNo]) - 3
WHERE CaseNo LIKE "*-##";

This will search for CaseNo fields which end in a hyphen followed by two
numeric digits, and update it to discard that suffix.

John W. Vinson [MVP]

 

[DrEvil]

Thanx Karl,
I'm not sure if I did placed your code properly but it seems to give me
right results.. here is what I have;
UPDATE tblImport SET tblImport.CaseNo = Left([caseno],11)
WHERE (((Len([caseno]))>"11
"));
Also I didn't include right trim Update [suffix] as Right([caseno] ,2) ,
I don't really need these suffixes I just wanted to remove them totally from
the table... not sure if that was the purpose of the right trim.
Take care

Looking at the sample data you posted you seem to have two types od case
numbers - 4 characters with a dash and then 6 characters - 5 characters with
a dash and then 5 characters. Your suffixes always are 2 characters.
If this is true for all your data then it will be easy. Add a field in your
table for suffix.
Run an update query with this criteria -
WHERE Len([caseno]) > 11
Update [caseno] as Left([caseno] ,11)
Update [suffix] as Right([caseno] ,2)

--
KARL DEWEY
Build a little - Test a little

Hi guys,
I feel like I'm posting something every day here.. In any case here is my
problem;
I'm importing table from Excel into Access and that works fine but I need to
do some cleaning up before I start comparing imported cases with currently
active cases, and my problems is in cleaning up cases. I would like to remove
suffix from the case if case has a suffix. Cases could be formatted like this;
22903-02296-01 , 22923-03219 , 0622-AC00441-01 , 0622-AC08276 , 22023-06047-05
What I need to do is just trim the suffix away, -01 , -02 , 03 etc. on the
end.. Hope this is clear enough. In my query I only have field called CaseNo
SELECT tblImport.CaseNo
FROM tblImport;

I'm doing same thing in form as case is scanned in (one at a time) using
this code;
Dim strlen As Integer
strlen = Len(CaseNo)
If Mid(CaseNo, (strlen - 2), 1) = "-" Then
Me.CaseNo = Left(CaseNo, strlen - 3)

But I need to do this en mass using update query (unless there is some
better way) for couple of hundred cases at time. This would be daily function.

 

[DrEvil]

WoW, such a great response,
Thanx guys I really liked Dale's and John's way of just looking for that
part of the string and not even looking at the length of the case,,, I guess
you never know with large number of cases, anything is possible.
Thank you guys, you all rock!

Hi guys,
I feel like I'm posting something every day here.. In any case here is my
problem;
I'm importing table from Excel into Access and that works fine but I need to
do some cleaning up before I start comparing imported cases with currently
active cases, and my problems is in cleaning up cases. I would like to remove
suffix from the case if case has a suffix. Cases could be formatted like this;
22903-02296-01 , 22923-03219 , 0622-AC00441-01 , 0622-AC08276 , 22023-06047-05
What I need to do is just trim the suffix away, -01 , -02 , 03 etc. on the
end.. Hope this is clear enough. In my query I only have field called CaseNo
SELECT tblImport.CaseNo
FROM tblImport;

I'm doing same thing in form as case is scanned in (one at a time) using
this code;
Dim strlen As Integer
strlen = Len(CaseNo)
If Mid(CaseNo, (strlen - 2), 1) = "-" Then
Me.CaseNo = Left(CaseNo, strlen - 3)

But I need to do this en mass using update query (unless there is some
better way) for couple of hundred cases at time. This would be daily function.

UPDATE Cases
SET CaseNo = Left([CaseNo], Len([CaseNo]) - 3
WHERE CaseNo LIKE "*-##";

This will search for CaseNo fields which end in a hyphen followed by two
numeric digits, and update it to discard that suffix.

John W. Vinson [MVP]