asked on

Need to calculate two fields, notes vs. hours.

Hi experts,

I have two fields in a query, one for total minutes of a shift (Duration) and one displaying total count of notes related (CountOfNotes).

For every two hours there must be a note.

Now I need to display in bold (conditional formatting) every record that has less notes than required.

How do I accomplish that in a non performance cost fashion?

Fabrice Lambert

Since you didn't mention how you'll display your result, you can export your datas to Excel, and use Excel's conditional formatting.

Norie

You could get the notes per hour with this expression:

NotesPerHour:[CountOfNotes]/[Duration]*60

That could be used in conditional formatting but unfortunately not in a query, you would need to do that in a form/report based off the query.

bfuchs

ASKER

Hi experts,

unfortunately not in a query, you would need to do that in a form/report based off the query

Yes, I have a form bound to that query, excuse me for leaving that out.

I tried yours as follows

NotesPerHour: DCount("*","SNVNotesQry","SNV_ID = '" & [SNV_ID] & "'")/Abs(DateDiff("n",TimeSerial(Nz([Shift_From_Hour]),Nz([Shift_From_Minute]),0),TimeSerial(Nz([Shift_To_Hour]),Nz([Shift_To_Minute]),0)))*60

Open in new window

See attached what I'm getting.

Also what do I put in the conditional formatting logic?
Untitled.png

bfuchs

ASKER

Just to clarify the logic needed in formatting.

If there are shifts for less then two hours then no note are required.
Same goes if shift are 5 hours, only 2 notes are required. (for the two sets of 2 hours each)
In other words, only two hours counts.

Thanks,
Ben

Norie

Ben

For the errors you'll need to check for 0 values being returned by the 'duration' calculation.

Apart from that the results are showing the no of notes per hour.

Not quite following your logic for the formatting.

bfuchs

ASKER

Hi,

For the errors you'll need to check for 0 values being returned by the 'duration' calculation.

The duration should either has some positive numbers or zero (as seen in attached).

Not quite following your logic for the formatting

I basically need to make sure each two hours shift has a note record.

Therefore for 4 hours I need two notes, same is for 5 hours, but for 6 hours I already need 3 notes.
For 1 hour no no needed, same for 1.5, but when there are already 2 hours one note record is required.

Hope that's clear.

Thanks,
Ben

Gustav Brock

Try with:

NotesPerHour: Int(DCount("*","SNVNotesQry","SNV_ID = '" & [SNV_ID] & "'")/Abs(DateDiff("n",TimeSerial(Nz([Shift_From_Hour],0),Nz([Shift_From_Minute],0),0),TimeSerial(Nz([Shift_To_Hour],0),Nz([Shift_To_Minute],0),0)))*60)

Open in new window

/gustav

bfuchs

ASKER

@Gustav,

Still getting errors, see attached.

And what about the logic?

I mainly need a yes/no field in my query indicating if missing notes.

Thanks,
Ben
Untitled.png

ASKER CERTIFIED SOLUTION

bfuchs

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Gustav Brock

If that works, I would say that you can skip all the Nz stuff, as Nz([Shift_From_Hour]) will return an empty string for Null values, and an empty string is not an accepted parameter value for TimeSerial , thus it should fail.

/gustav

bfuchs

ASKER

@Gustav,

Tried removing just the first instance of nz() and already got some errors.
See attached last column.

Thanks,
Ben
Untitled.png

bfuchs

ASKER

Problem solved.