Record Selection Criteria

[HayesBMH]

After trying many things over many days, I need help figuring out why the selection criteria I am using is not working. I am accessing an OBDC datasource using Crystal version 10. I am trying to pull records canceled or added on a given date which are identified using certain criteria. Also, when I "SHOW SQL query", it does not show the OR statement used to identify cancels and adds.The following code pulls the canceled record for the day but not the added record. If I switch the order in the OR (add criteria first), nothing is pulled.

{appt.resunit_id} = {?ResUnitID} AND
{res.restype_id} = 2 AND
(({schedlog.audittype_id} = 3 and
{apptcancel.last_sched_datetime} = (CurrentDate) and
{apptcancel.canceled_datetime} = (CurrentDate)) OR (({appt.start_datetime} = (CurrentDate)) and
(Date({appt.entered_datetime}) = Date({appt.start_datetime})) and
({schedlog.audittype_id} = 2)))

Any help would be greatly appreciated!!!

 

[jdemmi]

I think you need to wrap you entire OR in parens....

....and

({apptcancel.canceled_datetime} = (CurrentDate)) OR (({appt.start_datetime} = (CurrentDate)))

and......

-- Jason
"It's Just Ones and Zeros

 

[HayesBMH]

THANKS Jason. I did use parenthesis around the OR though what I posted may not show it correctly because I did some editing to take out some formulas I tried before posting. It did not work with the parens either though. The following is more accurate.

{appt.resunit_id} = {?ResUnitID} AND
{res.restype_id} = 2 AND
(({schedlog.audittype_id} = 3 and
{apptcancel.last_sched_datetime} = CurrentDate and
{apptcancel.canceled_datetime} = CurrentDate) OR ({appt.start_datetime} = CurrentDate and
Date({appt.entered_datetime}) = Date({appt.start_datetime}) and {schedlog.audittype_id} = 2))

 

[jdemmi]

In the second half of your OR, where you are dealing with start date...

({appt.start_datetime} = CurrentDate and Date({appt.entered_datetime}) = Date({appt.start_datetime})

try replacing it with this...


({appt.start_datetime} = CurrentDate and appt.entered_datetime}) = CurrentDate })

-- Jason
"It's Just Ones and Zeros

 

[jdemmi]

oops...i made typos myself..

({appt.start_datetime} = CurrentDate and {appt.entered_datetime} = CurrentDate)

-- Jason
"It's Just Ones and Zeros

 

[HayesBMH]

THANKS Jason! That was actually something that I had not tried. Unfortunately it still did not work.

 

[jdemmi]

Have you tried removing all of the other criteria from the report except the criteria needed to locate only the ADDED record?

Which, from the formula alone appears to be

{appt.start_datetime} and {appt.entered_datetime}

So, when I have to troubleshoot issues like this I take my whole entire selection forumula out and paste it into notepad.

I then add it back ONE criteria at a time, like this..


Step 1 (since in your case the problem is around added records, start there) ONLY ADD this to the selection criteria {appt.start_datetime} = CurrentDate

Step 2 ({appt.start_datetime} = CurrentDate and {appt.entered_datetime} = CurrentDate)

Step 3 ({appt.start_datetime} = CurrentDate and {appt.entered_datetime} = CurrentDate and schedlog.audittype_id} = 2)

and so on....

when you stop getting the expected results you know where your issue is.

-- Jason
"It's Just Ones and Zeros