Results 1 to 7 of 7
  1. #1
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Converting text to value (Access 2003)

    I have two textboxes on a form, both having an input mask "00:00:00:00_

    They are bound to a table and store a time value, and have text properties (Done by someone else)

    I have a problem comparing the contents of the two fields, Time Start and Time End. I am trying to insure the values of Time End are after the values of Time Start. I have tried using <> after using VAL property of the text contents.

    However with start as 11:20:00:00 and end of 11:30:00:00 my messagebox says they are the same values 11 and 11.

    Anyone know a way round it? Thanks

  2. #2
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Converting text to value (Access 2003)

    You can use the Cdate function to convert to a date/time, but it does not like the last two digits and the last colon. so you can also use the left function to trim them off.

    Cdate(left("11:20:00:00",8)) = 11:20:00 AM


    Cdate(left("11:20:30:00",8)) = 11:20:30 AM

    Cdate(left("11:20:00:00",5)) =11:20:00AM
    Regards
    John



  3. #3
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting text to value (Access 2003)

    Many thanks for your help John, that fixed it perfectly. Best regards

  4. #4
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting text to value (Access 2003)

    Thought all was okay, however is CDate a date syntax? as further testing fails giving error. If I have 22:22:22:22 and 33:33:33:33 it reports a type mismatch, I think error 13. So I am still stuck, thanks

  5. #5
    Super Moderator
    Join Date
    Jun 2002
    Location
    Mt Macedon, Victoria, Australia
    Posts
    3,993
    Thanks
    1
    Thanked 45 Times in 44 Posts

    Re: Converting text to value (Access 2003)

    What is 22:22:22:22 meant to mean as a time?

    In my testing of CDate it could not handle the last two digits, so I removed them with the left function.
    But if the last two digits are important, what do they mean?
    Regards
    John



  6. #6
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting text to value (Access 2003)

    Hi John, thanks. I have just managed to find out the eight digits represent hours, minutes, seconds & frames but cannot confirm wether or not the the hours digits go past 24 ie 99. All I know is the second pair of numbers has to be later in time. Wish I was on holiday. Regards

  7. #7
    4 Star Lounger
    Join Date
    Feb 2008
    Location
    United Kingdom
    Posts
    490
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Converting text to value (Access 2003)

    Hi John, moments after posting back someone mailed me this passage:

    The original uses of SMPTE timecode include accurate video editing and synchronising film sound-tracks. The timing data in SMPTE takes the form of an eight digit twenty-four hour clock. The count consists of 0 to 59 seconds, 0 to 59 minutes and 0 to 23 hours. The second is subdivided into a number of frames, which may be varied to match the various frame-rates used around the world. The frame-rate is the number of times a second that the picture is updated so as to give the illusion of continuous movement.

    So if its based on a 24 hour clock then my issue on trying 33 hours does not fit, therefore your code is correct!!

    So many thanks, problem should finally be resolved and put to bed. Very best regrads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •