Round hours to nears quarter or 1/2 hr

[rascal]

Hi all:
I have been trying to figure out how to get my query to round the hours to
the nearest 1/2 or quarter hr. One of the problems I have is that we use the
following break-down for quarter & half hours:
7:15 would be 7.25hrs
7:30 would be 7.30hrs
7:45 would be 7.75hrs
So if some worked 7hrs and 10mins it would need to round off to 7.25hrs
Thanks in advance for any help

 

[rascal]

Sorry correction to the 7:30 it should be 7.5hrs

 

[Steve Schapel]

Rascal,

Assuming you have a Date/Time data type field, something like this...
HoursWorked: Hour([TimeField])+((Minute([TimeField])+7.5)\15)/4

 

[rascal]

Thanks Steve:
It worked wonderfully. I have another problem maybe you or someone else can
help me with. I have a simple database that has 2 tables: agents & dailyhrs,
1 query and 1 form. The form has a combo box to select a name, 2 date time
stamp fields each with a button, 1 called log in & another called log out. A
text box that gets the ID of the agent logged into the system. When the log
in button is clicked the date time stamp fills in and the database closes.
The same for the log out button.
I have my main database which gets the information from the database
mentioned above to convert the log in & log out times to hrs worked.
What I have noticed is that the agents don't scroll back to their log in
record to log out but create a new record. Is there a way to have the form
open automatically to their log in record for that day when they open the
database to log out.
Thanks

Rascal,

Assuming you have a Date/Time data type field, something like this...
HoursWorked: Hour([TimeField])+((Minute([TimeField])+7.5)\15)/4

--
Steve Schapel, Microsoft Access MVP

Hi all:
I have been trying to figure out how to get my query to round the hours to
the nearest 1/2 or quarter hr. One of the problems I have is that we use the
following break-down for quarter & half hours:
7:15 would be 7.25hrs
7:30 would be 7.30hrs
7:45 would be 7.75hrs
So if some worked 7hrs and 10mins it would need to round off to 7.25hrs
Thanks in advance for any help

 

[Steve Schapel]

Rascal,

I think I would need more detailed information to know for sure. But I
think the query that the form is based on, you could use a Criteria of
Is Null for the log out time field, it will probably be a good start.

 

[rascal]

Hi Steve:
I put the Is Null in the criteria field of the query and ran it which gives
me all the empty log outs but I need something ie a code I can put in the on
open even of the form that would check the date and FOSUserName and if the
log out field is blank open the form to that record and if there is no empty
record open up to a new record. I am not familiar with VB code so how would
I go about doing this.
Thanks for your help

 

[Steve Schapel]

Rascal,

Use criteria in the query that the from is based on to manage this. Put
Date() in the date field, and the user as applicable (sorry, I don't
know enough detail to advise explicitly). This will be easier.

 

[rascal]

Hi Steve:
I have tried that it it seems to work. In the query the form is based on I
have 3 criteria set now. In the dateworked field i have set Date(); in the
logout field i have set Is Null and in the user field I have set
FOSUserName() as the criteria. I have set them all on the same long so all
must be true for the form to open to an existing record and if not it opens
to a empty log in form. Much easier and simpler then trying to figure out
code to do it.
Thanks again for your help.

 

[Steve Schapel]

Rascal,

Brilliant. Thanks for letting us know. Best wishes with the rest of
your project.