Results 1 to 8 of 8
  1. #1
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132

    Unanswered: How to Add 1 to Time

    Hi
    I have one field in table named "Time Required" and its Double where user puts 1.0 or .5 etc. I would like to add this digit or double to Time . How can achieve that?
    ex.

    if its 10:00 AM (short format)
    then i would like to add
    10:00 +1.0 and it should give me 11:00

    Here is the code where im tryinjg to achieve this :

    If adrs2.EOF = False Then
    Me![From] = txtTime
    Me![To] = Left(From, 5) + Left(Format(adrs2![TimeRequired], "Short Time"), 5)

    End If

    adrs2![TimeRequired] is that field which holds a number to be added to Time. but it always gives me 00:00.
    Thank you in advance
    VBAPROGRAMMWER
    Last edited by VBAPROGRAMMER; 11-04-04 at 07:09.

  2. #2
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1

    Lightbulb

    Try
    Format (dateadd("h", 1,date() ) , "hh:mm")

    Format (dateadd("h", 1,yourField ) , "hh:mm")

  3. #3
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132
    Quote Originally Posted by hammbakka
    Try
    Format (dateadd("h", 1,date() ) , "hh:mm")

    Format (dateadd("h", 1,yourField ) , "hh:mm")

    Me![To] = Format(DateAdd("h", adrs2![TimeRequired], Me![From]), "hh:mm")

    Thank you thsi works for adding one hour , im trying to see if it works to add half an hour as the field will hold "0.5" to be added. Thankyou in advance.

  4. #4
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132
    Quote Originally Posted by hammbakka
    Try
    Format (dateadd("h", 1,date() ) , "hh:mm")

    Format (dateadd("h", 1,yourField ) , "hh:mm")

    if field holds .5 then it does nothing if it holds 1 then it works fine. any solution? how to make it take that .5 and think of it as to add half an hour to time?
    Me![To] = Format(DateAdd("h", adrs2![TimeRequired], Me![From]), "hh:mm")

  5. #5
    Join Date
    Oct 2003
    Location
    Ger
    Posts
    1,969
    Provided Answers: 1
    To add minutes use

    Format (dateadd("n", 30,yourField ) , "hh:mm")

  6. #6
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132
    Quote Originally Posted by hammbakka
    To add minutes use

    Format (dateadd("n", 30,yourField ) , "hh:mm")

    This code works only if the field is less than 10.0 meaning it will work if its 9.5 , upon condition it will add half an hour then will add number of hours if required else willjsut add the number fo hours. this works but any better solution ?

    If adrs2.EOF = False Then
    Me![From] = txtTime
    If Right(adrs2![TimeRequired], 1) = 5 Then
    Me![To] = Format(DateAdd("n", 30, Me![From]), "hh:mm")
    If Left(adrs2![TimeRequired], 1) > 0 Then
    Me![To] = Format(DateAdd("h", (Left(adrs2![TimeRequired], 1)), Me![From]), "hh:mm")
    End If
    Else
    Me![To] = Format(DateAdd("h", adrs2![TimeRequired], Me![From]), "hh:mm")
    End If

    End If

  7. #7
    Join Date
    Sep 2004
    Location
    Charlotte, NC
    Posts
    164
    This may not be what you are asking for, but based upon what I read in your question, this should provide the correct result.

    I created a small table called 'Project Times' that has a field for a Project ID called 'Project', Starting Time set as a Date\Time and formatted as Medium Date and called 'StartTime' and the hours required set as Double and called 'TimeRequired'. I then calculate the Project Ending time by adding the time required to the starting time and calling it 'EndTime'.

    SELECT [Project Times].Project, [Project Times].StartTime, [project times].[StartTime]+([Project Times]![TimeRequired]/24) AS EndTime
    FROM [Project Times];


    TD

  8. #8
    Join Date
    Nov 2002
    Location
    Hamilton,Ontario
    Posts
    132
    Quote Originally Posted by buckeye_td
    This may not be what you are asking for, but based upon what I read in your question, this should provide the correct result.

    I created a small table called 'Project Times' that has a field for a Project ID called 'Project', Starting Time set as a Date\Time and formatted as Medium Date and called 'StartTime' and the hours required set as Double and called 'TimeRequired'. I then calculate the Project Ending time by adding the time required to the starting time and calling it 'EndTime'.

    SELECT [Project Times].Project, [Project Times].StartTime, [project times].[StartTime]+([Project Times]![TimeRequired]/24) AS EndTime
    FROM [Project Times];


    TD

    I changed the implementation of this sql statement into vba. and the RESULT? PERFECT
    here is the code

    If adrs2.EOF = False Then
    'To Add the Time
    Me![From] = txtTime
    Me![To] = (Me![From] + (adrs2![TimeRequired]) / 24)
    End If

    this code works like a magic Thank you all for your help . I really appreciated it

Posting Permissions

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