Date filter help

[src2]

I am running a CR 8.5 report and I want to filter my data for records for the previous month. The raw data includes a date as part of a string. I've created a formula building a date using the mid and cdate function. The formula doesn't report any errors and displays correctly on the report like 04/23/03. When I try to use this formula to filter my data to see if the formula is in Lastfullmonth it says bad date format string and brings up the formula I've created. Any suggestions would be helpful and appreciated.

 

[synapsevampire]

Share example data and the formula you've used.

I would do the reverse of your approach, build out 2 formula fields which build the appropriate string (as your database uses), based on the previous month date range.

Then you reference these date string formulas to compare with a substring({table.stringdatefield,X,Y) in your record selection formula.

The advantage is that the server would then do the processing, as opposed to Crystal.

BTW, to optimize, dates should be stored as dates in the table, not as a part of a string field.

If required, provide sample data and someone can flesh out the formulas/record selection for you.

-k

 

[src2]

I didn't understand what you are telling me so here's an example of one of my records, the formula I used for extracting a date and converting it into a date format, and the code I used to filter the data.

Sample record: OR145279BK041203L29....
(the embedded date is 041203)

Formula: {@OrderDate}
cdate(Mid ({orders.detail},11,2) & "/" &
Mid ({orders.detail},13,2) & "/" &
Mid ({orders.detail},15,2))

Filter used: {@OrderDate} in Lastfullmonth

 

[synapsevampire]

Change your record selection formula to:

mid({orders.detail},11,8) >= totext(minimum(lastfullmonth),"ddMMyyyy&quot
and
mid({orders.detail},11,8) <= totext(maximum(lastfullmonth),&quot;ddMMyyyy&quot

This converts the last full month start and end datses to the same data type as in your database.

-k

 

[src2]

Thanks that did the trick. I was wondering how you knew to do that? I guess what I'm asking is how did you know that lastfullmonth was in a different format and how would you find out what format something was in if you didn't know?

Thanks again