I am trying to use the QUERYDATE function. I am querying a TB WIP database.

I am using:
{TransDate}<=QUERYDATE("Enter starting date",01-01-05)

A calendar pops up starting 1900. Obviously I don't want to scroll through 105 years to choose my beginning date!

Currently I want to look at transactions from Jan 1, 2005 to Dec 31, 2005 but it would be nice to set something up so that I could look at any range of dates.

Please help.
Thanks.

Hi Bal,

Use

{TransDate}<=QUERYDATE("Enter starting date",DATE(2005,01,01))

Thanks very much.

Now I have another question.

I want to look at a list of dockets.

I need to trim the docket numbers.

I have tried:
Trim({Docket})="70-97",
Trim({Docket})="30-01",

and so on.
Only the first docket is reported.
What do I do?

Hi bal,

If you ever want to have 'either this or that' as a list of dockets, you can use the OR function to separate the statements:

OR(
Trim({Docket})="70-97",
Trim({Docket})="30-01"
)

This can get large if you want a list of 10 specific dockets, so alternately you can use the FINDANY function:

FINDANY( Trim({Docket}),"70-97","30-01",...) = 1

FINDANY returns a value of 1 if the first value equals any of the subsequently listed values, others the value is 0.

I'm really trying to create a view of all time spent during a given period on a specific set of dockets on the same view.

I probably need to use:

AND(
Trim({Docket})="70-97",
Trim({Docket})="30-01"
)

Does that seem right?

Thanks

If I don't use "TRIM", I can pull information on more than one docket but once I introduce the TRIM, only the first docket is reported.

Hi bal,

It's not the TRIM() that's causing the problem - it's the AND(). A single docket number cannot be equal to BOTH "70-97" AND "30-01" at the same time (which is what AND() is asking).

Use:

to check both the date and docket range.

I copied and pasted the code into a new filter.

The report only shows 70-97 transactions.

This is my the filter I am trying to refine. The problem is that the report only shows 70-97.


AND
(

{Trans Date} >= QUERYDATE("Enter start Date",DATE(2005,01,01))
,
{Trans Date} <= QUERYDATE("Enter end Date",DATE(2005,01,01))
,
{Trans Type Desc} = "Time"
,

{Staff} <>" ADJ"
,

OR(
Trim({Docket})="70-97",
Trim({Docket})="30-01"
)

,

{Non-Billable Category} =" "
)

He bal,

Well, the date defaults select only a single day (and New Year's Day at that), so hopefully you are selecting a broader range of dates.

The "OR" is selecting from only two dockets.

In an "AND()" test, the test FAILS (and no record is selected), when any SINGLE test fails. Add all the fields you are testing to your View, turn the filter off, and see all the records - then turn the filter on an determine why a specific row disappears.