Displaying records of a certain period 1

[tsjd]

I need help on how to set 2 parameters to view a report from a certain period to another period. For e.g - Displaying a record from 01/01/06 - 31/01/06

 

[synapsevampire]

Create a date parameter of type range, the use the Report->records selection->record and place:

{table.date} = {?MyDateParameter}

Or if you don't want to use a parameter, hardcode it:

{table.date} >= cdate(2006,1,1)
and
{table.date} <= cdate(2006,1,31)

And you should at least post your software version, solutions vary.

-k

 

[tsjd]

I'm using crystal 8.5 and used the following arguements in my record selection. I get an error message for the second portion of the year and period saying that a string is required here

{ARATR.TRANSTYPE}={?Transtype}
//condition for document type to bring up

and {ARATR.FISCYR}>=NumericText(Year({?StartingPeriod})) and {ARATR.FISCYR}<=NumericText(Year({?EndingPeriod}))
and {ARATR.FISCPER}>=NumericText(Month({?StartingPeriod})) and {ARATR.FISCPER}<=NumericText(Month({?EndingPeriod}))
//condition for period

both tables FISCYR and FISCPER are of string types.

 

[synapsevampire]

I think that Numerictext returns true/false, it's used to test whether it's a valid numeric, not converting.

Try:

and {ARATR.FISCYR}>=totext(Year({?StartingPeriod}),0,"") and {ARATR.FISCYR}<=totext(Year({?EndingPeriod}),0,"")
and {ARATR.FISCPER}>=totext(Month({?StartingPeriod}),0,"") and {ARATR.FISCPER}<=totext(Month({?EndingPeriod}),0,"")

This assumes that your parameters are numerics.

If they're strings, you won't need to do anything.

-k

 

[tsjd]

Tried the solution outlined above, but it seems to be displaying a blank report instead >< The tables FISCYR and FISCPER are both string fields with lengths of [4] and [2] respectively. Due to the fact that they are strings, I also tried

and {ARATR.FISCYR}>=Year({?StartingPeriod}) and {ARATR.FISCYR}<=Year({?EndingPeriod})
and {ARATR.FISCPER}>=Month({?StartingPeriod}) and {ARATR.FISCPER}<=Month({?EndingPeriod})

 

[synapsevampire]

OK, so post some examples of what's in the fields, and what's in the Database->Show SQL Query.

If they are strings, then the last thing you tried should have produced an error unless you changed the parameter types.

Another solution is to create 2 SQL Expressions that convert the string fields to numerics and use numeric parameters.

But you need to post your database type, the software being used is important to include in posts.

-k

 

[tsjd]

Works now. Tried using a numeric instead of date. So i'll have to enter two seperate parameters to ask for the month/year. Less flexible, but gets the job done Thanks man