SELECT recognizes parameter but not formula CR8.5

[vey99]

Hello there,

I have a report that takes in 2 date_time parameters.
I then convert them to a string, timestamp style.
The conversion formula (@start) is:

totext({?datetimestart},"yyyyMMddHHmmss&quot

i.e 22/01/2003, 08:00:00 --> 20030122080000


In the select expert, I choose
{MyDB.MyField} in {@start} to {@end}

When I check the SQL Query, there is no "WHERE" clause, causing it to select all records.

However, if I create two string parameters ?start and ?end and stick it into the select expert, it works nicely.

The database field is a String type of length 14.

I hope someone can give some ideas on what went wrong.
Thanks in advance.

 

[kenhamady]

Not all CR functions can be converted to SQL, including ToText. Since these have to be evaluated on the client, CR has to return all records to the client and let CR figure out which to include.

I think you are going to have to choose between performance and a date parameter.

Is the database SQL Based? Ken Hamady, On-site Custom Crystal Reports Training & Consulting
Public classes and individual training.
Expert's Guide to Formulas / Guide to Crystal in VB

http://www.kenhamady.com/

- tek@kenhamady.com

 

[vey99]

Thanks for the note on that, I wasn't aware that CR would take the lot in before processing. The database in question is an MS Access 97 file. It houses around 200K rows of data on one table *sigh*

The nearest solution would be to get the users to key in the timestamp.

Cheers