Parameters in Crystal Reports 2008

I just want the end user to be able to select between two (order_date)  dates via a parameter in CR2K8.  


I am not sure why the date field is coming over as a string and I guess this could be the reason why my parameters aren't working.  I can make the parameters, but it still grabs everything in the database.  Any help would be appreciated.

SQL Script
SELECT     order_date, cust_code, cust_po, shipto_code, part_code, qty
FROM         ediord
WHERE     (statflg = 'm') AND (notes3 = 'ITEM MARKED AS PROCESSED') Screen shot of Field Explorer
gpsdhAsked:
Who is Participating?
 
zephyr_hex (Megan)DeveloperCommented:
how is the date field defined in the database (what's its datatype?)

have you tried something like this:
SELECT     convert(datetime,order_date) as order_date, cust_code, cust_po, shipto_code, part_code, qty
FROM         ediord
WHERE     (statflg = 'm') AND (notes3 = 'ITEM MARKED AS PROCESSED')

another option would be to do something like this:
SELECT     order_date, cust_code, cust_po, shipto_code, part_code, qty
FROM         ediord
WHERE     (statflg = 'm') AND (notes3 = 'ITEM MARKED AS PROCESSED') AND convert(datetime,order_date) = {?date_parameter}
0
 
James0628Commented:
Since you seem to be using a CR Command, you should create the parameters in the CR Command editor, not in the Field Explorer (as in your screenshot), and then add the parameters to the WHERE in the Command.  I guess you could create parameters in the Field Explorer and then use them in a record selection formula, but I don't know if those tests would be passed to the server with the query in the Command.

 If CR is seeing order_date as a string, then the column must be a "string" type (eg. char).  You may be able to create the parameter(s) as type date and just add them to the WHERE, and let the db handle the conversion.  MS SQL, for example, would probably handle the conversion for you.  Or you could add some code to your Command to do the conversion, as in zephyr_hex's second query.

 James
0
 
gpsdhAuthor Commented:
Sorry have been out with the flu.  Appreciate your help.  I still get all records instead of what is ordered between the two dates, so my thought it must be date related may be wrong.


Crystal-Reports-2.png
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
GJParkerCommented:
what code do you have in the record selection ? It should be this

{Command.OrderDate} In {?OrderDate}
0
 
GJParkerCommented:
Alternatively as James0628 says you should be adding 2 parameters to the command to ensure they are passed to the database.

SELECT     order_date, cust_code, cust_po, shipto_code, part_code, qty
FROM         ediord
WHERE     (statflg = 'm') AND (notes3 = 'ITEM MARKED AS PROCESSED')  
AND order_date >= {?start_date} AND order_date <= {?end_date}
0
 
gpsdhAuthor Commented:
Thank you all for the help! Zephyrs change allowed me to make it a date field and James/ GJParker gave me the parameter help.
0
 
James0628Commented:
You're welcome.  Glad I could help.

 James
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.