Solved

Returning characters from a string

Posted on 2011-03-16
3
311 Views
Last Modified: 2012-05-11
Hello,

I have a field containing the following type of string data.  I want to create a field in a query that extracts and displays 2.09.56 So I need to find the position of the third occurence of the period.  and then use a MID function to do the actual extraction.  How do I find a specified occurance of a characer
2.09.56.25.67303.00.P09
0
Comment
Question by:chtullu135
3 Comments
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
Comment Utility
it will be better to use a function.
place this function in a regular module

function getX(s) as string
if s & ""="" or isnull(s) then getX="":exit function

dim arS,j, strX

ars=split(s,".")
for j=0 to 2
  strx = strx & "." & arS(j)
next

getX=mid(strx,2)

end function


yo use in your query

select f1, getx([f1]) as newfield from table

0
 
LVL 17

Expert Comment

by:JezWalters
Comment Utility
Or you could do it like this:

    Option Explicit
    Option Base 0

    Public Function GetDate(pvarField As Variant) As Variant

        Dim astrComponents() As String

        astrComponents = Split(Nz(pvarField), ".")
        If UBound(astrComponents) > 2 Then
            ReDim Preserve astrComponents(2) As String
            GetDate = Join(astrComponents, ".")
        Else
            GetDate = Null
        End If

    End Function

, with am acommpanying query like this:

    SELECT YourField, GetDate(YourField) AS FieldDate
    FROM YourTable
0
 

Author Closing Comment

by:chtullu135
Comment Utility
Thanks,

I appreciate the help.
0

Featured Post

Get up to 2TB FREE CLOUD per backup license!

An exclusive Black Friday offer just for Expert Exchange audience! Buy any of our top-rated backup solutions & get up to 2TB free cloud per system! Perform local & cloud backup in the same step, and restore instantly—anytime, anywhere. Grab this deal now before it disappears!

Join & Write a Comment

In the previous article, Using a Critera Form to Filter Records (http://www.experts-exchange.com/A_6069.html), the form was basically a data container storing user input, which queries and other database objects could read. The form had to remain op…
Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Familiarize people with the process of utilizing SQL Server views from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Access…
In Microsoft Access, learn the trick to repeating sub-report headings at the top of each page. The problem with sub-reports and headings: Add a dummy group to the sub report using the expression =1: Set the “Repeat Section” property of the dummy…

743 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

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now