Time intervals

[Dutch1956]

I have a table with a start and stop time and a table with 30 minute segments in each field, like 00:00, 00:30, 01:00, etc.
Using a query I would like the time period between the start and stop time to be shown in the minute segment fields like:
start time 01:45, stop time 03:00
I would like the following result:
00:00=0
00:30=0
01:00=0
01:30=0.50
02:00=1
02:30=1
03:00=0


etc.
I managed to get this to work in Excel using the following formula:
=MAX(0,MIN(D$1,$B2)-MAX(C$1,$A2))*48
where the start time is in A2 and the stop time in B2.
How can I get this to work in an Access query.

I tried to put this in the criteria of each time field:
DMAX(0,min([00:30],[stop time]-MAX([00:00],[start time]))*48,
but receive the message that the function contains the wrong number of arguments.
Any help is very much appreciated.


Bert.

[dr4ke]

Hi, I would recommend searching for each individual EXCEL formula's counterpart in Access and trying to work it out that way. Apologies, i'm not a master at Access but this should give you a starting point and will teach you loads

For example; http://www.pcreview.co.uk/forums/exc...-t3102723.html

Hope it helps

[orange]

You could try the Access Partition function - I'm not exactly sure what you're trying to do - but the link to Partition is
http://office.microsoft.com/en-us/ac...001228892.aspx

Good luck

[help_me_with_access]

that's got to be more complicated than simply using partition(). access is not the program for this, sir.

Try using a different program. the only way you're gonna get this to work in this program is to create multiple layers of stacked queries or just simply print in out with coding.

that's all you've got I think.

[dr4ke]

Why not let it run the code in Excel and import it into Access to do the rest of what you need?

[Dutch1956]

Thank you all for your response. Unfortunately none of the suggestions is a solution to the problem I am trying to solve.
I guess I will have to export the data to excel to make it work.
Thanks again.

Bert.