Selecting data for a report based on calendar 1st or 2nd half 1

[vsimpkins]

Hi,
I'm using Crystal Reports XI with an Access database based on employees and hours worked by date. The data pull for the report is based on when the report is run along with the field containing the comparison/qualifying data, the HOURS.DATE field.

For example,
Today is 5/28/09 so, if the report is run today, it should pull all data where the HOURS.DATE falls between Jan - June (in the Calendard1sthalf). If today was 8/28/09, the report should pull data based on the HOURS.DATE falling between July 1 - Dec 31st (in the Calendar2ndhalf.)

I tried using a selection formula with the Calendar1sthalf and Calendar2ndhalf functions but can't get it to work.

Valerie Simpkins

http://www.pacetraining.com

 

[lbass]

Try a selection formula like this:

(
(
currentdate in calendar1sthalf and
{table.date} in calendar1sthalf
) or
(
currentdate in calendar2ndhalf and
{table.date} in calendar2ndhalf
)
)

-LB

 

[vsimpkins]

Hi,
I placed the selection formula as follows:
((currentdate in calendar1sthalf and {Hours.DATE} in calendar1sthalf) or
(currentdate in calendar2ndhalf and {Hours.DATE} in calendar2ndhalf ))
What I'm getting is all the data whether it's in the 1st half or the 2nd half. What it should do is exclude any data not falling within the 1st half of the based on the current date. Any additional ideas are most welcome.


Valerie Simpkins

http://www.pacetraining.com

 

[lbass]

Please show your entire record selection formula as you entered it. There must be an error, since currentdate cannot be in both halves of the year. Maybe you have another 'or' clause that is allowing all records to appear.

-LB

 

[vsimpkins]

That is the entire selection formula.

Valerie Simpkins

http://www.pacetraining.com

 

[lbass]

It can't be the exact copy--are you sure the parens are in place?

-LB

 

[vsimpkins]

The previous post shows the entire selection formula as copied and pasted from the formula editor.

Valerie Simpkins

http://www.pacetraining.com

 

[lbass]

There is something we are missing then, since the currentdate can ONLY be in one half of the year. Please show some samples of what is in your {Hours.Date} field.

-LB

 

[vsimpkins]

Hi,
Thanks for your help. It worked. The one report I put it in had subreports and I should've put the filter in the subreports.

Valerie Simpkins

http://www.pacetraining.com

 

[vsimpkins]

Me again...it didn't work The dates in the Hours table are all 3/5/2009. now that the system date is 6/22/09, none of the records should appear...but they do.

Valerie Simpkins

http://www.pacetraining.com

 

[lbass]

Why is the Hours table called "hours"? Please show samples of the Hours.Date field as requested earlier and identify the datatype. Right click on the field->browse field->datatype is at top.

-LB

 

[vsimpkins]

Hi,
I don't know why it's necessary to know why a table has a specific name. At any rate, I've attached an image of a partial view of the Hours table in Access. The Hours.Date field is a date/time field.

Valerie Simpkins

http://www.pacetraining.com

 

[lbass]

And yet it is displaying as a date, so I guess you have it formatted only as a date.

Why do you think that none of the records should appear now that the date is 6/22/09? Both 6/22/09 and 3/5/2009 or in the first half of the calendar year.

-LB