If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Mid in query
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. |
#2
|
|||
|
|||
Mid in query
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 "DrEvil" wrote: 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. |
#3
|
|||
|
|||
Mid in query
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 -- Email address is not valid. Please reply to newsgroup only. "DrEvil" wrote: 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. |
#4
|
|||
|
|||
Mid in query
On Fri, 18 May 2007 08:35:27 -0700, DrEvil
wrote: 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] |
#5
|
|||
|
|||
Mid in query
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 "KARL DEWEY" wrote: 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 "DrEvil" wrote: 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. |
#6
|
|||
|
|||
Mid in query
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! "John W. Vinson" wrote: On Fri, 18 May 2007 08:35:27 -0700, DrEvil wrote: 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] |
Thread Tools | |
Display Modes | |
|
|