ISTech Support Forum

ISTech Support Forum
http://www.istechforum.com/YaBB.pl
Crystal Reports, ODBC & Access >> Crystal Reports General Issues >> Filter Dates in Crystal?
http://www.istechforum.com/YaBB.pl?num=1155591948

Message started by ginawendelin on 08/14/06 at 13:45:48

Title: Filter Dates in Crystal?
Post by ginawendelin on 08/14/06 at 13:45:48

What is the format for filtering out dates in Crystal reports? I've tried "is between 01/01/06 to 06/30/06" or "is between 010106 to 063006" or "is between 01/01/2006 to 06/30/2006" for the BKAR_INV_INVDTE field.

I get the error message: "Failed to retreive data from the database" and "Unknown Database Connector Error" and nothing is displayed. But it connects just fine if I don't specify a filter.

Help!! Does anyone know what I am doing wrong? ???
There is nothing in the help file for Crystal reports.

Thanks!

Title: Re: Filter Dates in Crystal?
Post by JNAPIER on 08/14/06 at 14:44:55

Are you using the select expert or are you formating the field yourself? Try different selections with select expert to see if it is related to a specific field. Even if their is no items in the range, the report is just blank, no errors. Are you using only one file to build your report? sometimes fields in other data files are not linked or primary keyed.

John

Title: Re: Filter Dates in Crystal?
Post by David Waldmann on 08/15/06 at 04:11:47

If using the Select Expert, try using the drop-down box to pick a date - that will give you the correct format which you can then modify to the date you want. If writing a formula try the format "Date (2006, 08,15)".

Title: Re: Filter Dates in Crystal?
Post by ginawendelin on 08/21/06 at 15:29:21

How do you use the select expert? I havent seen anything like that. If I try to filter it by "Is between" or "In the period" then I get a drop down box that gives me predetermined filters such as MonthToDate, YearToDate, Last7Days, Next30Days, Calendar1stQtr, etc., etc... Is this what you mean by select expert? By the way I am using Crystal Reports 11 if that makes a difference. Also when I try to get the invoice subtotal by using BKARINV_INV_SUBTOT I get all 00.00

Basically, in this report all I want is the Invoice Date, Number, and Subtotal for filtered amount of time.

Could either of you Crystal Report Pros help me? Pretty please... :)

Title: Re: Filter Dates in Crystal?
Post by David Waldmann on 08/22/06 at 04:54:01

It sounds like you are using the Select Expert, but that it has changed. I'm on version 8, and the only time I get the option of pre-determined dates is on "In the period". I don't see how you could possibly be between LastFull Week and YearToDate....

But here's what you can do (I think).

Go ahead and pick LastFullWeek. Select OK (use saved data), and then go back to the Select Expert, or whatever it's called in v11. There should be a button that says "Show Formula". You should see something like:

{BKAR_INV_INVDTE} in Date LastFullWeek

Change "LastFullWeek" to "(2006, 01, 01) to Date (2006, 06, 30)", click OK, and Refresh Data.

Title: Re: Filter Dates in Crystal?
Post by Tim Keating on 08/22/06 at 06:48:06

I've found Parameters to work well on the type of report you are creating.

You can read up on them in the Crystal manual.

Each time you run the report, you will be prompted for a Begining Date and Ending Date for the report. At this point you can pull down an actual calendar which makes it quick.

Almost all of our reports use Parameters for Dates, Part Numbers, etc....

We're still on Crystal 7 or I'd share one with you.

Title: Re: Filter Dates in Crystal?
Post by David Waldmann on 08/22/06 at 09:03:56

Tim Keating wrote:

Almost all of our reports use Parameters for Dates, Part Numbers, etc....

Never thought of that. I'll have to remember - that will make the report a lot more user-friendly, and drastically reduce the training requirements.

Is there some way to "force" entry of new parameters?

Title: Re: Filter Dates in Crystal?
Post by Tim Keating on 08/22/06 at 11:34:33

David Waldmann wrote:

Is there some way to "force" entry of new parameters?

Once you define a Parameter and use it in your report (such as using it for selection criteria), the user will be prompted to enter the parameter at run time.

Title: Re: Filter Dates in Crystal?
Post by David Waldmann on 08/23/06 at 03:14:16

In version 8 it only asks you if you want to enter new paramenter values (and the default selection is to use the current ones).

Title: Re: Filter Dates in Crystal?
Post by Tim Keating on 08/23/06 at 07:26:56

David Waldmann wrote:

In version 8 it only asks you if you want to enter new paramenter values (and the default selection is to use the current ones).

O.K. - you're talking about refreshing the report in design mode. That's the same in v7.

I was talking about running the executable report which is all I let the users do.

Title: Re: Filter Dates in Crystal?
Post by David Waldmann on 08/29/06 at 05:33:52

I'm looking forward to the CR Training in NH. I've "learned" CR by trial and lots of error. I knew that there was such a thing as executable reports, but no idea how to make/access/install provisons for such.

Title: Re: Filter Dates in Crystal?
Post by dameng on 09/06/06 at 16:08:03

basically the date selection will work like this:
{BKARHINV.BKAR_INV_INVDTE} in Date (2006, 01, 01) to Date (2006, 08, 31)

you can enter this into the Record Selection or use the Select Expert.

p.s. ver 11 offers these fixed values of "in the Period... 1stQuarter, etc." i don't remember what version that was introduced. i'll look it up.

2nd, Ver 7 has the deployment of reports as executables. the ability of that deployment was removed in 8 and 8.5 but you could use an Add-On which was the same v7 deployment.
needless to say, ver 9 and newer, that option is completely gone.

Title: Re: Filter Dates in Crystal?
Post by David Waldmann on 09/07/06 at 04:25:10

dameng wrote:

Ver 7 has the deployment of reports as executables. the ability of that deployment was removed in 8 and 8.5 but you could use an Add-On which was the same v7 deployment.

Yes, I've now looked into it. I'll need to do some more research, as I get an error when I try to compile the report. Something about "the location of the database must be set ti 'same as in report', please change and try again". Tried to find such an option and couldn't.

Title: Re: Filter Dates in Crystal?
Post by JNAPIER on 09/07/06 at 07:13:25

David Waldmann wrote:

I think it is in DATABASE, SET LOCATION

John

Title: Re: Filter Dates in Crystal?
Post by David Waldmann on 09/08/06 at 03:54:34

Yes it is.

However, on some files, when I try it I get error messages:

Btrieve [12]: File not found

Error opening file
File could not be opened "file", at file location: "file location"

Error detected by database DLL

It seems to be when the reports are using the native Btrieve driver. ODBC ones seem to be already set the same ("Same as in report" is greyed out)?