Page 1 of 2 12 LastLast
Results 1 to 15 of 17
  1. #1
    Join Date
    Aug 2003
    Posts
    64

    Unanswered: convert text numbers

    Guy's,


    Using this expression:
    CDbl(Left([MYTIME],2))+(CDbl(Mid([MYTIME],4,2)/60))+(CDbl(Right([MYTIME],2)/3600))

    When i import (195929) using the expression above the results are 54.4247222222222 when I format it's (10:11:36 AM). Looking at the imported numbers I would think this should be 19:59:29 or 08:59 pm.

    My question is, what is missing from the expression because it's adding more hours from the text number. Maybe the time is rolling over the 24 hour mark, if so how can I accomadate this problem..


    Any revisions would be helpful.....Thanks

  2. #2
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    This will work even if MYTIME has a single-digit hour:

    CDbl(Left(Right(Space(6) & [MYTIME],6),2))/24+CDbl(Left(Right(Space(6) & [MYTIME],4),2))/24/60+CDbl(Left(Right(Space(6) & [MYTIME],2),2))/24/60/60

    The other problems with what you're using:
    1) Everything needs to be divided by 24
    2) The middle term, which represents the minutes, should be Mid([MYTIME],3,2) instead of Mid([MYTIME],4,2)
    Last edited by JTRockville; 08-21-03 at 00:42.

  3. #3
    Join Date
    Aug 2003
    Posts
    64

    "Error"

    Undefined Function 'Left' in expression.

    Not sure what's wrong, what happen?

  4. #4
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    hmmmm...
    It works fine for me.

    "Left" is in the "Visual Basic For Applications" library, so as long as you have that reference, you shouldn't have a problem.

    "Left" worked before, didn't it? Does your application compile?

  5. #5
    Join Date
    Aug 2003
    Posts
    64

    Now it's working but.....

    I made the corrections via Visual Basic, but your expressions gives an error when my time rolls over midnight

    Please advise

    thanks comf36

  6. #6
    Join Date
    Aug 2003
    Posts
    64

    here's what I need

    the error has a time starting at 00:00:14, my guess with the expression you gave me needs an expression also using seconds?? The conversion only handles hours and minutes, could you revised this to include seconds.

    comf36

  7. #7
    Join Date
    Aug 2003
    Posts
    64

    Talking hey forgot to mention

    using the field (MYTIME) and another field (THEIRTIME) with the examples you gave me, how could I calculate them to determine the elapsed time. in minutes an hours.

    (MYTIME)-(THEIRTIME), would this work with an query or report, your suggestions is well received.....

  8. #8
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    If MYTIME is 195929, it should work

    If MYTIME is 19:59:29, try this:
    CDbl(Left(Right(Space(8) & [MYTIME],8),2))/24+CDbl(Left(Right(Space(8) & [MYTIME],5),2))/24/60+CDbl(Left(Right(Space(8) & [MYTIME],2),2))/24/60/60

    Alternatively, (there's always more ways than one to skin a cat), try this:
    Hour(CVDate([MYTIME]))/24 + Minute(CVDate([MYTIME]))/24/60 + Second(CVDate([MYTIME]))/24/60/60

    This should work to calculate the difference:
    Format$(CDbl(CVDate([MYTIME]))-CDbl(CVDate([THEIRTIME])),"hhh:nn")

  9. #9
    Join Date
    Aug 2003
    Posts
    64

    Answer

    Both of your suggestions is now showing 00:00:00, what happen? Also I can't get the elasped time; (MYTIME)-(THEIRTIME).

  10. #10
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Are [MYTIME] and [THEIRTIME] fields in a table?
    If so, what format are they (string, date/time)?
    Can you show a few samples?

    If you attach the mdb, I'll gladly take a peek at it.

  11. #11
    Join Date
    Aug 2003
    Posts
    64

    Wink Ok but...

    Not sure how to use the extensions to send you a copy, provide steps. I have XP professional, Accees2k.. I did select the browse button and when I click to send, is said invalid extensions, more familiar with just selecting the text then send. Hope I'am making sense?


    Valid file extensions: gif jpg png txt zip bmp jpeg gz tgz rar par pdf doc sql pc c pm pls pl rar rex rexx

  12. #12
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Be sure no one has your mdb open. Then from the file explorer:
    • Right-click on the file
    • Choose "Send To", then "Compressed (zipped) Folder"

    This will create a file with a .zip extension (which is valid as an attachment here) instead of an .mdb extension.

  13. #13
    Join Date
    Jan 2003
    Location
    Rockville, MD
    Posts
    179
    Code:
    SELECT tblfile.DATE, tblfile.USER, tblfile.TYPE, tblfile.NBR, 
    CDbl(Left(Right(String(6,"0") & [MYTIME],6),2))/24+
    CDbl(Left(Right(String(6,"0") & [MYTIME],4),2))/24/60+
    CDbl(Left(Right(String(6,"0") & [MYTIME],2),2))/24/60/60 AS Str, 
    CDbl(Left(Right(String(6,"0") & [THEIRTIME],6),2))/24+
    CDbl(Left(Right(String(6,"0") & [THEIRTIME],4),2))/24/60+
    CDbl(Left(Right(String(6,"0") & [THEIRTIME],2),2))/24/60/60 AS [End], 
    Format$(iif([MYTIME]>[THEIRTIME],1,0)+
    CDbl(Left(Right(String(6,"0") & [THEIRTIME],6),2))/24+
    CDbl(Left(Right(String(6,"0") & [THEIRTIME],4),2))/24/60+
    CDbl(Left(Right(String(6,"0") & [THEIRTIME],2),2))/24/60/60-
    CDbl(Left(Right(String(6,"0") & [MYTIME],6),2))/24-
    CDbl(Left(Right(String(6,"0") & [MYTIME],4),2))/24/60-
    CDbl(Left(Right(String(6,"0") & [MYTIME],2),2))/24/60/60,"hh:nn:ss")  AS Total
    FROM tblfile
    Problems with my original query were caused by "blank" values for the hour, between midnight and 1 am. To correct that, I changed Space(6) to String(6,"0"), which uses zeros instead of blanks as padding.

    Also, the difference should be [THEIRTIME]-[MYTIME] rather than [MYTIME]-[THEIRTIME]. A problem arose here as well, if MYTIME was late in the day, and THEIRTIME was early the next morning. To correct that, I added 1 day to THEIRTIME before subtracting, if MYTIME is greater than THEIRTIME.

    Hope that works for you!

  14. #14
    Join Date
    Aug 2003
    Posts
    64

    Thumbs up Got it,

    Thanks, could you guide me where to put this, how to start or use a module, because I see code.

    thanks so much for your patients.....

  15. #15
    Join Date
    Aug 2003
    Posts
    64

    Also,,

    I have several thousands records coming in daily and I would like to leave it and have the expression to them all at once..

Posting Permissions

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