Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

Return characters after the second-to-last period in a string using query

Posted on 2003-11-18
26
Medium Priority
?
551 Views
Last Modified: 2007-12-19
Hello,

In variable length field, extract characters after the second to last period "." using query function.

For example, in a set of records like:

a.bc.defg.xyz
c.cd.rty
abc.cba.fdse.fdse

I would return:

defg.xyz
cd.rty
fdse.fdse

Thank you.

0
Comment
Question by:jsmith1k
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 5
  • +4
26 Comments
 
LVL 28

Expert Comment

by:TextReport
ID: 9776675
In Access 2000 you can use the InstrRev function. in the example below replace ("a.bc.defg.xyz" with your field name

mid("a.bc.defg.xyz", instrrev("a.bc.defg.xyz", ".", instrrev("a.bc.defg.xyz", ".")-1)+1)

Cheers, Andrew
0
 

Author Comment

by:jsmith1k
ID: 9776918
Hi Andrew,

I have Access 2K but I'm getting "undefined function" message.  Any ideas?

Thanks.
0
 
LVL 8

Expert Comment

by:Benjamin_Luk
ID: 9777225
Put following code into your modle:

Function GetLast()
Dim rs As Recordset, I As Integer, K As Integer, myStr As String, J As Integer, L As Integer
Set rs = CurrentDb.OpenRecordset("myTable")
If rs.RecordCount < 1 Then
Set rs = Nothing
Exit Function
Else
rs.MoveFirst
Do Until rs.EOF
'Get the last . position
K = 0
For I = 1 To Len(rs("myField")) - 1
If InStr(I, rs("myfield"), ".", 1) > 0 Then
K = K + 1
End If
Next I
'Get the second last . position
J = 0
L = 0
For J = 1 To K - 1
If InStr(J, Left(rs("myfield"), (K - 1)), ".", 1) > 0 Then
L = L + 1
End If
Next J
MsgBox Right(rs("myfield"), Len(rs("myfield")) - L)
rs.MoveNext
Loop
End If
End Function
Public Function GetStr(myStr As String) As String
Dim I As Integer, K As Integer, J As Integer, L As Integer
'Get the last . position
K = 0
For I = 1 To Len(myStr) - 1
If InStr(I, myStr, ".", 1) > 0 Then
K = K + 1
End If
Next I
'Get the second last . position
J = 0
L = 0
For J = 1 To K - 1
If InStr(J, Left(myStr, (K - 1)), ".", 1) > 0 Then
L = L + 1
End If
Next J
GetStr = Right(myStr, Len(myStr) - L)
End Function

Then you can use GetSTR([FIELD]) to get the string, like SQL:
SELECT myTable.MyField, GetStr([myTable]![MyField]) AS NewValue
FROM myTable;

Testing Passed

Regards

Ben
0
 [eBook] Windows Nano Server

Download this FREE eBook and learn all you need to get started with Windows Nano Server, including deployment options, remote management
and troubleshooting tips and tricks

 
LVL 8

Expert Comment

by:Benjamin_Luk
ID: 9777241
Sorry , it should be this:
Public Function GetStr(myStr As String) As String
Dim I As Integer, K As Integer, J As Integer, L As Integer
'Get the last . position
K = 0
For I = 1 To Len(myStr) - 1
If InStr(I, myStr, ".", 1) > 0 Then
K = K + 1
End If
Next I
'Get the second last . position
J = 0
L = 0
For J = 1 To K - 1
If InStr(J, Left(myStr, (K - 1)), ".", 1) > 0 Then
L = L + 1
End If
Next J
GetStr = Right(myStr, Len(myStr) - L)
End Function

Then you can use GetSTR([FIELD]) to get the string, like SQL:
SELECT myTable.MyField, GetStr([myTable]![MyField]) AS NewValue
FROM myTable;

Testing Passed

Regards

Ben
0
 
LVL 2

Expert Comment

by:Psyclones
ID: 9777251
You need to add it into the SQL of the table, similar to below.

in the example below replace ("a.bc.defg.xyz" with your field name) and Table1 with your table name

SELECT mid("a.bc.defg.xyz", instrrev("a.bc.defg.xyz", ".", instrrev("a.bc.defg.xyz", ".")-1)+1), *
FROM table1;

Hope this helps

Points to andrew if it does as he answered it,

I just added to his comments

hope this helps
Psyclones
0
 
LVL 7

Accepted Solution

by:
wsteegmans earned 1000 total points
ID: 9777316
jsmith1k,

The function GetLastParts (code below) has this functionality:
Suppose we have this string: 111.222.333.444.555

The function has three parameters (string, delimiter, count). I show some examples and the result:
  GetLastParts("111.222.333.444.555", ".", 0) ->
  GetLastParts("111.222.333.444.555", ".", 1) -> 555
  GetLastParts("111.222.333.444.555", ".", 2) -> 444.555
  GetLastParts("111.222.333.444.555", ".", 3) -> 333.444.555
  GetLastParts("111.222.333.444.555", ".", 4) -> 222.333.444.555
  GetLastParts("111.222.333.444.555", ".", 5) -> 111.222.333.444.555

The count parameter defines how many parts you want ... The other parameters are clear, I suppose.

You can also use this function in a query, something like this:
SELECT Field1,Field2, GetLastParts([Field3], ".", 2) FROM MyTable

Just create an new module (or use an existing one) in Access and past the code below ...

Public Function GetLastParts(strValue As String, Delimiter As String, Count As Integer) As String

    GetLastParts = GetLastPartsEx(strValue, Delimiter, Count, Count)

End Function

Private Function GetLastPartsEx(strValue As String, Delimiter As String, Count As Integer, InitCount As Integer) As String
   
    Dim strResult As String
    Dim intPos As Integer
    Dim strNewValue As String
   
    intPos = InStrRev(strValue, ".")
    If Count <> 0 Then
        If (intPos > 0) Then
            strNewValue = Left(strValue, intPos - 1)
            strResult = GetLastPartsEx(strNewValue, Delimiter, Count - 1, InitCount) & Mid(strValue, intPos) & strResult
        Else
            strResult = strValue
        End If
    End If
   
    If (Count = InitCount) And (Left(strResult, 1) = ".") Then
        GetLastPartsEx = Mid(strResult, 2)
    Else
        GetLastPartsEx = strResult
    End If
       
End Function
0
 
LVL 39

Expert Comment

by:stevbe
ID: 9778491
couple of optimizations ....

Although the function provides a delimiter argument it was still looking for hardcoded "." in some places.
Functions that have string function equivalents ... the string version is much faster... Left$ vs. Left
intPos is only ever used inside your first IF block so moving it there avoids having to process if not necessary.
InStrRev returns a Long so assigning it to an Integer forces the system to convert and Mid$ and Left$ also use Long datatype in their argumants so you would be forcing it to convert back again... changed intPos to lngPos

Normally you would not notice a processing speed difference but because you are suggesting that this function be called to create a field value on a query I think it is worth the effort to tighten up as much as possible.

Private Function GetLastPartsEx(strValue As String, Delimiter As String, Count As Integer, InitCount As Integer) As String
   
    Dim strResult As String
    Dim lngPos As Long
    Dim strNewValue As String
   
    If Count <> 0 Then
        lngPos = InStrRev(strValue, Delimiter )
        If (lngPos > 0) Then
            strNewValue = Left$(strValue, lngPos - 1)
            strResult = GetLastPartsEx(strNewValue, Delimiter, Count - 1, InitCount) & Mid$(strValue, lngPos) & strResult
        Else
            strResult = strValue
        End If
    End If
   
    If (Count = InitCount) And (Left$(strResult, 1) = Delimiter ) Then
        GetLastPartsEx = Mid$(strResult, 2)
    Else
        GetLastPartsEx = strResult
    End If
       
End Function

Steve
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9779006
stevbe,

Thx for your optimizations ... If you want really want to speed up your Function, indeed try to minimize the data converts ...

I think I wrote it a little bit toooo fast ;-)

Maybe another question for you when comparing strings ... What is the best way to do it, just
If (str1 = str2) Then
or is the StrComp better or faster?
0
 

Author Comment

by:jsmith1k
ID: 9780783
Benjamin_Luk,

I'm a neophyte when it comes to modules, so please bear with me.  I cut and pasted your code into a module, then created a query.  When I run the query, I get "undefined function getstr in expression".  What did I do wrong?

Thanks.
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9780955
- You took the latest response from Benjamin_Luk? Because his first answer was about a different Function ...
- Be sure you dropped it in a Access-Module (tab Modules), and  not in a Form-Module ...
- Check if the function start with Public, so like
     Public Function GetStr(myStr As String) As String

You could also have a look the functions GetLastParts, GetLastPartsEx (Posted 11/19/2003 10:44AM CET), because they are more flexible ... If you should you use them, don't forget the optimizations of stevbe ...

Success!
0
 
LVL 28

Expert Comment

by:TextReport
ID: 9781480
To answer why my posting isn't working create a new module and go to the Tools Menu, References and ensure none of the ones that are ticked are saying MISSING and then do a Debug Menu, Compile All

Cheers, Andrew
0
 

Author Comment

by:jsmith1k
ID: 9781595
Hi wsteegmans,

I created a new module with your code and ran my query and got the same error message "undefined function GetLastParts in expression".  
0
 

Author Comment

by:jsmith1k
ID: 9781704
Andrew,

I still get the error message.  Only the top 4 references are checked (none with "missing" on it). I'm creating the query using the query tab, does this matter?

Thanks.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 9781863
for the code from wsteegmans drop the keywork PRIVATE from the first line

Cheers, Andrew

PS Still looking into the InstrRev issue
0
 
LVL 28

Expert Comment

by:TextReport
ID: 9781891
Just tested it in Access 2000, no problem

The query was
SELECT tblCalendarMonths.CalendarPhoto
, Mid([CalendarPhoto],instrrev([CalendarPhoto],"-",instrrev([CalendarPhoto],"-")-1)+1) AS Expr1
FROM tblCalendarMonths;

The InStrRev function is in the VBA Reference and should be OK.

Did you compile the database?

Cheers, Andrew
0
 

Author Comment

by:jsmith1k
ID: 9783227
Andrew,

The VBA Reference had no "missing" on it, and I did complile it.  

However, I took your suggestion about the code from wsteegmans (i.e. dropping the keywork PRIVATE from the first line), and it works.  

I'm at a loss why the "undefined function" error happens when I run your code.   I had even tried a stripped down statement just using the InStrRev function and it gave the same error.  So I have no idea why his code works.  



0
 
LVL 28

Expert Comment

by:TextReport
ID: 9783309
What Service Pack have you installed, I am Running SP3. Can you try another New Database and see if you can get it working.

Cheers, Andrew
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9783568
Hi guys,

Tried also the query

SELECT tblCalendarMonths.CalendarPhoto
, Mid([CalendarPhoto],instrrev([CalendarPhoto],"-",instrrev([CalendarPhoto],"-")-1)+1) AS Expr1
FROM tblCalendarMonths;

and got the same error ... Undefined Function ...

But, if you would use the Build... functionality in the Query Builder (Right Click and choose Build ...) and you choose Function -> Built-In Function, you see the Instr, but not the InstrRev ... Maybe this explains some things?

jsmith1k,

What you could do ...
- Or you use one of the other solutions posted ...
- If you want to use the Query, do this
   -> Create a new module (or use an existing one) in Access and paste this code:
            Function MyInstrRev(StrinCheck As String, StringMatch As String, Optional Start As Long = -1, _
                                Optional Compare As VbCompareMethod = vbBinaryCompare) As String
                MyInstrRev = InStrRev(StrinCheck, StringMatch, Start, Compare)
            End Function
  -> Then, alter your query like this
            SELECT tblCalendarMonths.CalendarPhoto
            , Mid([CalendarPhoto],MyInstrRev([CalendarPhoto],"-",MyInstrRev([CalendarPhoto],"-")-1)+1) AS Expr1
            FROM tblCalendarMonths;

This will work ...
Hope this helps ...

Wouter
0
 
LVL 7

Expert Comment

by:wsteegmans
ID: 9783588
Could indeed be a Service Pack problem ... I'm working on an old computer (not mine ;-)) with Access 2000 without a Service Pack ...

Maybe installing the latest service pack for Office (Service Pack 3) will help.
That explains maybe why it works with Andrew ...

Regards.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 9783608
I have just read http://support.microsoft.com/default.aspx?scid=kb;en-us;210439&Product=acc2000 and this talks about strReverse, not a function I have used but worked perfectly in my select query under SP3

Cheers, Andrew
0
 
LVL 8

Expert Comment

by:Benjamin_Luk
ID: 9784012
Wsteegmans,

Thanks for your reply for me.

Jsmith1k,

Please follow these steps:

1. Paste the code into one new Modle.
2. Save the modle
3. Then create the SQL.

You may have not saved you modle before running your SQL

Regards

Ben
0
 

Author Comment

by:jsmith1k
ID: 9784327
I ran the SP3, created a new database, cut and pasted the code into a new module and I still get the "undefined" error.  

Maybe it's the way I'm trying to execute the query.  What I'm doing - is to click on the "Queries" tab, go into "Create a query in design view", in a field I type "test: instrrev([field1],"."-1).

Thanks,

JK

0
 
LVL 8

Expert Comment

by:Benjamin_Luk
ID: 9784502
Sorry, I want u to try this one;
Please delete any 'PRIVATE' string above these code:

Public Function GetStr(myStr As String) As String
Dim I As Integer, K As Integer, J As Integer, L As Integer
'Get the last . position
K = 0
For I = 1 To Len(myStr) - 1
If InStr(I, myStr, ".", 1) > 0 Then
K = K + 1
End If
Next I
'Get the second last . position
J = 0
L = 0
For J = 1 To K - 1
If InStr(J, Left(myStr, (K - 1)), ".", 1) > 0 Then
L = L + 1
End If
Next J
GetStr = Right(myStr, Len(myStr) - L)
End Function

Pasted it into on new modle, and then save it.
Remember to save it first.

And use GetStr([myField]) to get your wanted string

Try agian.

Ben

0
 
LVL 2

Expert Comment

by:HobsonT
ID: 9784842
Just had a quick read through, so I may have missed this.

Do you have a reference to MS DAO 3.6 Object Library?

Some of the functions that you are using in your code are located in this library.
0
 
LVL 28

Expert Comment

by:TextReport
ID: 9790172
If you email me, my address is in my profile I will send you an example that I have knowcked up that works on my PC

Alternatively send me a small database with the problem and I will see if I can get it to work.

Cheers, Andrew
0
 

Author Comment

by:jsmith1k
ID: 9793274
I tried both Ben's and HobsonT's suggestions, but to no avail. My Access or PC config must be messed up.

Since Wsteegmans' works fine, I'm going to use this code.  

Thank you all very much for very creative approaches!

JK
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Visualize your data even better in Access queries. Given a date and a value, this lesson shows how to compare that value with the previous value, calculate the difference, and display a circle if the value is the same, an up triangle if it increased…

618 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question