Re: Please Help! Ref Article ID 210504

No sir, I don't think so.

ID InTime OutTime Elapsed
1 4/1/2005 9:00:00 AM 4/1/2005 9:30:00 AM 40
2 4/1/2005 10:10:00 AM 4/1/2005 10:25:00 AM 35
5 4/1/2005 11:00:00 AM 4/1/2005 11:30:00 AM 45
3 4/1/2005 12:15:00 PM 4/1/2005 12:30:00 PM 60
4 4/1/2005 1:30:00 PM 4/1/2005 1:45:00 PM



It is computing the time between each Out Time and the next Occurring In
Time. That's what you said you wanted. Did I misunderstand the requirement?

--
Randy Harris
(tech at promail dot com)


"Telobamipada" <Telobamipada@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A957C765-8398-480E-ADEB-DD31540434A1@xxxxxxxxxxxxxxxx
> 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" <Telobamipada@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> > news:DF88BDB9-8F58-4191-9315-FAE5CE4ED945@xxxxxxxxxxxxxxxx
> > > 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" <Telobamipada@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in
message
> > > > news:A0095597-FE99-4370-82AD-C41892E70104@xxxxxxxxxxxxxxxx
> > > > > 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)
> > > >
> > > >
> > > >
> >
> >
> >


.

Relevant Pages

  • Re: Report for single record
    ... If you just need to look up a single value from another table so you can limit the report to the correct value, DLookup() should do that for you. ... After retrieving the correct value from the table, you can then open the report filtered to just that one record by using the WhereCondition of OpenReport. ... Alternatively it may be possible to create a query that uses both your main table and the lookup table, and use this query as the RecordSource for the report. ... the project manager is set ...
    (microsoft.public.access.reports)
  • Re: Report for single record
    ... I used Dlookup as the source for the control on the report. ... Alternatively it may be possible to create a query that uses both your main ... In my new DB, the project manager is ...
    (microsoft.public.access.reports)
  • RE: I have a problem with Dlookup via an unbound control on report
    ... You asked "Why do you need to use DLookup when you might have been able to ... matches that parameter against a complex postcode matching query. ... I needed a way to get the chosen parameter value into the report so that the ... "Duane Hookom" wrote: ...
    (microsoft.public.access.reports)
  • Re: How do I use table data as a variable in a macro query
    ... I must look into that DLookup thing some other time. ... Your suggestion of using "a value on the report" is not really possible. ... can't just say "a value from a table or query". ... form in the To argument of the macro. ...
    (microsoft.public.access.macros)
  • Re: DLookup that isnt working
    ... The 3rd argument for DLookup needs to be a string like this: ... > LinkField ... > In a report based on tblTransactions, I have in the detail area the ... > It is pulling up a Combined field from the query, but I get the same field ...
    (microsoft.public.access.reports)