Basic question on Record Selection 2

[vbSun]

I want something like this to be done. What is the best way to do?

if {?TransDate}<> Empty then {TransactionNew.Date}={?TransDate}

where ?TransDate is my parameter field,
TransactionNew.Date is my field name

Am getting an error at {?TransDate}<> Empty , obviously because it is a date that is needed there. Is there any other go? Am using crytal reports 8, and if i dont pass a value to it, the parameter field is set to today. But parameter fields' value= today and parameter field value = Nothing has two meanings to me. Both are valid. How to solve this one?

Thanks for your expert advice..

Sunil

 

[Ngolem]

if {?TransDate}<> Empty then {TransactionNew.Date}={?TransDate}
***************************

What does &quot;Empty&quot; mean to you? Is it the same as meaning Null?? You cannot have a null value for a parameter.

Is it that &quot;Empty&quot; means that you want all values of {TransactionNew.Date} ???? If so then make sure the {?TransDate} is assigned as a date parameter and give it a ridiculously low value (eg. 01/01/1900) then try something like this

if totext({?TransDate},&quot;dd/MM/yyyy&quot = &quot;01/01/1900&quot; then
true
else
{TransactionNew.Date}={?TransDate};


Jim Broadbent

 

[vbSun]

Dear Jim

Thanks for the quick reply. But, this is what my selection formula look like.. i tried with values, it works fine, but yes, i want it to work with the date criteria also.

If {?TransID}<>&quot;-1&quot; then {TransactionNew.TransactionID}=val({?TransID})
else if {?CustID}<>&quot;-1&quot; then {TransactionNew.CustomerID}=val({?CustID})

--may be the missin part here is the transaction date thing..

else
true

Well i managed to discover up to this, now am stuck.. lol..this is my first week with Crystal Reports..

Thanks again for the fast reply.. will you pls try to suggest a best way to incorporate the date condition to this?

This is the situation.
1. If the user has his transactionid, let us show the details of that transaction. Else all transactions.
2. If the user has his customerid, then show the transactions made by that customer. Else select all customers
3. If the user has a transactiondate, select all transactions of that date. else select all transactions.

The user is free to choose any combinations of these three conditions..all three, any two, any one or none..

Pls help.. am really getting confused with the CR Syntax..

Thanks,
Sunil

 

[synapsevampire]

Try setting the date parameter to a default value of something not otherwise found in the database, such as 1/1/1970.

Then use:

(
if {?TransDate} <> cdate(1970,1,1) then {TransactionNew.Date}={?TransDate}
else
if {?TransDate} = cdate(1970,1,1) then
true
)
and
(
If {?TransID}<>&quot;-1&quot; then {TransactionNew.TransactionID}=val({?TransID})
else if {?CustID}<>&quot;-1&quot; then {TransactionNew.CustomerID}=val({?CustID})
else
true
)

Not sure about the intent of the rest of your record selection formula, you made no mention of it's intent so I just copied it over.

-k

 

[vbSun]

Dear synapsevampire,

This is the logic am trying to make working..

1. If the user has his transactionid, let us show the details of that transaction. Else all transactions.
======================================================
If {?TransID}<>&quot;-1&quot; then {TransactionNew.TransactionID}=val({?TransID})
======================================================
2. If the user has his customerid, then show the transactions made by that customer. Else select all customers
======================================================
if {?CustID}<>&quot;-1&quot; then {TransactionNew.CustomerID}=val({?CustID})
======================================================
3. If the user has a transactiondate, select all transactions of that date. else select all transactions.
======================================================
blank blank blank...
======================================================


Am a bit more confused by the structure of your if statement now.. what i have written (branching with ifs and elses) seems to have no sense at all compared to your usage of (AND)..

If I am clear, will you please help me through?

I will give a shot at the date criterion..

Thanks
Sunil

 

[synapsevampire]

Try going into the date parameter and setting the default value to 1/1/1970 as referenced above, then I'd change the {?CustID} parameter to a numeric to match your data type:

(
if {?TransDate} <> cdate(1970,1,1) then {TransactionNew.Date}={?TransDate}
else
if {?TransDate} = cdate(1970,1,1) then
true
)
and
(
If {?TransID} <> -1 then
{TransactionNew.TransactionID}= {?TransID}
else If {?TransID} = &quot;-1&quot; then
true
)
and
(
if {?CustID} <> -1 then
{TransactionNew.CustomerID} = {?CustID}
else if {?CustID} <> -1 then
true
)

Note that I intentionally qualify the ELSE conditions to assure SQL pass through to the database. By stating True, nothing is pass to the database, hence all rows are returned for that portion.

I have a FAQ in the Crystal Formulas forum that speaks of optimizing Record Selection Formulas too if you want to read more of it.

-k

 

[vbSun]

I would luv to read more on this synapsevampire ..

its 1.45 AM here, lemme catch some sleep b4 i go to another shift tomorrow morning..

Pls give me the link to the FAQ. I will read it in the morning, before i go taming the CrystalReports.. lol

Thanks for the help.. i know its value is more than just a star..

thanks Ngolem.. your post helped too..

Sunil

 

[Ngolem]

SV - I keep forgetting that trick of yours to pass it down to the server...your last formula should do it

Jim Broadbent

 

[vbSun]

hello all,

I succesfully implemented the mechanism in the report. Thanks for the expert help.

But when i called the report from VB, i used this syntax..

cr.ParameterFields(0) = &quot;CustID;-1;True&quot;
cr.ParameterFields(1) = &quot;TransID;-1;True&quot;
cr.ParameterFields(2) = &quot;TransDate;01-01-1900;True&quot;

But its throwing me an &quot;Invalid parameter value&quot; error..

Is this not the format it should go?

Pls help..

Thanks