Solved

Returning characters from a string

Posted on 2011-03-16
3
348 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
[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
3 Comments
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 35148639
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
ID: 35149278
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
ID: 35151826
Thanks,

I appreciate the help.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
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 …
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
In Microsoft Access, learn different ways of passing a string value within a string argument. Also learn what a “Type Mis-match” error is about.

751 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