Round numbers

[bowz75]

I am exporting an access database to excel. the database is a call center database that tracks system downtime and the impact. Impact varies from green=up-low impact, yellow=partial-medium impact, and red=down-major impact. The export expression, listed below, takes the close date and time - the open date and time, converts to minutes and then tack on the impact. I am trying to get either access or excel to display a round number. The expression and the result are listed below. Excel will not autoformat a mixed (number and text) cell. Win 2K SP4 OS and Office/Access 2K.

Expression:
TimeToRepair: ([MPR].[Close Date]-[MPR].[Open Date])*1440 & "Minutes" & [System Impact] & "Time"

Current Result:
188.999999997905 Minutes Green Time

Desired Result:
189 Minutes Green Time

 

[traingamer]

One way would be to round the number first:

TimeToRepair: [COLOR=red]round([/color]([MPR].[Close Date]-[MPR].[Open Date])*1440[COLOR=red], 0)[/color] & "Minutes" & [System Impact] & "Time"

Greg
"Personally, I am always ready to learn, although I do not always like being taught." - Winston Churchill

 

[genomon]

We use this:

Public Function RoundTotal(ByVal dblNumber As Double, ByVal intDecimals As Integer) As Double

  'Comments   : 
  '           : 0.5 is rounded up
  'Parameters : dblNumber - number to round
  '           : intDecimals - number of decimal places to round to
  '           : (positive for right of decimal, negative for left)
  'Returns    : Rounded number

    Dim dblFactor As Double
    Dim dblTemp As Double         ' Temp var to prevent rounding problems in INT()

    dblFactor = 10 ^ intDecimals
    dblTemp = dblNumber * dblFactor + 0.5
    RoundTotal = Int("" & dblTemp) / dblFactor
  
End Function

It's always darkest before dawn. So if you're going to steal your
neighbor's newspaper, that's the time to do it.

 

[ZOR]

Don't know if this would work, something I would try myself while waiting for the correct syntax/ideas

TimeToRepair: int(([MPR].[Close Date]-[MPR].[Open Date])*1440) & "Minutes" & [System Impact] & "Time"

Might work??

 

[bowz75]

All,

Thanks for the replys. I tested round and int and both worked. The only difference was that round returned 189 while int returned 188. Thanks again.