VB Oracle Crystal Reports date problem

[renjukarsf]

Hi all

rr.SQLQuery = "select * from EXPORT where to_date(PLACEME NTDATE, 'DD/MM/YYYY') < TO_DATE('" & Format("17/08/2007", "YYYY/MM/DD") & "', 'YYYY/MM/DD') "

while executing the above querry im getting runtime error 2053 vb oracle8 and crystal report. Please help me to solve this urgent.


Renjith

[debasisdas]

The sql query is parsed at database level ,remove the format function from the SQL string . Try to use oracle only syntax.

[renjukarsf]

The sql query is parsed at database level ,remove the format function from the SQL string . Try to use oracle only syntax.

I already tried with following sql , but the same error , please help

rr.SQLQuery = "select * from EXPORT where to_date(PLACEME NTDATE, 'YYYY/MM/DD') < TO_DATE('200/17/10', 'YYYY/MM/DD') "

[debasisdas]

Kindly post the exact error message with error number.

[debasisdas]

I already tried with following sql , but the same error , please help

rr.SQLQuery = "select * from EXPORT where to_date(PLACEME NTDATE, 'YYYY/MM/DD') < TO_DATE('200/17/10', 'YYYY/MM/DD') "

The date that you are passing is wrong. How can the month be 17.and the year 200?
If the placement date is already a date field, no need to convert it again.
Try the following syntax.

select * from EXPORT where PLACEMENTDATE < TO_DATE('2007/10/17', 'YYYY/MM/DD')

Hope that solves your problem.

[renjukarsf]

The date that you are passing is wrong.How month can be 17.and year 200.
If the placement data is already a date field no need to convert it again .
Try the following syntax.

select * from EXPORT where PLACEMENTDATE < TO_DATE('2007/10/17', 'YYYY/MM/DD')

Hope that solves your probelm.

Exact error number is :20534. Due to over sight i posted worng querry , the correct one is

rr.SQLQuery = "select * from EXPORT where to_date(PLACEME NTDATE, 'YYYY/MM/DD') < TO_DATE('2007/10/17', 'YYYY/MM/DD') "

[QVeen72]

Hi,

When using CR, Create the Report with all the required fields, and Instead of Changing the SQL Query From VB,
Just use "SelectionFormu la" (before viewing/Showing/Printing the report) to Filter out Records say :

rr.SelectionFormula = "{EXPORT.PlacementDate}< Date( "  _
& Format("17/08/2007", "YYYY,MM,DD") & ") "
 

For Higher Versions of CR it shud be : rr.RecordSelect ionFormula

Regards
Veena

[renjukarsf]

Hi,

When using CR, Create the Report with all the required fields, and Instead of Changing the SQL Query From VB,
Just use "SelectionFormu la" (before viewing/Showing/Printing the report) to Filter out Records say :

rr.SelectionFormula = "{EXPORT.PlacementDate}< Date( "  _
& Format("17/08/2007", "YYYY,MM,DD") & ") "
 

For Higher Versions of CR it shud be : rr.RecordSelect ionFormula

Regards
Veena

Hi veena

Thanks for your help.
My field 'placementdate' is in dd/mm/yyyy format . please lookinto below and reply wht is the error . I want compare date for generating report from oracle. Im yotaly new in Crystalreport , please guide

rr.SelectionFor mula = "Format({EXPORT .PlacementDate} ,'YYYY/MM/DD') < Date( " _
& Format(frmdt, "YYYY/MM/DD") & ") "

rr.SQLQuery = "select * from EXPORT "

[QVeen72]

Hi,

Dont Format, Dont Pass SQLQuery. If report is designed properly(with Tables), you need not pass SQLQuery to report..
Just pass the Selection Formula as I said in my Previous Post. and What is the version of ur CR..?
For CR8 and above it is CR.RecordSelect ionFormula

Regards
Veena

[renjukarsf]

Hi,

Dont Format, Dont Pass SQLQuery. If report is designed properly(with Tables), you need not pass SQLQuery to report..
Just pass the Selection Formula as I said in my Previous Post. and What is the version of ur CR..?
For CR8 and above it is CR.RecordSelect ionFormula

Regards
Veena

Hi

Your previous post gives me wrong report. Im not getting the report according to my comparison

EG: im selecting date lessthan 11/07/20007 it shows all records

Thanks
Renjith