asked on

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

peter57r

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}"

Jlucht

ASKER

I did try that. I used Single Quotes, Double Quotes, ## signs, I am confused.

Jlucht

ASKER

I did figure it out I had one ` vs '

however, the values don't update my report.

fslattery

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.

Jlucht

ASKER

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.

ASKER CERTIFIED SOLUTION

Mike McCracken

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jlucht

ASKER

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

Mike McCracken

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

SOLUTION

James0628

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Jlucht

ASKER

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.

James0628

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