Date Parameter into Record Formula Selection

[sommererdbeere]

Hi,
I really really hope someone can help me.. i've been trying to figure this out for a long long time, but no solution found.. here is my problem:

In Record Formula Selection, i've try to pass in 3 parameter, part_no, begin_date, end_date.. however, the date doesn't pass correctly, it mess up the whole report i created... let say, i wanna select the date between 11/1/2003 - 11/10/2003, this date frame is not being selected.. the report shows ALL the date.. here how i wrote in parameter fields and the record formula for the date:

Parameter Fields: Begin_Date --> Value type(date)
--> default value (1/1/9999)
end_Date --> Value type(date)
--> default value (12/30/9999)

Record Formula Selection:


{CLOSE_DATE} >= {?Begin_Date} and
{CLOSE_DATE} <= {?end_Date}


i have tried to set Value Type (string) in the Parameter fields and do cdate({?Begin_Date}), date({?Begin_Date}), datevalue({?Begin_Date}) in Record Formula Selection for both begin and end date..

the date is pass in from oracle SQL database.
i've heard it is very tricky to pass in Date from Oracle Database into Crystal Report..

i hope someone can help w/this problem.. i will appreciate very very very much.. thanks for those who help and answer my question.. million million thanks..

mm

 

[Ngolem]

I know this is a dumb question but .... what is the datatype for {CloseDate}...is it a date, datetime or a string.

the formulas given should work....something fundamental is missing...

Don't right click on the parameter...right-click on the field ,{CloseDate}...

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[sommererdbeere]

sorry, but what do u mean by datatype for {CloseDate}?? i click under : database field... then, next to CloseDate.. it said datetime... is that right??? btw, when i rt. click CloseDate --> Browse Data, it shows all the date?? but what does that do??? thank u

 

[lbass]

It looks like {table.closedate} is a datetime field. Next you should go to the field explorer->parameter->edit and make sure that {?Begin_Date} and {?End_Date} are set up as discrete value date parameters, not strings, and not ranges. Also try eliminating any default dates for these, and then I think they will automatically default to today's date.

Yes, you should enter &quot;All&quot; (without the quotes) as a default for the {?Part_No}, which I'm assuming is set up as a string.

-LB

 

[synapsevampire]

Begin date is your parameter, not the field...

Glad that you figured out the datatype though.

As most have pointed out, the formulas are sound, perhaps you're using the wrong connectivity, the date fields don't have what you think they have, or there are typos involved.

-k

 

[sommererdbeere]

thanks for all ur help.. but very strange.. i still can't have the exact date range i wanna it..

this is my formula:
((UpperCase({table.Part_No}) like &quot;%&quot or
(UpperCase({table.PART_NO}) startswith UpperCase({?Part_No})))
and
{table.closedate} >= {?Begin_Date} and
{table.closedate} <= {?end_Date}


where my i have {?Begin_Date} & {?end_Date} parameter set as &quot;Date&quot; and no default..

i've also, try: (
if {?Part_No} <> &quot;All&quot; then
{table.PART_NO} startswith UpperCase({?Part_No})
else if {?Part_No} = &quot;All&quot; then
true
)
and
{table.CLOSE_DATE} >= {?Begin_Date} and
{table.CLOSE_DATE} <= {?end_Date}

still.. same problem occur...

i know this doesn't make sense.. but it is very strange..

 

[lbass]

You still have the parentheses wrong on the first formula. Try:

(UpperCase({table.Part_No}) like &quot;%&quot; or
UpperCase({table.PART_NO}) startswith UpperCase({?Part_No}))
and
{table.closedate} >= {?Begin_Date} and
{table.closedate} <= {?end_Date}

If you use the second formula, you need to use:

(
if {?Part_No} <> &quot;All&quot; then
uppercase({table.PART_NO}) startswith UpperCase({?Part_No})
else if {?Part_No} = &quot;All&quot; then
true
)
and
{table.CLOSE_DATE} >= {?Begin_Date} and
{table.CLOSE_DATE} <= {?end_Date}

-LB

 

[synapsevampire]

Simplify, jus put in the date part first and get that working.

BTW, Thw OR was causing you grief I think, it should be n OR of the part number, not compared to everything else.

Again, this may even be connectivity related, but you haven't responded to those inquiries.

-k

 

[Ngolem]

((UpperCase({table.Part_No}) like &quot;%&quot or
(UpperCase({table.PART_NO}) startswith UpperCase({?Part_No})))
*******************************

{shaking my head}

This is useless....the first part will bring back every Part Number

Jim Broadbent

The quality of the answer is directly proportional to the quality of the problem statement!

 

[sommererdbeere]

yap.. i can choose/select All the Part_NO on a specific date i input... taht's why i have the first part... anyone has any other clues??? million million thanks.. the reason i'm asking 'coz this report has to be due tomm.. so, many thanks for anyone who help...

 

[synapsevampire]

Sommer: Your'e not paying attention to what anyone has posted, you mix suggestions, write improper code, and then zag off.

Try the record selection formula with ONLY the date criteria.

What you are attempting is very basic, the fact that it isn't working ISN'T the result of bad advice.

-k

 

[sommererdbeere]

thanks for everyone who posted their advice and suggestions on this thread.. thank u.. i finally have it work.. thanks to all of u.. thank u... i'm very happy that it gets work out.. thanks for everything. tahnk u everyone.. ehehehehee