Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations Chris Miller on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Returning a Row with a Date change of a DateTime Field

Status
Not open for further replies.

Garyjr123

MIS
Sep 14, 2010
139
US
I have a report that I am trying to make work where I have a schedlog.audittype_id of 1 and I want rows returned with this audittype if two criteria are fulfilled.

1) schedlog.reschedreason_id in 1 to 22
2) schedlog.scheduled_datetime has a date change ONLY. Not just a time change within the same day.

This formula was provided to me but is not working the way I want and should this be a formula or should I put it in the Select Expert as a X-2 for suppression? Or go in the Record Selection of the Formula Workshop? How can I make this work?

(if {schedlog.audittype_id} = 1 and {schedlog.reschedreason_id} in [1, 10, 11, 12, 13, 14, 15, 16, 17, 18, 19, 2, 20, 21, 22, 23, 3, 4, 5, 6, 7, 8, 9] and DateValue({schedlog.scheduled_datetime}) <> DateValue({schedlog.scheduled_datetime}) Then TRUE Else False)
 
In thinking about this further, I realize there is nothing to compare the schedlog.scheduled_datetime to in the formula if it is associated with just schedlog.audittype_id of 1.

The way the schedlog table works is that the appointment is first scheduled as an audittype_id of 2 (new) then if the appointment is then moved the next row for the appointment is given a audittype_id of 1. The schedlog.scheduled_datetime can change two ways, by date, time, or both.

So, I want all rows that have an schedlog.audittype_id of 2 (no criteria) and all the rows that have an schedlog.audittype_id of 1 if the schedlog.reschedreason_id in 1 to 22 and if the DATE of the schedlog.scheduled_datetime is different then the DATE of the schedlog.scheduled_datetime of schedlog.audittype_id of 2 (comparing the two rows).
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top