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?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
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
OWASP Proactive Controls

Learn the most important control and control categories that every architect and developer should include in their projects.

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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Crystal Reports

From novice to tech pro — start learning today.