Enter Date param in crystal command

[tsquare1975]

Dear All,
I am trying to get user input for date (getting prompt to enter date) and have following satement with left outer join in my query
(
and cast(o.expdate as varchar) = '{?enterdate}'
)
but when i use single qoutes in '{?enterdate}' error found (faild to convert text data type to numeric) and without single qoutes i get syntax error. at last i use above said statement to conver expdate (which is numeric/decimal) into varchar .... but still i can't get any prompt. Plz guide how can i get my desired result
Note: enterdate is created param in crystal and is numeric. In Crystal 10

Thanks

 

[ettienne]

Your enterdate parameter should be a string, and do not enclose it in quotes.
Look at some of the stock reports to see how they deal with dates.

 

[tsquare1975]

if i don't enlcose in qoutes it get syntex error. on the other hand string date param mismatches wity table's date field which is numeric/decimal

 

[ettienne]

Yes.... but I've done 100s of reports, how many have you done?

 

[tsquare1975]

Dear sir its very much true that you've done 100s rather 1000s and i've done hardly 10s but plllzzz help me to getrid of this error i.e without qoutes it gives syntax error thouth its of type string... following is my complete query

SELECT TOP (100) PERCENT o.expdate, M.SEGMENT2,
isnull(SUM(O.QTYSHIPPED),0) AS QTY, isnull(SUM(O.UNITPRICE * O.QTYSHIPPED),0) AS AMT,
M.OPTFLD5,csopt.data as seg2des
FROM icsegv, csopt, dbo.ICITEM AS m left outer JOIN
dbEINVD AS o ON m.FMTITEMNO = o.ITEM
and o.expdate = {?enterdate}
where ((segval=m.segment3 and icsegv.segment=3))
and (csopt.code =optfld5 and

= 'dept')
GROUP BY M.SEGMENT2, M.OPTFLD5,icsegv.[desc], csopt.data,o.expdate

ERROR: "faild to open a row set.
Details: 4200:[Microsoft][ODBC SQL Server Driver]syntax error or access violation

If i place date like '20120619' rather than data param query is OK and gives desired results.

thanks ... waiting for help!!!!

 

[DjangMan]

You've pretty much laid out all of the issues and the solutions.

What data type is the {?enterdate} parameter? And you've already indicated you know the datatype of o.expdate.

When you create a formula as follows on the report - what does the output look like? It will be text - but how is the field value formatted?
cast(o.expdate as varchar)

Are you comparing strings to dates, formatted dates to number, apples to oranges?

 

[tsquare1975]

{?enterdate} was numaric when i used through crystal report select expert, and expdate is also numaric. But plz tell me why this statement gives me syntax error ... wats problem with this

and o.expdate = {?enterdate}

wats problem with this {?enterdate}

 

[DjangMan]

Perhaps move that line from your FROM clause to your WHERE clause.

 

[tsquare1975]

it gives error with every field in where and from as well. I tried it with different ways with syntax error with {?pram}........... Note: I m working in CR 10