#7860 - 11/03/06 09:21 AM
Gridwiew Date Format
|
Adagio Ace
Registered: 03/29/99
Posts: 216
Loc: Ottawa
|
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.
|
Top
|
|
|
|
#7861 - 11/03/06 09:58 AM
Re: Gridwiew Date Format
[Re: bal]
|
Adagio Master
Registered: 03/16/99
Posts: 10499
Loc: Canada
|
Hi Bal,
Use
{TransDate}<=QUERYDATE("Enter starting date",DATE(2005,01,01))
_________________________
Andrew Bates
|
Top
|
|
|
|
#7862 - 11/03/06 10:53 AM
Re: Gridwiew Date Format
[Re: Retired_Guy]
|
Adagio Ace
Registered: 03/29/99
Posts: 216
Loc: Ottawa
|
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?
Edited by bal (11/03/06 11:00 AM)
|
Top
|
|
|
|
#7863 - 11/03/06 11:39 AM
Re: Gridwiew Date Format
[Re: bal]
|
Adagio Action Team
Registered: 03/09/99
Posts: 11380
Loc: Vancouver, BC Canada
|
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.
_________________________
Regards, Softrak Tech Support
|
Top
|
|
|
|
#7864 - 11/03/06 11:45 AM
Re: Gridwiew Date Format
[Re: Softrak Support]
|
Adagio Ace
Registered: 03/29/99
Posts: 216
Loc: Ottawa
|
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
Edited by bal (11/03/06 11:46 AM)
|
Top
|
|
|
|
#7866 - 11/03/06 12:34 PM
Re: Gridwiew Date Format
[Re: bal]
|
Adagio Ace
Registered: 03/29/99
Posts: 216
Loc: Ottawa
|
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.
|
Top
|
|
|
|
#7869 - 11/03/06 02:48 PM
Re: Gridwiew Date Format
[Re: bal]
|
Adagio Master
Registered: 03/16/99
Posts: 10499
Loc: Canada
|
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:
AND(
{TransDate}<=QUERYDATE("Enter starting date",DATE(2005,01,01)),
OR(
Trim({Docket})="70-97",
Trim({Docket})="30-01"
)
)
to check both the date and docket range.
_________________________
Andrew Bates
|
Top
|
|
|
|
#7870 - 11/03/06 03:24 PM
Re: Gridwiew Date Format
[Re: Retired_Guy]
|
Adagio Ace
Registered: 03/29/99
Posts: 216
Loc: Ottawa
|
I copied and pasted the code into a new filter.
The report only shows 70-97 transactions.
|
Top
|
|
|
|
#7871 - 11/03/06 03:40 PM
Re: Gridwiew Date Format
[Re: bal]
|
Adagio Ace
Registered: 03/29/99
Posts: 216
Loc: Ottawa
|
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} =" " )
|
Top
|
|
|
|
#7873 - 11/03/06 04:48 PM
Re: Gridwiew Date Format
[Re: bal]
|
Adagio Master
Registered: 03/16/99
Posts: 10499
Loc: Canada
|
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.
_________________________
Andrew Bates
|
Top
|
|
|
|
|
0 registered (),
81
Guests and
1
Spider online. |
Key:
Admin,
Global Mod,
Mod
|
|
1850 Members
5 Forums
14296 Topics
69957 Posts
Max Online: 359 @ 11/18/19 09:08 PM
|
|
1
|
2
|
3
|
4
|
5
|
6
|
7
|
8
|
9
|
10
|
11
|
12
|
13
|
14
|
15
|
16
|
17
|
18
|
19
|
20
|
21
|
22
|
23
|
24
|
25
|
26
|
27
|
28
|
29
|
30
|
|
|
|
|
|
|
|