Date Range Query Problem!

[Reynet01]

I know the following parameter query is probably bloated and uneccassary but it's all I know how to do to make it work. I have on Problem though the following works but the
{Dictations.dtScribed}field gets updated when a job comes back from QA and it throws off the report. there is however a field called {Dictations.DtAwaitQA} that will work for the jobreport and it will never change even when returned from QA, Only problem with that is not all jobs will go to QA. So I need to make the report lookup records that were transcribed "Dictations.dtScribed" within the date range THAT HAVE NOT GONE TO QA and I need the jobs that were awaiting QA "Dictations.DtAwaitQA" in that same range.

Date Fields Null if no activity for that record

Any Help with this is much appreciated.





if {?DictatorID}<>"ALL" and
{?WorkType}<> 9999999999.00 and
{?TranID}<> "ALL"
then{Dictations.state}>14 and
{Dictations.dtScribed}={?DateRange}and
{Dictations.DictatorID}={?DictatorID}and
{Dictations.dWorkType}={?WorkType}and
{Dictations.TranID}={?TranID}

Else
if {?DictatorID}<>"ALL" and {?WorkType}<> 9999999999.00
then{Dictations.state}>14 and
{Dictations.dtScribed}={?DateRange}and
{Dictations.DictatorID}={?DictatorID}and
{Dictations.dWorkType}={?WorkType}

Else
if {?DictatorID}<>"ALL" and {?TranID}<> "ALL"
then{Dictations.state}>14 and
{Dictations.dtScribed}={?DateRange}and
{Dictations.DictatorID}={?DictatorID}and
{Dictations.TranID}={?TranID}

Else
if {?WorkType}<> 9999999999.00 and {?TranID}<> "ALL"
then{Dictations.state}>14 and
{Dictations.dtScribed}={?DateRange}and
{Dictations.dWorkType}={?WorkType}and
{Dictations.TranID}={?TranID}



Else
if {?DictatorID}<>"ALL"
then{Dictations.state}>14 and
{Dictations.dtScribed}={?DateRange}and
{Dictations.DictatorID}={?DictatorID}

Else
if {?WorkType}<> 9999999999.00
then{Dictations.state}>14 and
{Dictations.dtScribed}={?DateRange}and
{Dictations.dWorkType}={?WorkType}

Else
if {?TranID}<> "ALL"
then{Dictations.state}>14 and
{Dictations.dtScribed}={?DateRange}and
{Dictations.TranID}={?TranID}


Else{Dictations.state}>14 and
{Dictations.dtScribed}={?DateRange}

If knowledge were power I would be a AAA battery!

 

[Madawc]

Are you allowing for nulls in your date range?

Having come to Crystal from mainframe languages, I got a 'cultural shock' when encountering null. It means 'no data': Mainframe languages mostly treat this as the same as zero.
It is actually a finer shade of meaning, the difference between 'Yes, we have no bananas' and 'I don't know how many bananas we have, it could be some, it could be zero'. In Crystal, the entry is 0 or null and can be tested for.
Note that Crystal assumes that anything with a null means that the field should not display. Always begin with something like

if isnull({your.date}) 
or {your.date} in [{Start-Date} to {End-Date}) then "OK"
else "not"

It helps to give your Crystal version, since newer versions have extra options, and some extra problems. I use Crystal 8.5.

Madawc Williams (East Anglia, UK)

 

[Reynet01]

It is a MSDE backend and the field does allow nulls I use Crystal version 9

If knowledge were power I would be a AAA battery!

 

[Madawc]

That's probably the cause, then. My method should work in Crystal 9.

Madawc Williams (East Anglia, UK)

 

[Reynet01]

You will have to forgive me I am new to this and I am not sure how to apply your code to my query.

If knowledge were power I would be a AAA battery!

 

[Madawc]

Do a formula field that returns the answers. Then test for them in your selection criteria, in place of the direct test of date.


Madawc Williams (East Anglia, UK)