Int(1) Returns 0 in Access Query

[M_S_Jones]

Hello,

I am working on the query behind a report that deals with durations and costs. At the moment the durations are in hours only. I'm using these durations in hours to calculate labour costs, based on hourly rates. But I'd also like the durations to be displayed in hours and minutes. So I added a new field to my query which formats the durations in hours into hours and minutes. I used the following formula, where [Duration] refers to the duration in hours:

Int([Duration]) & ":" & Format(([Duration]-Int([Duration]))*60,"00")

I thought that this would work, and it does in almost every scenario in my data. Except where the duration is one hour exactly. Then a time of 0:60 is calculated, as Int(1) returns 0 and therefore 0 hours are displayed, along with (1 - 0) *60, to give 60 minutes. Is there another function that would work as Int (and Fix), but return 1 as 1?

Thank you,

Matthew

 

[dkinley]

How about something like ....

Int(IIF([Duration] < (60 seconds), 1, [Duration]) & ....

Basically something to evaluate duration and bump it up if it doesn't meet a specification. I am not sure how to represent (60 seconds) because I do not fully understand the duration field or how it is setup.

It might end up being

IIF(Int[Duration] = 0, 1, [Duration]) & ...

-dK

 

[Brianwarnock]

I suspect Duration is a field calculated from times as Int(1) definitely gives 1, try
durationinhhmm:timeserial(0,[duration],0)

Brian

 

[M_S_Jones]

Thank you for your replies. The duration field is a number field consisting of the time in hours, i.e. 1.5 for 1 hour 30 minutes. Where a duration of a record had been entered as hour (and so 1), the time was calculated as 0:60. Just to verify that this was a problem with the Int() function, I added a test field to the query:

Test: Int(1)

This returned 0 for every record. I did consider an if statement around the time conversion field, but I wasn't sure if there were numbers other than 1 that would cause problems. However, I ran a compact and repair and suddenly everything started working and the "Test" field also returned 1.

Matthew

 

[Brianwarnock]

In my tests Int(1) gives me 1

Brian

 

[dkinley]

However, I ran a compact and repair and suddenly everything started working and the "Test" field also returned 1.

Zounds ... Who woulda thunk that? I was going to reply an integer of 1 must be 1 because that is the definition of integer and suggest corruption but I would have been going on about exporting everything in a new database.

I am glad you figured it out, got it going, and that it was something simple and especially glad I didn't post.

-dK

 

[Brianwarnock]

Thanks for the update, glad its working.

Brian