Decimal places and Date Range in Expression Builder

  • Thread starter Thread starter Axess08
  • Start date Start date
A

Axess08

I am trying to output a range of dates and times in a query that will show
the max and min of the dates like this: 1-99

I have used this expression in the expression builder which works:
Time to Arrival Range: IIf([MinOfTime to Arrival (hrs)]=[MaxOfTime to
Arrival (hrs)],Null,[MinOfTime to Arrival (hrs)] & " - " & [MaxOfTime to
Arrival (hrs)])

However, for the range of times I am getting a long string of numbers after
the decimal place despite the fact that the original location of these values
is setup as "standard" in format and "1" in decimal places. I get an output
of "1 - 3.66666666666667" for instance. What can I use in the expression
builder that will allow me to format this to just "1 - 3.67", with a rounded
decimal place?
 
I just realized that my problem is similar to a problem posted earlier today
(which was already responded to). Thank you to whomever sees this - to the
moderator, this post can be removed or linked to the post on 12/09/2008 -
"Equivalent to trunc functions in access".
 
I am trying to output a range of dates and times in a query that will show
the max and min of the dates like this: 1-99

I have used this expression in the expression builder which works:
Time to Arrival Range: IIf([MinOfTime to Arrival (hrs)]=[MaxOfTime to
Arrival (hrs)],Null,[MinOfTime to Arrival (hrs)] & " - " & [MaxOfTime to
Arrival (hrs)])

However, for the range of times I am getting a long string of numbers after
the decimal place despite the fact that the original location of these values
is setup as "standard" in format and "1" in decimal places. I get an output
of "1 - 3.66666666666667" for instance. What can I use in the expression
builder that will allow me to format this to just "1 - 3.67", with a rounded
decimal place?
Click to expand...

The Format() or Round() function:

instead of

[MinOfTime to Arrival (hrs)] & " - " & [MaxOfTime to Arrival (hrs)]

use

Format([MinOfTime to Arrival (hrs)] & " - " & [MaxOfTime to Arrival (hrs)],
"#.00")

or

Round([MinOfTime to Arrival (hrs)] & " - " & [MaxOfTime to Arrival (hrs)], 2)
 
Back
Top