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?
LVL 5
bfuchsAsked:
Who is Participating?
 
bfuchsAuthor Commented:
Hi Experts,

I got it to work as follows
MissingNotes: (Abs(DateDiff("n",TimeSerial(Nz([Shift_From_Hour]),Nz([Shift_From_Minute]),0),TimeSerial(Nz([Shift_To_Hour]),Nz([Shift_To_Minute]),0)))/120)-nz(DCount("*","SNVNotesQry","SNV_ID = '" & [SNV_ID] & "'"),0)

Open in new window

And then at the conditional formatting I put in
[MissingNotes]>0

Open in new window


Thanks,
Ben
0
 
Fabrice LambertFabrice LambertCommented:
Since you didn't mention how you'll display your result, you can export your datas to Excel, and use Excel's conditional formatting.
0
 
NorieVBA ExpertCommented:
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.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
bfuchsAuthor Commented:
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
0
 
bfuchsAuthor Commented:
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
0
 
NorieVBA ExpertCommented:
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.
0
 
bfuchsAuthor Commented:
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
0
 
Gustav BrockCIOCommented:
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
0
 
bfuchsAuthor Commented:
@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
0
 
Gustav BrockCIOCommented:
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
0
 
bfuchsAuthor Commented:
@Gustav,

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

Thanks,
Ben
Untitled.png
0
 
bfuchsAuthor Commented:
Problem solved.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.