Selecting records 5 months prior to a given date. 1

[BrettS]

I would like to select only PO records that have a date 5 months prior (or greater) to a given Post Date. I know I can conditionally suppress the records in the details section using the DateDiff function, but I want to do this in the selection criteria.

Any help would be appreciated.

Thanks in advance,
BrettS

 

[synapsevampire]

Try creating a date parameter for the post date and use:

Report->Edit Selection Criteria->Record:

{table.date} >= dateadd("m",-5,{?MyDateParameter})

-k

 

[BrettS]

Thanks for the tip.

I failed to mention before that the date info is a string field. So is the parameter. I tried this:

{Table.StringDate} <
CStr(CDate(DateAdd("m", -5, CDate({?@PostingDate}))))

But the only records it returns have a date of "0000-00-00"

 

[synapsevampire]

<chuckling>

You failed to mention anything technical, I knew there'd be at least another post here...

If the field is a string containing a date, then the FORMAT of that date string is important to emulate when doing comparisons.

Try:

totext(CDate(DateAdd("m", -5, CDate({?@PostingDate}))),"yyyy-MM-dd")

As a kindness to everyone involved, please post technical information rather than text descriptions:

Crystal version
Database/connectivity used
Example data (show tables/fields/types)
Expecteed output (based on example data)

And why use a string field for the parameter? Date parms are easier to enter, and you can convert it to a string for comparison.

Better still, you might use a SQL Expression to convert the string date field in the table to a date on the database side to simplify things, but that would require knowing the database type and Crystal version.

You're too secretive

-k

 

[lbass]

Try converting your date field to a date, and make the parameter a date type parameter. Something like:

cdate({table.stringdate}) < dateadd("m",-5,{?PostingDate})

This might depend on how your stringdate is formatted. What does it return if you place it on the report canvas?

-LB

 

[synapsevampire]

Good point, LB.

If it's "0000-00-00" then wrapping it in cdate should work.

-k

 

[BrettS]

Thanks for your input k & LB

k,

I apologize for the lack of detail.

I am using v10.

The data source is an ADO object that provides a resultset of data from Lawson Financials. The database is Oracle, but we are required to access the data through Lawson's Query Builder which builds the ADO object (I am new to the use of this adapter and not sure of its conversion capabilities).

I inherited the report and would prefer not to rebuild the ADO object (although that is a possibility).

LB,

I too thought that

cdate({table.stringdate}) < dateadd("m",-5,{?PostingDate})

would work. However, I receive the error:

"Bad date format string."

and the "cdate(({table.stringdate})" portion of the statement is highlighted.

When I drop the field table.stringdate on the canvas it looks like this: 2004-04-07

Thanks Again

 

[synapsevampire]

I'm using Lawson on my current contract.

The OLE DB and Query Builder are mind numbingly slow....

I use Oracle Native connectivity for reports not requiring the Lawson security, one example is a report that takes 10 minutes in Lawson, less than 1 second using Oracle directly.

Odd that the cdate didn't work.

Try:

cdate(val(left(trim({table.field}),4)),val(mid(trim({table.field}),6,2)),val(mid(trim({table.field}),8,2)))

-k

 

[BrettS]

k

That did the trick, I had to change the 8 to a 9 though.
Must have been the dashes.

I understand about the slowness. That's out of my hands unfortunately.

Thanks again very much.

Brett

 

[synapsevampire]

Ooops, yeah, should have been an eight.

I'd be intersted in how you're working with Lawson, and perhaps we can share some efforts if you're using HR.

-k

 

[BrettS]

k

I'd be happy to share whatever I know. I just started here so I am still getting the "lay of the land". Its a big organization that is using a number of approaches. We are using HR I believe. If I don't have the answer for something you're looking for, there are a number of people here I could ask.

You can reach me at
brett.sheets@providenceRemove-This-Part.org

Take Care
Brett