VB Syntax Question

[#1]

Hi Everybody,

I have a quick question on VB syntax. I have a short macro that selects the current year in the date selector field. The syntax looks like this:

ActiveDocument.Fields(

"Calendar Year"). Select YEAR(now())

Instead of using the now() function, I want to use a variable that stores the date of the last refresh. What would be the syntax to use a variable? I can't figure it out!

Thank you!!

[Oleg_Troyansky · Accepted solution]

you need to use another API GetContent to retreive the value out of the QlikView variable. Here is an example:

set v = ActiveDocument.Variables("Variable1")
msgbox(v.GetContent.String)

In your case, if you'd like to write it all in one line:

ActiveDocument.Fields("Calendar Year"). Select ActiveDocument.Variables("vLastReload").GetContent.String

Just be aware of the Date/Number formatting - what are you expecting to get out of the variable - the number or the formatted string? In order to ensure that you are getting the number, use function num() when you assign the variable in your script.

regards,

Oleg

[rwunderlich]

An interesting variation is to use the extended search syntax with Select:

ActiveDocument.Fields("Calendar Year").Select " =[Calendar Year] = '$(vLastReload)' "

Same caveat applies re Date/Number formatting. But you can debug by testing the search expression in a listbox.

-Rob

[#4]

You guys are both awesome, thank you so much for the help.

[#5]

Hello vwalker, Oleg Troyansky, Rob Wunderlich,

I have a similar problem. I have one field ReloadDate which carries only the date when the report was reloaded. I need to compare ReloadDate with current date in a macro written using VBScript.. so how can I do that? 

I tried with

set dte = ActiveDocument.Fields("ReloadDate").GetPossibleValues

if  (dte = Date) then

------------------

end if

But the type mismatch error occures. Also tried with

if  (dte.item(0) = Date) then

------------------

end if

But again "Wrong number of arguments or invalid property assignment: 'dte.item'" error occures.

How to fix the issue??

Please reply

[rwunderlich]

GetPossibleValues returns an array of FieldValue which is going to take some extra processing. Instead how about:

If ActiveDocument.Evaluate("only(ReloadDate) = today(1)") then

-Rob

[#7]

No Rob that also did not work..

But i have done one thing...

I used this condition instead, in a variable

if(MakeDate(Year(ReloadDate), Month(ReloadDate), Day(ReloadDate)) =

MakeDate(Year(Today()), Month(Today()), Day(Today())), 1, 0)

This way I achieved what I needed to.

[#8]

Thanks Oleg - I have been struggling with this issue for a while - it worked like a charm!