View Single Post
  #5  
Old April 15th, 2005, 07:49 PM
Telobamipada
external usenet poster
 
Posts: n/a
Default

Randy, One small problem. Your example is showing the elapsed time for each
occurance. What I need to do is show the elapsed time from the "Out Time" of
each occurance to the "In Time" of the next occurance. Basically, the first
occurance wouldn't have an elapsed time because there wouldn't be anything
previous to it for that day. Thats why I was attempting to use the DLookUp,
this allowed me to pull the "Out Time of the previous Occurance and compare
it to the "In Time" of next occurance.
I was also getting this error; Syntax Error (missing operator) in query
expression 'Min(In Time)'.

"Randy Harris" wrote:

You are correct that you would not be able to use the DLookup method
described in that article, because your records won't be ordered by ID
number. Frankly, I don't think much of that method anyway, your ID field
would always have to be perfectly sequential. That could prove to be a real
nightmare in the future.

I was also getting a

This is one of those problems that have many different solutions. One
solution is to do the math in a query. For example:

SELECT
[IN-OutDates].ID,
[IN-OutDates].InTime,
[IN-OutDates].OutTime,
DateDiff("n",[OutTime],DMin("InTime","In-OutDates","[InTime]#" &
[OutTime] & "#")) AS Elapsed
FROM
[IN-OutDates]
ORDER BY
[IN-OutDates].InTime;

ID InTime OutTime Elapsed
1 4/1/2005 9:00:00 AM 4/1/2005 9:30:00 AM 30
2 4/1/2005 10:00:00 AM 4/1/2005 10:30:00 AM 30
5 4/1/2005 11:00:00 AM 4/1/2005 11:30:00 AM 30
3 4/1/2005 12:00:00 PM 4/1/2005 12:30:00 PM 30
4 4/1/2005 1:00:00 PM 4/1/2005 1:30:00 PM




I pasted the output from the query above, I hope it comes through.

The calculation could also be done in the report, using the same sort of
date math.

Hope this helps.

--
Randy Harris
(tech at promail dot com)


"Telobamipada" wrote in message
news
Randy, thanks for the reply. Here's an example:

If you have a table with the 3 fields I have described and populate it

with
4 records as shown below:

ID In Time Out Time

1 4/1/2005 09:00 AM 4/1/2005 09:30 AM
2 4/1/2005 10:00 AM 4/1/2005 10:30 AM
3 4/1/2005 12:00 AM 4/1/2005 12:30 AM
4 4/1/2005 01:00 PM 4/1/2005 01:30 PM

Now, using the DLookUp formula posted in Article 210504 for "Queries"
everything is fine. I now need to add a record, say for the period 11:00

to
11:30. Now my table looks like this:

ID In Time Out Time

1 4/1/2005 09:00 AM 4/1/2005 09:30 AM
2 4/1/2005 10:00 AM 4/1/2005 10:30 AM
3 4/1/2005 12:00 AM 4/1/2005 12:30 AM
4 4/1/2005 01:00 PM 4/1/2005 01:30 PM
5 4/1/2005 11:00 AM 4/1/2005 11:30 AM

If I set up a macro to sort the table based on the "In Time" field when

the
record is saved from the form, the table looks like this:

ID In Time Out Time

1 4/1/2005 09:00 AM 4/1/2005 09:30 AM
2 4/1/2005 10:00 AM 4/1/2005 10:30 AM
5 4/1/2005 11:00 AM 4/1/2005 11:30 AM
3 4/1/2005 12:00 AM 4/1/2005 12:30 AM
4 4/1/2005 01:00 PM 4/1/2005 01:30 PM

The way the DLookUp formula works is based on the "ID" field which is an
auto number and primary key. I've attempted to use the DLookUp with the

"In
Time" field and it is obviously not going to work...
I simply need to set up a report which will show me the the elapsed time
between the "Out Time" of the first event for that day and the "In Time"

for
the next event , in sequencial order. The query is set up to have the user
enter a date they are after. The report when finished would look like

this:

Procedures for 4/1/2005

In Time Out Time
Elapsed Time

4/1/2005 09:00 AM 4/1/2005 09:30 AM 30
4/1/2005 10:00 AM 4/1/2005 10:30 AM 30
4/1/2005 11:00 AM 4/1/2005 11:30 AM 30
4/1/2005 12:00 AM 4/1/2005 12:30 AM 30
4/1/2005 01:00 PM 4/1/2005 01:30 PM 30

I hope this helps you to understand what I'm trying to accomplish here, I

am
no expert by any stretch of the imagination! Since the DLookUp obviously
will not work with a date field I thought I would add a field to my table

and
form such as "Patient Sequence Number" that the user would enter and base

my
DLookUp on that... I'm thinking there's a better way...


Randy Harris" wrote:

"Telobamipada" wrote in message
...
While I found previous comments helpful, I still have an issue I can't
seem to figure out. Say I have a table with an "ID" field, an "In
Date/Time" and
"Out Date/Time". I want to calculate the elapsed time between events

("Out
Time"
of the previous event to the "In Time" of the next event). Everything
works fine until I forget to add a record and need to add a record

with
times which fit between 2 previous records (Based on the In Time).

The
new
record is entered as the last record in the table and the DLookUp is

now
finding the "Out Time" of the previous record in the table but not the
previous event (Based on the "In Time" and "Out Time" fields). What I

want
is
for the DLookUp to find the "Out Time" of the previous event based on

the
"In
Time" and not the "ID" field for cases like this where a record may be
entered later. Any help
would be greatly appreciated!


--
If you can read this thank a teacher...
If your reading it in english, thank a veteran!

It's difficult to understand what you are trying to do. Suggest you

post
some sample data to provide a clearer explanation.

BTW - your tag line is a nice sentiment. Given its content, however,

you
should correct the grammatical error in it.

(my 2 cents worth for both suggestions)