Need Function in Access for text conversion


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 6 of 6

Thread: Need Function in Access for text conversion

  1. #1
    Join Date
    Jul 2007
    Posts
    6

    Need Function in Access for text conversion

    I've got a text field in Access which looks as follows:

    5 02:05
    or
    11 06:59

    representing elapsed time of 5 days, 2 hours and 5 minutes in the first case;
    and
    11 days, 6 hours, and 59 minutes in the second case.

    I'd like to break this down to Days, Hours, Minutes in separate, NUMERICAL fields, so that I can do calculations on it.

    I've figured out how to get the Days separated, using:
    Day: Left([Time],InStr([Time]," "))

    and figured out the minutes, using:
    Min: Right([Time],(Len([Time])-InStrRev([Time],":")))

    but I can't figure out how to get the hours part separated, then, how do I convert to numerical values?

    Thanks!!

  2. #2
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Since you can make then separated by spaces for each time part you can split the entire value to an array and then element 0 will be days, 1 will be minutes and 2 will be seconds.

    You can CInt each element of the array if you need it in Integer data type.

    Code:
    Public Function SplitMe(ByVal sDayTime As String) As Variant
        Dim ar() As String
        ar = Split(sDayTime, Chr(32))
        SplitMe = ar
    End Function
    
    Private Sub WhateverTest()
        Dim ar() As String
        ar = SplitMe(Replace([Time], ":", Chr(32)))
        MsgBox "Days: " & ar(0) & " Hours: " & ar(1) & " Minutes: " & ar(2)
    End Sub
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  3. #3
    Join Date
    Jul 2007
    Posts
    6
    Awesome - thanks. I've not really used VB in access though so I probably need to learn a bit there and I assume I paste this into a Module and then let it rip?

  4. #4
    Join Date
    Mar 2005
    Location
    Los Angeles, Calif. AKA: Gangsta Yoda™
    Posts
    455
    Yes, you can paste the SplitMe funciton into a module as I made it a public function. The WhatererTest sub is just an example of its use.
    VB/Office Guru™ (AKA: Gangsta Yoda®)
    I dont answer code questions via PMs.
    Microsoft MVP 2006-2009
    Office Development FAQ (VBA, VB6, VB.NET, C#)
    Software Engineer MCP (VB6 & .NET)
    Reps & Rating PostsVB/Office Guru™ Word SpellChecker™.NETVB/Office Guru™ Word SpellChecker™ VB6Outlook Global Address List

  5. #5
    Join Date
    Nov 2003
    Location
    Portland, OR
    Posts
    8,387
    To convert a string to a numeric value, you may use the Val() or CInt() functions.
    Phil Weber
    http://www.philweber.com

    Please post questions to the forums, where others may benefit.
    I do not offer free assistance by e-mail. Thank you!

  6. #6
    Join Date
    Jul 2007
    Posts
    6
    Thanks guys.

Similar Threads

  1. call function for button
    By angela_quests in forum VB Classic
    Replies: 2
    Last Post: 04-13-2007, 05:57 AM
  2. Getting a GUI to run
    By Eric in forum Java
    Replies: 4
    Last Post: 04-14-2006, 10:09 AM
  3. Replies: 4
    Last Post: 03-16-2006, 06:23 PM
  4. Replies: 60
    Last Post: 09-13-2002, 06:41 PM
  5. Getting a GUI to function
    By Eric in forum Java
    Replies: 1
    Last Post: 11-27-2001, 07:53 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center