Showing more than 30 days (format "dd")

Thread view:

Enable EMail Alerts

Start New Thread

Thread rating:

Bill R - 04 Oct 2005 14:40 GMT

I want to return a number indicating total days and hours between 2 date/time
entries which were created using the Now() function. The resulting number may
be something like 77.25. How do I format that result in a form textbox to
show "77 days, 6 hrs"? According to the help file, "dd" will only return 1-30.

I also have a function that subtracts holidays and weekends from the number,
but currently I am doing it without using any date-specific functions in the
code. Is there a simple Access function I could use to do the same thing once
I have determined the count of days "off"?

Thanks,

Bill

Reply to this Message

Charles - 04 Oct 2005 14:45 GMT

Have you looked at DateDiff?

CL

>I want to return a number indicating total days and hours between 2
>date/time
[quoted text clipped - 15 lines]
>
> Bill

Reply to this Message

Rick B - 04 Oct 2005 14:51 GMT

I would think you'd need to figure out how many hours were between the two
dates, then perform math to display days and hours.

Something like (not tested)...

=DateDiff("hh",[somefield],Now())\24 & " Days" &
DateDiff("hh",[somefield],Now()) Mod 24 & " Hrs"

You may have to work on the syntax a bit.

Signature

Rick B

> I want to return a number indicating total days and hours between 2 date/time
> entries which were created using the Now() function. The resulting number may
[quoted text clipped - 9 lines]
>
> Bill

Reply to this Message

Klatuu - 04 Oct 2005 15:53 GMT

Me.MyTextBox = format(datediff("d",dtmStart, dtmEnd),"#0") & " Days " _
& format(datediff("h",dtmStart, dtmEnd),"#0") & " Hours"

Reply to this Message

Bill R - 04 Oct 2005 16:38 GMT

Thanks to all for your responses.

I have developed it so far that it now shows a double in the query as the
result of my function (1.04166666666667). The form based on the query shows
that number in a text box. I need to do some summations of those values, so I
need the number (I can't return a string, I must return a double). But 1.
04166666666667 looks funky in the textbox, so I'd like to display it as "1
day(s), 1 hr(s)". I need the format function that will accomplish that. Do I
format the control source? If so, what should that look like? I only know
that format(CDate(result), "dd day(s), h hr(s)") doesn't work.

Thanks

barada nikto

>Me.MyTextBox = format(datediff("d",dtmStart, dtmEnd),"#0") & " Days " _
> & format(datediff("h",dtmStart, dtmEnd),"#0") & " Hours"
[quoted text clipped - 4 lines]
>>
>> Bill

Reply to this Message

Douglas J Steele - 04 Oct 2005 17:38 GMT

Write your own function to format the number as you'd like.

Int(1.04166666666667) will give you the number of days.

(1.04166666666667 - Int(1.04166666666667)) * 24 will give you the number of
hours.

Therefore, the following untested air-code should be what you need:

Function FormatDayTime(InputValue As Double) As String

Dim intDays As Integer
Dim intHours As Integer

intDays = Int(1.04166666666667)
intHours = (1.04166666666667 - Int(1.04166666666667)) * 24

If intDays = 1 Then
FormatDayTime = intDays & " day, "
Else
FormatDayTime = intDays & " days, "
End If

If intHours = 1 Then
FormatDayTime = FormatDayTime & intHours & " hour"
Else
FormatDayTime = FormatDayTime & intHours & " hours"
End If

End Function

You'd use =FormatDayTime(1.04166666666667) as the control source for your
text box.

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(no e-mails, please!)

> Thanks to all for your responses.
>
[quoted text clipped - 19 lines]
> >>
> >> Bill

Reply to this Message

Bill R - 04 Oct 2005 20:57 GMT

Thanks a million to you and all other "first responders" ;-)

>Write your own function to format the number as you'd like.
>
[quoted text clipped - 35 lines]
>> >>
>> >> Bill

Reply to this Message

Bill R - 04 Oct 2005 16:40 GMT

Additionally:

I also have the question about formatting using "dd" in that the help files
say that will only return a number from 1 - 30.

Thanks

>Me.MyTextBox = format(datediff("d",dtmStart, dtmEnd),"#0") & " Days " _
> & format(datediff("h",dtmStart, dtmEnd),"#0") & " Hours"
[quoted text clipped - 4 lines]
>>
>> Bill

Reply to this Message