Record selection

[gilsonr]

I need to select records based on the follwoing criteria

Job type not equal to cancelled and
Mailing date between X and Y and
Completion date is null

or

Job type not equal to cancelled and
Calldate1 between X and Y and
Completion date is null

or

Job type not equal to cancelled and
calldate2 date between X and Y and
Completion date is null

Any suggestions

 

[pgtek]

Hi
you need to create formula fo filter this

ex:
if
((Job type <> to cancelled and
Mailing date between X and Y and
isnull(Completion date))

or

(Job type <> to cancelled and
Calldate1 between X and Y and
isnull(Completion date))

or

(Job type <> to cancelled and
calldate2 date between X and Y and
isnull(Completion date)))

then
do this
else
something

hope this helps

cheers

pgtek

 

[kenhamady]

Why Not:I would use the following selection formula:

isnull(Completion date)) and
(Job type <> to cancelled and

( Mailing date in X to Y or
Calldate1 in X to Y or
calldate2 in X to Y )


Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[gilsonr]

Thanks for the help, I was on the right lines but I could not get the syntax to work.

I have created the following code in the Record Selection formula section

isnull({CAMP_MGT.Date Completed})) and
({CAMP_MGT.Job type} <> 9 and

({CAMP_MGT.Mailing File Due Date} in DateTime (2003, 09, 01, 00, 00, 00) to DateTime (2003, 11, 30, 00, 00, 00) or
{CAMP_MGT.Call date 1} in DateTime (2003, 09, 02, 00, 00, 00) to DateTime (2003, 12, 01, 00, 00, 00) or
{CAMP_MGT.Call date 2} in DateTime (2003, 09, 02, 00, 00, 00) to DateTime (2003, 12, 01, 00, 00, 00) or
{CAMP_MGT.Call date 3} in DateTime (2003, 09, 02, 00, 00, 00) to DateTime (2003, 12, 01, 00, 00, 00) or
{CAMP_MGT.Call date 4} in DateTime (2003, 09, 02, 00, 00, 00) to DateTime (2003, 12, 01, 00, 00, 00))

But I still get the following message

'The remaing text does not appear to be part of the formula'

 

[lbass]

Change the parens so that the first two lines read:

isnull({CAMP_MGT.Date Completed}) and
{CAMP_MGT.Job type} <> 9 and

-LB

 

[Ngolem]

your bracketing is set wrong

try this

isnull({CAMP_MGT.Date Completed}) and
{CAMP_MGT.Job type} <> 9 and

({CAMP_MGT.Mailing File Due Date} in DateTime (2003, 09, 01, 00, 00, 00) to DateTime (2003, 11, 30, 00, 00, 00) or
{CAMP_MGT.Call date 1} in DateTime (2003, 09, 02, 00, 00, 00) to DateTime (2003, 12, 01, 00, 00, 00) or
{CAMP_MGT.Call date 2} in DateTime (2003, 09, 02, 00, 00, 00) to DateTime (2003, 12, 01, 00, 00, 00) or
{CAMP_MGT.Call date 3} in DateTime (2003, 09, 02, 00, 00, 00) to DateTime (2003, 12, 01, 00, 00, 00) or
{CAMP_MGT.Call date 4} in DateTime (2003, 09, 02, 00, 00, 00) to DateTime (2003, 12, 01, 00, 00, 00))

I am assuming here that you want the record selected if the record satisfies one or maore of the date requirements.

Your bracket probs were in the first 2 lines

Jim Broadbent

 

[gilsonr]

Thanks for all you help its very much appreciated. I have amended the code above to fix the problem that I was having with the bracket and it works OK.

But the records I now get are lower than expected, as I have run the same selection in Access.

My guess at the formula that I need to produce is something like this

({CAMP_MGT.Job type} <> 9 and
({CAMP_MGT.Mailing File Due Date} in DateTime (2003, 09, 01, 00, 00, 00) to DateTime (2003, 11, 30, 00, 00, 00) and
isnull({CAMP_MGT.Date Completed})) or
({CAMP_MGT.Job type} <> 9 and
({CAMP_MGT.Call date 1} in DateTime (2003, 09, 02, 00, 00, 00) to DateTime (2003, 12, 1, 00, 00, 00) and
isnull({CAMP_MGT.Date Completed})) or
({CAMP_MGT.Job type} <> 9 and
({CAMP_MGT.Call date 2} in DateTime (2003, 09, 02, 00, 00, 00) to DateTime (2003, 12, 1, 00, 00, 00) and
isnull({CAMP_MGT.Date Completed})) or
({CAMP_MGT.Job type} <> 9 and
({CAMP_MGT.Call date 3} in DateTime (2003, 09, 02, 00, 00, 00) to DateTime (2003, 12, 1, 00, 00, 00) and
isnull({CAMP_MGT.Date Completed})) or
({CAMP_MGT.Job type} <> 9 and
({CAMP_MGT.Call date 4} in DateTime (2003, 09, 02, 00, 00, 00) to DateTime (2003, 12, 1, 00, 00, 00) and
isnull({CAMP_MGT.Date Completed}))

Do I need to use an If then else query to get the correct seletions. At present the only error message that I get tells me that Iam missing a ')' at the end.

 

[kenhamady]

You shouldn't need to repeat lines that are exactly the same if you bracketed correctly. Post the formula you are using.

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[gilsonr]

Formula as requested


isnull({CAMP_MGT.Date Completed}) and
isnull({CAMP_MGT.Dialler File 1 Loaded}) and
isnull({CAMP_MGT.Dialler File 2 Loaded}) and
isnull({CAMP_MGT.Dialler File 3 Loaded}) and
{CAMP_MGT.Job type} <> 9 and
({CAMP_MGT.Mailing File Due Date} in DateTime (2003, 10, 01, 00, 00, 00) to DateTime (2003, 12, 31, 00, 00, 00) or
{CAMP_MGT.Call date 1} in DateTime (2003, 10, 02, 00, 00, 00) to DateTime (2004, 01, 01, 00, 00, 00) or
{CAMP_MGT.Call date 2} in DateTime (2003, 10, 02, 00, 00, 00) to DateTime (2004, 01, 01, 00, 00, 00) or
{CAMP_MGT.Call date 3} in DateTime (2003, 10, 02, 00, 00, 00) to DateTime (2004, 01, 01, 00, 00, 00) or
{CAMP_MGT.Call date 4} in DateTime (2003, 10, 02, 00, 00, 00) to DateTime (2004, 01, 01, 00, 00, 00))

 

[kenhamady]

Looks right but your dates keep changing.

If you are getting too few records I would find a record that shows up in MS Access and doesn't in CR and check the value for each of the fields used above.

Also, do you have times in your data? If so, you might need 2003,12,31,23,59,59

Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Tips and Tricks / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com