Showing more than 30 days (format "dd")

[Bill R via AccessMonster.com]

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

 

[Charles]

Have you looked at DateDiff?

CL

 

[Rick B]

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.

 

[Guest]

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

 

[Bill R via AccessMonster.com]

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"

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

 

[Bill R via AccessMonster.com]

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"

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

 

[Douglas J Steele]

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.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)

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"

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

 

[Bill R via AccessMonster.com]

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

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.

Thanks to all for your responses.

[quoted text clipped - 19 lines]