Using Parameters in SQL Record Select Query

[Barbara265]

I'm using Crystal 6.0 and am trying to use a series of parameters in the SQL query to eliminate hard-coded dates. Here is a snippet of the query that has the dates...
... AND
((EComp.EmDateBeg <= '05/31/00' and EComp.EmDateEnd >= '05/31/00') OR (EComp.EmDateBeg <= '05/31/00' and EComp.EmDateEnd Is Null AND EEmploy.EeDateLastHire <= '05/31/00') OR
(EEmploy.EeDateLastHire > '05/31/00' AND EEmploy.EeDateLastHire = EComp.EmDateBeg)) AND...

I have created a parameter called Month1 that I linked to EComp.EmDateBeg through the Select Expert. When I replace the hard-coded date with
EComp.&quot;EmDateBeg&quot; <= ? (the syntax Crystal creates through use of the Select Expert.) and run the report I get an ODBC error that says &quot;COUNT field incorrect&quot;. Ulitmately what I need to do is replace all of the hard-coded dates with parameters, but I thought I would start with one and get it to work before I tried to change them all.

At this point, I'm not even sure if this will work. I have used parameters in a similar way in the past and have not had this error. Any insight will be appreciated.

 

[Ina]

Using Oracle, I use date parameters often in place of hard coded dates. It has always worked if I input date in the following format for date parameter: Date(2000,05,31). Also, you said you replaced the date with &quot;EmDateBeg&quot; <= ?, you mean &quot;EmDateBeg&quot; <= ?DateParmName Right?

Once, i did not see the parameterized statement come through in the Database &quot;Show SQL query&quot; option and I could not figure out why even though I did countless refresh. So I deleted the old statement, saved the report and recreated the new statement through Report | Edit Selection Formula | Record and that did the trick.

Good Luck.

 

[Barbara265]

I tried replacing my original parameter in the SQL query with this...

((EComp.EmDateBeg <= '{?Month1}' and EComp.EmDateEnd >= '05/31/00') OR (EComp.EmDateBeg <= '{?Month1}' and EComp.EmDateEnd Is Null AND EEmploy.EeDateLastHire <= '05/31/00') OR
(EEmploy.EeDateLastHire > '05/31/00' AND EEmploy.EeDateLastHire = Comp.EmDateBeg)) AND...

The date is input through the parameter in the Date(2000, 05, 31) format. When I run the query I get a new ODBC error... &quot;syntax error converting datetime from character string.&quot; I tried removing the single quotes around the parameter name (thinking it was mistaking the date for a string I used this {?Month1}), and got an error &quot;syntax error or access violation.&quot;

I'm not sure what to try next and am considering leaving the hard-coded dates in.

Thanks for your help though!

 

[Ina]

The database field is in Date format, correct? I assume it works perfectly when all dates are hard coded?

For troubleshooting purpose, where you know the data exists, use only one part of the formula to test your results just in case the error is being generated elsewhere and leave everything else hard coded. e.g.

((EComp.EmDateBeg <= {?Month1} and EComp.EmDateEnd >= '05/31/00') OR
(EComp.EmDateBeg <= '05/31/00' and EComp.EmDateEnd Is Null AND

Just to make sure again, you stated you used Date(2000, 05, 31), does this mean there are spaces after the comma? there should be none.

Also, you should not need any kind of quotes around the parameter. I agree that it must be mistakened for String.

It might be that the syntax error is occuring elsewhere and not in this statement? Try troubleshooting this date issue by itself with isolated data where you might be able to use just this one parameter and you'll know if the syntax error is being generated by this.

 

[PTW]

I had the same problem as you did Barbara. I could NOT get it to work if I directly typed the Parameter name into the Show SQL Query screen. So instead I used the Edit Selection Formula screen to build the Parameters into the WHERE clause. Then I went to the Show SQL Query screen and fixed up the rest of my SELECT statement manually.