Can't Get Update Query To Work

  • Thread starter Thread starter Robert
  • Start date Start date
R

Robert

I created an update query to add three different break
times together and update a field
named "intTotalBreakTime" in a record. I have created the
following record fields.


dtmMBreakStartTime
dtmMBreakEndTime
dtmLunchStartTime
dtmLunchEndTime
dtmAFBreakStartTime
dtmAFBreakEndTime
intTotalBreakTime

The problem I am having is that if I have no times input
in either the morning and afternoon break time fields or
lunch time fields, the expression does not update the
field. Do I have to have data is all three time fields to
make the update work?



Expression



DateDiff("n",[dtmMBreakStartTime],[dtmMBreakEnd
Time])/60+DateDiff("n",[dtmLunchStartTime],
[dtmLunchEndTime])/60+DateDiff("n",[dtmAFBreakStartTime],
[dtmAFBreakEndTime])/60
 
Robert wrote:
The problem I am having is that if I have no times input
in either the morning and afternoon break time fields or
lunch time fields, the expression does not update the
field. Do I have to have data is all three time fields to
make the update work?
Click to expand...
Expression

DateDiff("n",[dtmMBreakStartTime],[dtmMBreakEnd
Time])/60+DateDiff("n",[dtmLunchStartTime],
[dtmLunchEndTime])/60+DateDiff("n",[dtmAFBreakStartTime],
[dtmAFBreakEndTime])/60
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

If one of the column values is NULL the expression evaluates to NULL.
You can try the Nz() function, but you'll have to put in default times
for each column value. E.g.:

DateDiff("n",
Nz(dtmMBreakStartTime,"10:00:00"),
Nz(dtmMBreakEnd, "10:15:00") )

This would set default times as follows:

dtmMBreakStartTime "10:00:00"
dtmMBreakEnd "10:15:00"

If your column includes a date, be sure to include a default date as
part of the default time.

Hint: cdate(format(date() & " 10:15:35", "m/d/yy hh:nn:ss"))

- --
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQGEIuYechKqOuFEgEQInCgCgzjsWqn18zDiTh6N3MpXX+QhnPCcAoPa+
bAXfia8TCoUqHXoJxepItM5q
=fMvZ
-----END PGP SIGNATURE-----
 
Back
Top