Setting the display format of a query using the DateDiff function

G

Guest

I have a question concerning the display of a calculated field based on the
DateDiff function.

Assume there exist “Start Time†and “End Time†fields that contain valid
date & time information, and the date and time difference between these two
fields is 75 minutes.

I have created the following queries successfully…

Duration (Min): DateDiff("n",[Start Time],[End Time])
Duration (Hrs): DateDiff("h",[Start Time],[End Time])

The “Duration (Min)†query shows the following result: 75
The “Duration (Hrs)†query shows the following result: 1

I am trying to find a method of displaying the results as 1.25 hours
(decimal equivalent of 1-1/4 hours).

Can anyone offer suggestions as to how this may be accomplished?

I would like to be able to use this method in forms as well as queries.
 
G

Guest

Yes - I have tried that also. It does work, but I find that it often
displays numbers with too much precision. For example: 1.66666666667.

Do you know of a method to "format" or filter the returned value to a
specific number of decimal places?

--
RichG


Dale Fye said:
How about:

Duration (Hr): DateDiff("n",[Start Time],[End Time])/60

Dale
--
Email address is not valid.
Please reply to newsgroup only.


RichG said:
I have a question concerning the display of a calculated field based on the
DateDiff function.

Assume there exist “Start Time†and “End Time†fields that contain valid
date & time information, and the date and time difference between these two
fields is 75 minutes.

I have created the following queries successfully…

Duration (Min): DateDiff("n",[Start Time],[End Time])
Duration (Hrs): DateDiff("h",[Start Time],[End Time])

The “Duration (Min)†query shows the following result: 75
The “Duration (Hrs)†query shows the following result: 1

I am trying to find a method of displaying the results as 1.25 hours
(decimal equivalent of 1-1/4 hours).

Can anyone offer suggestions as to how this may be accomplished?

I would like to be able to use this method in forms as well as queries.
Click to expand...
Click to expand...
 
J

John Spencer

Format([SomeValue],"0.00")

or

Round([SomeValue,2)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

RichG said:
Yes - I have tried that also. It does work, but I find that it often
displays numbers with too much precision. For example: 1.66666666667.

Do you know of a method to "format" or filter the returned value to a
specific number of decimal places?

--
RichG


Dale Fye said:
How about:

Duration (Hr): DateDiff("n",[Start Time],[End Time])/60

Dale
--
Email address is not valid.
Please reply to newsgroup only.


RichG said:
I have a question concerning the display of a calculated field based on
the
DateDiff function.

Assume there exist "Start Time" and "End Time" fields that contain
valid
date & time information, and the date and time difference between these
two
fields is 75 minutes.

I have created the following queries successfully.

Duration (Min): DateDiff("n",[Start Time],[End Time])
Duration (Hrs): DateDiff("h",[Start Time],[End Time])

The "Duration (Min)" query shows the following result: 75
The "Duration (Hrs)" query shows the following result: 1

I am trying to find a method of displaying the results as 1.25 hours
(decimal equivalent of 1-1/4 hours).

Can anyone offer suggestions as to how this may be accomplished?

I would like to be able to use this method in forms as well as queries.
Click to expand...
Click to expand...
Click to expand...
 
G

Guest

John:

Using the following query as a starting point...

Duration (Hrs): DateDiff("h",[Start Time],[End Time])

I am not sure how to use the "Format" or "Round" function in conjunction
with this query. For example, I tried the following...

Duration (Hrs): Format([DateDiff("h",[Start Time],[End Time])],"0.00")
Duration (Hrs): Round([DateDiff("h",[Start Time],[End Time])],2)

In each case, the system displays the following error message...

"The expression you entered contains invalid syntax."

Can you please show me the proper syntax to get this query to work?

Thank you very much...

--
RichG


John Spencer said:
Format([SomeValue],"0.00")

or

Round([SomeValue,2)



--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

RichG said:
Yes - I have tried that also. It does work, but I find that it often
displays numbers with too much precision. For example: 1.66666666667.

Do you know of a method to "format" or filter the returned value to a
specific number of decimal places?

--
RichG


Dale Fye said:
How about:

Duration (Hr): DateDiff("n",[Start Time],[End Time])/60

Dale
--
Email address is not valid.
Please reply to newsgroup only.


:

I have a question concerning the display of a calculated field based on
the
DateDiff function.

Assume there exist "Start Time" and "End Time" fields that contain
valid
date & time information, and the date and time difference between these
two
fields is 75 minutes.

I have created the following queries successfully.

Duration (Min): DateDiff("n",[Start Time],[End Time])
Duration (Hrs): DateDiff("h",[Start Time],[End Time])

The "Duration (Min)" query shows the following result: 75
The "Duration (Hrs)" query shows the following result: 1

I am trying to find a method of displaying the results as 1.25 hours
(decimal equivalent of 1-1/4 hours).

Can anyone offer suggestions as to how this may be accomplished?

I would like to be able to use this method in forms as well as queries.
Click to expand...
Click to expand...
Click to expand...
 
J

John Spencer

Your posting has square brackets when it should have parentheses. I think
the following should work.

Duration (Hrs): Format(DateDiff("h",[Start Time],[End Time]),"0.00")

Duration (Hrs): Round(DateDiff("h",[Start Time],[End Time]),2)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
G

Guest

John:

The following query works...

Duration (Hrs): Format((DateDiff("n",[Start Time],[End Time])/60),"Fixed")

or

Duration (Hrs): Format((DateDiff("n",[Start Time],[End Time])/60),"0.000")

Thank you for your help!

--
RichG


John Spencer said:
Your posting has square brackets when it should have parentheses. I think
the following should work.

Duration (Hrs): Format(DateDiff("h",[Start Time],[End Time]),"0.00")

Duration (Hrs): Round(DateDiff("h",[Start Time],[End Time]),2)

--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

RichG said:
John:

Using the following query as a starting point...

Duration (Hrs): DateDiff("h",[Start Time],[End Time])

I am not sure how to use the "Format" or "Round" function in conjunction
with this query. For example, I tried the following...

Duration (Hrs): Format([DateDiff("h",[Start Time],[End Time])],"0.00")
Duration (Hrs): Round([DateDiff("h",[Start Time],[End Time])],2)

In each case, the system displays the following error message...

"The expression you entered contains invalid syntax."

Can you please show me the proper syntax to get this query to work?

Thank you very much...

--
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Access DateDiff function 0
Total Query: Adding a field to a calculation. 3
Strange result from query, '>' gives same result as '>=' 6
Change the form source through a VB code 2
Add a field in a query. Easy. . .so I though. 5
End of Month: Parameter Query? 3
Display the lesser of two DateDiff expressions 1
Calculating the Difference in a Query 4

Top