Evaluating a date variable in a Macro

gsbeaton — Sat, 13 Mar 2010 12:32:46 GMT

Hi Folks, I've been fighting with this one for a while and I'm struggling.

Firstly, I have a master calendar table with joins to various fact tables that are working well and as expected. The calendar dates are stored as DD/MM/YYYY and they are as dates (rather than string representations of dates)

Secondly, I have 2 calendar objects, to allow users to select a start date and end date. These dates are stored as variables in QV's native date form eg (39836 for 23 Jan 2009)

Now I am trying to use a macro to make a selection in my calendar table whenever either of the calendar objects changes:

[CODE]

SUB UpdateDates

DIM theStartDate
theStartDate = ActiveDocument.Variables("dateStart").GetContent().string
'theStartDate = FormatDateTime(Now(),2) 'This wont even work!

ActiveDocument.GetField("[Calendar Date]").Select
ActiveDocument.Evaluate(theStartDate)

END SUB

[/CODE]

Obviously in this example, there is a missmatch between the two dates I am evaluating (39836 does not match 23/01/2009) but I have tried this code even with a date. The only thing that seems to work is if I do

 ActiveDocument.Evaluate(today())

I hope someone can help.

Many thanks

George

Evaluating a date variable in a Macro

Oleg_Troyansky — Sat, 13 Mar 2010 21:35:37 GMT

George,

A couple of comments:

1. If you are on ver. 9.00, you can apply selection on a field using Actions, in a much simpler way.

2. If you have to use macros, - I suppose there is a way to format date in VBScript, only I don't know it, so I can suggest a QlikView solution:

- Define another variable that holds a formatted dateStart:

SET v_FormattedDateStart = '=date(dateStart, 'DD/MM/YYYY')';

Then, in your macro, simply get the text and use it for Selection:

<pre>
SUB UpdateDates theStartDate = ActiveDocument.Variables("v_FormattedDateStart").GetContent().string
 ActiveDocument.GetField("[Calendar Date]").Select theStartDate
END SUB

topic Evaluating a date variable in a Macro in QlikView

Evaluating a date variable in a Macro

Evaluating a date variable in a Macro