RecordSelectionFormula

hi , i am newbie in crstal report,
I had face a problem when i try to set the formula for selection.

I hope to query record that inv_date and c_date bigger than certain date.
so i set the formula like below:

r.RecordSelectionFormula =
"{ViewReportTraking.inv_date} >= DateValue('" & datefrom.ToString() & "') or {ViewReportTraking.c_date} >= DateValue('" & datefrom.ToString() & "')"

What happen is the query only select the record inv_date >= certainDate,
but not the c_date >=certainDate.

when i try to set it to opposite
r.RecordSelectionFormula =
"{ViewReportTraking.c_date} >= DateValue('" & datefrom.ToString() & "') or {ViewReportTraking.inv_date} >= DateValue('" & datefrom.ToString() & "')"

I need query either inv_date or c_date >= certainDate.Are you wanting to retrieve records back where both the inv_Date and c_date are >= your "certain date"? If so change your "or" to "and", then you will get back records where both conditions are true.

GJ|||No, i need either inv_date > certainDate or c_date > certainDate is true then query record.
but the 'or' look like not function, but the 'and' is function.

Record Selection Formula. Impossible?

I have a problem selecting records in my crystal report. There are two string parameters, dateFrom and dateTo. The record selection formula is:

{payroll_history.dateModified} in CDate({?dateFrom}) to CDate({?dateTo})

When typing in the string parameters in crystal, the report works fine, suggesting the fault is in the parsing from vb to crystal. See code below:

Report.Parameters.getItemByName("dateFrom").setCurrentValue(strDateFrom)
Report.Parameters.getItemByName("dateFrom").setCurrentValue(strDateFrom)

HOWEVER, I can display these parameters on the screen perfectly fine when executing the report from VB. Surely this means the selection formula is incorrect?? But it works from within crystal! Argh!

Any help anyone can give me will be so much appreciated.My first thought is that you have a DateTime field, but are trying to use a string parameter. Is payroll_history.dateModified a DateTime field in the database? If so, you need to pass it a DateTime. Also, if it is DateTime and not just Date, you will need to add the hour which, if you don't use a time, will have to be 00:00:00, so the parameter passed would have to be something like 08/12/2006 00:00:00 or else it won't work.

Record selection formula with Is Null

Hi all ,
Can any one correct my Record selection formula.
if IsNull ({?Start}) and IsNull ({?End})
then
{Query1.Completion Date} in CurrentDate-3 to CurrentDate-1
else
{Query1.Completion Date} in {?Start} to {?End}What if only one of the dates is set?

( IsNull({?Start}) and IsNull({?End})
and {Query1.Completion Date} in CurrentDate-3 to CurrentDate-1
)
or
( Not(IsNull({?Start}) and IsNull({?End}))
and {Query1.Completion Date} in {?Start} to {?End}
)

It's sometimes easier (if only to read) to create a formula for each parameter and use the formula in the record selection.
e.g. formula @.Start
if IsNull({?Start}) then CurrentDate-3 else {?Start}

e.g. record selection
{Query1.Completion Date} in {@.Start} to {@.End}

You might want to check how/whether this affects what's sent to the database (e.g. is the where clause set?)|||Hi Jagan,
Thanks for reply,
I have a report which will be schedule on every Monday at 1 am.
In this report they want Prev Friday to sunday data.So i am using date range
"{Query1.Completion Date} in CurrentDate-3 to CurrentDate-1"
this is the formula I have in my select expert.But some times user want to give his own parameters.So i want create a parameter that by default it has to bring data for prev Fri to Sun and if user select any date range then it has to bring data for that particular range dates..
I know this one we can create using stored procedures is there any other way with out stored procedure.

Record Selection Formula Help

I have the following data for example

GROUP SECTION
Invoice Number [Aug 8, 2007]

DETAILS SECTION
ItemNo | Description | Latest PurchaseDate
SAMPLEA | DESCRIPTIONA | Aug 7, 2007
SAMPLEA | DESCRIPTIONA | Jul 1, 2007
SAMPLEA | DESCRIPTIONA | Jun 5, 2007
SAMPLEB | DESCRIPTIONB | Jun 6, 2007
SAMPLEB | DESCRIPTIONB | May 5, 2007

Is there a way i can only select in the detail section the maximum date of the latest purchase where the latest purchase date should be <= Invoice Number Date


Thanks for the help.I am not sure what you want to do.

One way to show only the latest detail is: 1
1. sort the details section on the appropriate field so that the latest detail appears last.
2. Move all the fields in the detail section down to the group footer, maintaining their location across the page.
3. If you have some sort of totaling in the group footer, create a group footer B and move the summary fields there.
4. Hide or suppress the details section.

Record selection Formula

Hi all

my report is having a selection formula like this

BooleanVar CRef;
CRef :=({@.Is_CrossRef}=False) and ({AccVchHead.VchType}='Inv' or {AccVchHead.VchType}='Purch');
{AccVchHead.Vch_ID} > 0 AND
(CRef and {AccVchDet_1.Party_ID}={AccVchDet.Party_ID} Or
(Not CRef And {AccVchDet_1.Party_ID}<>{AccVchDet.Party_ID} And
(({AccVchDet_1.Amount}>0 and {AccVchDet.Amount}<0) or ({AccVchDet_1.Amount}<0 and {AccVchDet.Amount}>0))))

And i need to pass selection formula value from vb also

crystalreport.selectionformula=strsel (one selection formula)
crystalreport.action=1

This procedure gives error message saying BooleanVar Cref; Fieldname not found

Expecting positive replies

TonyBooleanVar CRef;
CRef :=({@.Is_CrossRef}=False) and ({AccVchHead.VchType}='Inv' or {AccVchHead.VchType}='Purch');
{AccVchHead.Vch_ID} > 0 AND
(CRef and {AccVchDet_1.Party_ID}={AccVchDet.Party_ID} Or
(Not CRef And {AccVchDet_1.Party_ID}<>{AccVchDet.Party_ID} And
(({AccVchDet_1.Amount}>0 and {AccVchDet.Amount}<0) or ({AccVchDet_1.Amount}<0 and {AccVchDet.Amount}>0))))
I assume this is the formula that is in the Crystal Reports formula and not the one from VB, correct?

crystalreport.selectionformula=strsel (one selection formula)
crystalreport.action=1

What is this --> strsel (one selection formula) ?

Try this:

crystalreport.selectionformula = "BooleanVar CRef; " & vbCrLf _
& "CRef :=({@.Is_CrossRef}=False) " _
& "and ({AccVchHead.VchType}='Inv' " _
& "or {AccVchHead.VchType}='Purch'); " & vbCrLf _
& "{AccVchHead.Vch_ID} > 0 " _
& "AND (CRef and {AccVchDet_1.Party_ID}={AccVchDet.Party_ID} " _
& "Or (Not CRef And {AccVchDet_1.Party_ID}<>{AccVchDet.Party_ID} " _
& "And (({AccVchDet_1.Amount}>0 and {AccVchDet.Amount}<0) " _
& "or ({AccVchDet_1.Amount}<0 and {AccVchDet.Amount}>0)))) "|||Hi Malll

Thanx for replying my query is as u specified it is a recordselection formula in crystal report but i need to pass extra one through vb that is why i specified strsel
which is a variable thru' which i'm going to pass another formula

abstract:

i already have one selection formula in crystal report and need to pass one more through vb. normally it is working but if u r specifying a variable in the record selection formula in crystal report it gives error messages

Tony