Crystal Reports SQL Command and Parameter not working correctly

I am not sure whats going on but I can't seem to properly insert a Crystal Reports Parameter into my SQL Command.  I am using the command below and its not working.  

If I change it back to @date1 and @date2 and hard code the dates it works just fine.  

Any Ideas?
select Distinct partnum, defect = 'Holes' , defecttotal = Sum(Hole)
From SCRAP
Where Dt between {?Date1} and {?Date2}
Group By Scrap.PartNum

Open in new window

LVL 3
JluchtAsked:
Who is Participating?
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.

peter57rCommented:
I suspect you need to add the correct date datatype delimiters that apply to your database.
For example....
between #{?Date1}# and #{?Date2}#
or
between "{?Date1}" and "{?Date2}"
0
JluchtAuthor Commented:
I did try that.  I used Single Quotes, Double Quotes, ## signs, I am confused.
0
JluchtAuthor Commented:
I did figure it out I had one ` vs '

however, the values don't update my report.
0
Amazon Web Services

Are you thinking about creating an Amazon Web Services account for your business? Not sure where to start? In this course you’ll get an overview of the history of AWS and take a tour of their user interface.

fslatteryCommented:
When you say it's not working, what's the error?  One issue may be that unless your end date is a datetime value with the time set to 11:59:59 pm, you're going to be missing out on that day's data...  I'm getting the gut feeling that it's the value of the dates passed into your query that's causing the issue since the formatting characters didn't produce the required result.
0
JluchtAuthor Commented:
I thought the same thing, so I went and changed the table to 'Date' so the data that is in the field is ''mm/dd/yyyy"

Here is the data flow... so you have the full picture

Data is entered into MS Access 2007 and a DTS package picks up the data and moves it to SQL Server 2000.  I then wrote a query (Above-With the help of others here) to manipulate the data.  I then put the query directly into crystal.  With the dates hard coded "Select @date1 = '5/1/2009', @date2 = '5/30/2009' the query works as it should.  if I take that out and add the parameters, I receive no errors and returns a blank screen.
0
mlmccCommented:
How are you adding the parameters?

It should be done in the ADD COMMAND screen

mlmcc
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
JluchtAuthor Commented:
I will have to try that.. I added them there and they didn't save.  I will take a crack at it and will let you know
0
mlmccCommented:
I haven't used command much and haven't used a parameter with one so I am not sure what the issue would be if they don't save.

mlmcc
0
James0628Commented:
I don't normally use commands in CR, but I just tried it and had no problem using datetime parameters.  I created two parameters in the "add command" screen, as mlmcc said, and then just double-clicked on each one to add it to my command (I assume that I could have just typed the parameter names into the command, but, in case it matters, I actually double-clicked them to add them).  "Show SQL Query" does not show any delimiters around the parameters.  It just shows

where date_field between {?d1} and {?d2}

 The command appears to be working fine (I get data for the selected date range on the report).  I'm not sure what you meant by "they didn't save", but when I saved that report and re-opened it, the parameters were still there and the command still worked.


 > I thought the same thing, so I went and changed the table to
 > 'Date' so the data that is in the field is ''mm/dd/yyyy"

 If you're talking about changing the data type in a table, that wasn't in SQL Server 2000, because it doesn't have a date data type.  So, Dt is presumably still datetime, although if Dt is set from an Access field that's date only, the fact that Dt is datetime may not really matter, since the time would always be 12 AM.

 James
0
JluchtAuthor Commented:
I could not get the COMMAND window to provide the function that I needed.  I used the features in Crystal Reports to manipulate the data.
0
James0628Commented:
Which version of CR are you using?  I'm using CR 10.  If you're using a different version (especially an earlier version), CR commands may work differently.

 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.