Re: Please help!

1. Decimal format? As in 2.95 hours or something? Divide the number of minutes by 60.

2. If you have a date and time (not just a time), it works. If not, you will need to use an IIf() expression to add 1 day to the end time if it is less than the start time and you want to assume that this means it wrapped past midnight. Use DateAdd() to add 1 days.

3. To block the user from being able to enter 2 records with the same start-time and end-time:
a) Open the table in design view
b) Open the Indexes box (toolbar)
c) On a blank row enter:
StartTimeEndTime DATE-START
and set the Unique property (lower pane of the dialog) to Yes.
Then on the next line, leave the name blank and enter the 2nd field:
TIME-FINISH
d) Save the table.
(Note that this may not work perfectly, due to floating point errors.)

4a) To sum the minutes in a form, add a text box to your Form Footer, and enter:
=Sum([Minutes])

b)You can do the same in a Report Footer.

c) In a query, depress the Total button on the Toolbar.
Access adds a Total row to the grid.
Under the Minutes field, choose Sum.

--
Allen Browne - Microsoft MVP. Perth, Western Australia
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"joet5402" <joet5402@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:C93A0797-0178-4B04-8C1E-7AF82072B8AC@xxxxxxxxxxxxxxxx
Allen, it seems to have worked, but I have a few more questions: first, how
can I display the duration time in decimal format? Second, when there is a
start/finish time of 22:30-01:30 for example, it displays a negative number;
how can I format it to reflect a more accurate time? Thirdly (when I'm
finally able to finish the previous 2 problems), how can I create rows that
do not duplicate the original row?
Lastly, how do I go about summing the minutes?

Sorry for all the questions...this database has been giving me a hard time.
--
Joe


"Allen Browne" wrote:

If your table has fields named DATE-START and TIME-FINISH, Access should
understand what you are talking about.

The fact that it pops up a parameter dialog means it cannot find those
names. Perhaps the table is not in the query. Perhaps the spelling is
different (e.g. a space.) Or perhaps they are calculated fields that are not
available at the time Access needs them.

"joet5402" <joet5402@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:436BFC5A-163D-45F0-B044-7DA124015AE8@xxxxxxxxxxxxxxxx
> Allen, I inserted it into an existing query, and when I went to run it, > it
> prompted an "Enter Parameter Value: Date-Start" then an "Enter > Parameter
> Value: Time-Finish". Is there a way I can draw the dates already in > the
> query to use in figuring the difference in time without being prompted?
> Should I have a Total row?
> -- > Joe
>
>
> "Allen Browne" wrote:
>
>> Whatever you like.
>>
>> "joet5402" <joet5402@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> news:428AFC54-EE83-4F3A-9572-782826C94E41@xxxxxxxxxxxxxxxx
>> > Allen, should this be in a new query or in an existing query
>> > that contained the previously mentioned fields?
>> > -- >> > Joe
>> >
>> >
>> > "Allen Browne" wrote:
>> >
>> >> Sorry: there should be a comma between the field names:
>> >> Minutes: DateDiff("n", [DATE-START], [TIME-FINISH])
>> >>
>> >> "joet5402" <joet5402@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> news:2CD5A749-D5CE-4E9C-9679-7DEF7D5616D9@xxxxxxxxxxxxxxxx
>> >> > Allen, after I deleted the "Duration" field, I entered the >> >> > Minutes:
>> >> > DateDiff("n", [DATE-START] [TIME-FINISH]) formula into a query, >> >> > but
>> >> > I
>> >> > got
>> >> > an
>> >> > error message saying, "The expression you entered contains >> >> > invalid
>> >> > syntax.
>> >> > You may have entered an operand without an operator". How can >> >> > this
>> >> > be
>> >> > fixed?
>> >> > -- >> >> > Joe
>> >> >
>> >> >
>> >> > "Allen Browne" wrote:
>> >> >
>> >> >> Set the Default View property of your form to:
>> >> >> Continuous
>> >> >>
>> >> >> Now you put only *one* row of controls in the form's Detail
>> >> >> section.
>> >> >> When you run the form, it repeats the row for every record.
>> >> >>
>> >> >> Remove the Time-Duration field from your table.
>> >> >> Instead, create a query, and type this into a fresh column in >> >> >> the
>> >> >> Field
>> >> >> row:
>> >> >> Minutes: DateDiff("n", [DATE-START] [TIME-FINISH])
>> >> >> You can then sum the minutes and do whatever you want.
>> >> >>
>> >> >> More details about there in:
>> >> >> Calculating elapsed time
>> >> >> at:
>> >> >> http://allenbrowne.com/casu-13.html
>> >> >>
>> >> >> "joet5402" <joet5402@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
>> >> >> news:D129E73B-3126-418B-A48E-D43F8F2F2C84@xxxxxxxxxxxxxxxx
>> >> >> > I'm currently working on the following form (from a Field >> >> >> > List):
>> >> >> >
>> >> >> > (these are in form header section)
>> >> >> > TECHNICIAN-START DATE-START TIME-FINISH TIME-DURATION
>> >> >> >
>> >> >> > TECHNICIAN-START DATE-START TIME-FINISH TIME-DURATION
>> >> >> > TECHNICIAN
>> >> >> > TECHNICIAN
>> >> >> > TECHNICIAN
>> >> >> > TECHNICIAN
>> >> >> > TECHNICIAN
>> >> >> > etc.
>> >> >> > (all these are text boxes in Detail section)
>> >> >> >
>> >> >> > This is my problem: How do I get the fields below the initial
>> >> >> > line
>> >> >> > to
>> >> >> > not
>> >> >> > duplicate any of the data from the initial line >> >> >> > (TECHNICIAN-START
>> >> >> > DATE-START
>> >> >> > TIME-FINISH TIME-DURATION)? Also, how do I create a total
>> >> >> > duration
>> >> >> > time?

.

Loading