format hour

[hair]

I have this query :

SELECT Sum((Planning.TotalHeures)) AS SommeDeTotalHeures
FROM Planning
WHERE (((Planning.[Nom Agent])=Forms!Plan.Texte5));


It should give me results like 35hours 45 minutes, but when it's passing over 24 hours is someting like 1,3355--etc

How can I use the format in the query to force the result to be like hh:nn ? I know it is possible to do it in the query without involving vba. I posted already this but I didn't managed. Thanks in advance.

 

[hair]

Thank you Pat

 

[hair]

after I wrote that I saw that the link is not working
searching after right now

 

[hair]

Pat, it worked now, it had problems yesterday I suppose
The thing is I knex that link already, and I can't yet handle to manage it. Just one simple question: in access 2002 there is no other way to get as a result of a query a record like 35:45, without having to pass by code? Is no way that in the query where is sum(blablabla) to format it simply by format(sum(blablabla) andheretherightformat))?

Because at my knowledge level now it is a little too scriptic the article. I don't know yet how to say to access that the result of the query should pass by that procedure. In a word, I'm lost there. Do you have a db example that does that? For me to be able to follow?

Thanks
PS
Sorry but I'm beginning with that stuff and I have the feeling that this should be a simple issue. Why the hack there is no option when u create a table to have a time field that goes over 24hours

 

[Mile-O]

Here's a function that might help you - the only problem is that it returns times over 24 hours as a string:

Function TimeConversion(ByVal tempTime As Date) As String
    
    'On Error GoTo Err_Error

    ' This routine is given values from the calling procedure and calculates the time
    ' beyond the obstacle of a 24-hour clock
    
    Dim lngDays As Long, lngHours As Long, lngMinutes As Long, lngSeconds As Long
    
    lngDays = Int(CSng(tempTime))
    lngDays = lngDays * 24
    lngHours = Int(CSng(tempTime * 24))
    lngMinutes = Int(CSng(tempTime * 1440))
    lngSeconds = Int(CSng(tempTime * 86400))
    lngHours = lngDays + (lngHours Mod 24) ' calculate hours
    lngMinutes = lngMinutes Mod 60
    lngSeconds = lngSeconds Mod 60
    
    TimeConversion = lngHours & ":" & IIf(lngMinutes <= 9, "0" & lngMinutes, lngMinutes) _
        & ":" & IIf(lngSeconds <= 9, "0" & lngSeconds, lngSeconds)
        
    MsgBox TimeConversion
    
Exit_Error:
    Exit Function
    
Err_Error:
    MsgBox Err.Description, vbExclamation, "Error #" & Err.Number
    Resume Exit_Error

End Function

 

[hair]

I'll try it now

Thank you Sir

 

[hair]

I know that isn't good for your mood, but how do I specify in my form field where the result of the query is delivered that it shoud call this function?

 

[hair]

just found out - in the format thing..AND IT WORKS

send you the beer again, give me u're adress

 

[hair]

nope - it passes over 24 hours but it's a mess with the minutes, the calculation isnt right. Something to do with the fact that I use the french version of windows and office?

 

[hair]

Pat, I made it like this. But for instance the result now, instead of
30:45, how it should be, is 1 Day 45 hours. If it would have been right I could handle toward but it isn't. I'll try to figure it out. Thanks a lot.

 

[hair]

As weird as it sounds, I tried the first function now, the conversiontime, and it works. It's just from time to time I get results that are not good (1 out of 10 tries), but I think it's because of some refresh issues. I'll dig to understand. Thank you Pat

 

[hair]

Do you have any idea why after you populate a table let's say with 30000 records, obviously the size od the database grows let's say until 5 mega/ But my question is why, after you erase the all records, the size remains the same? That is not normal

 

[Deverry]

HI,

Databases are not the same as other programmes ie Word. Each record has "space" allocated and although you may delete the record (form, report etc) the allocated space remains until you compact your database. To compact the database go to Tools, Database Utilities and click on Compact Database (Office 97/2000 not sure if identically the same in XP). After doing this you will see that your database size has reduced.

Ciao

 

[hair]

Thanks that was what I was looking for. But tell me, let's say I erase 5000 records in a table. The size remains the same. And after I put some other 5000 in that table. The records will go in the same place so the size will remain the same or it will be double?

 

[Deverry]

If you delete records/forms etc and don't compact the database and then add new records/forms etc the database will increase in size. It is always a smart move to compact any database after major changes.


"If I knew then what I know now - what would I know now?" - Dev