ActiveDocument.Fields($field).Select --- HELP!!!

[#1]

Hi,

I need some help really quick.

I'm using ActiveDocument.Fields("create_date").Select ($DateRange) in macro to pull tickets created with in $DateRange variable. Statement works like a charm! no problems at all. I can see all tickets opened with the the date range.

Right below that line, I've also added "ActiveDocument.Fields("resolve_date").Select($DateRange)" statement to pull tickets closed with in same date range.

Issue# -- I'm only seeing tickets closed with in the date range but not tickets opened in same time period. it basically over writing the first Select condition. I want report to show both selections.

i thought of using toggle select but it seems like it can be used on same field to select more than 1 value at same time.

can somebody help me out on this?

thanks,

kal

[disqr_rm]

For the second line, use ToggleSelect instead:

ActiveDocument.Fields("resolve_date").ToggleSelect.($DateRange)

Should work fine.

[#3]

thanks rakesh for the reply but still it won't work.

QV is ignoring 2nd statement!

[johnw]

Your selections are probably incompatible in the data model. I'm guessing you have a data model something like this:

ID, create_date, resolve_date
1, Jan 1, Jan 15
2, Jan 10, Jan 30
3, Jan 15, Jan 20
4, Jan 25, Jan 30

And I'm guessing you're setting up a date range like Jan 10 - Jan 20. What you want to see are IDs 2 and 3 based on the create_date, and IDs 1 and 3 based on the resolve_date. But the macro works exactly the same as doing the selections manually, which is to say they're AND, not OR, and later selections will override earlier selections if the resulting data is not compatible. So you just end up with the later selection in this case, with the resolve date.

Now, I'm not sure about what other requirements you have, but one way of handling this is by changing your data model:

ID, date_type, date
1, create, Jan 1
1, resolve, Jan 15
2, create, Jan 10
2, resolve, Jan 30
3, create, Jan 15
3, resolve, Jan 20
4, create, Jan 25
4, resolve, Jan 30

Now there's only ONE date field, so your selections are compatible, and will be treated like OR. If you select dates in the range Jan 10 - Jan 20, you'll get this data returned:

ID, date_type, date
1, resolve, Jan 15
2, create, Jan 10
3, create, Jan 15
3, resolve, Jan 20

I believe that's what you're looking for in this case. If that's the kind of data and results you want, but your source data is more like the first table, an easy way to do the transformation is with a crosstable load.

[#5]

thank you so much, john.

you nailed it! that's exactly what i was looking for.

my data is in first table format. could you please help me with crosstable statement?

crosstable ( Normal 0 false false false EN-US X-NONE X-NONE create_date,resolve_date) load blah blah ...?

how does result data set looks like?

[johnw]

The first two parameters of the crosstable are the new field names you want it to create for you, and then it will turn the second and further field names from the load into field values. So it looks like this:

CROSSTABLE (date_type,date)
LOAD
ID
,create_date as create
,resolve_date as resolve
... from whatever your source is ...

Note that if you have additional fields to load other than the ID that you DON'T want to be changed, you'll need to add a third parameter to the crosstable to tell it how many fields to skip. For instance:

CROSSTABLE (date_type,date,4)
LOAD
ID
,ticket_type
,ticket_status
,comments
,create_date as create
,resolve_date as resolve
... from whatever your source is ...

[#7]

It worked!

Thank you, John