topic How to select Datetime field values in a macro in QlikView

How to select Datetime field values in a macro

Tue, 12 Oct 2010 05:30:59 GMT

Hi,

I'm writing a macro that selects values for the current date when the document is first opened. For whatever reason, some of the values in the date field are displayed as *only* date, for example 5/7/2010 and others have a datetime representation like 9/10/2010 12:00:00 AM.

In the macro I'm writing the following line works just fine for the 'pure' date values:

ActiveDocument.Fields("Date").Select CDate("2010-07-05")

but for the datetime values the following does not work

ActiveDocument.Fields("Date").Select CDate("2010-10-09 12:00:00 AM")

Why?

I've tried many combinations and possible string representations to no avail

Thanks in advance,
Dave

How to select Datetime field values in a macro

Anonymous — Tue, 12 Oct 2010 07:01:51 GMT

Try skipping the CDate() function and just do .Select "Datevalue"

How to select Datetime field values in a macro

Tue, 12 Oct 2010 11:24:32 GMT

These are all the attempts I've made so far. None of these have worked for selecting Feb 28th, 2006 in the Date1field.

ActiveDocument.Fields("Date").Select "2006-02-28 12:00:00 AM"

ActiveDocument.Fields("Date").Select #2006-02-28 12:00:00 AM#

ActiveDocument.Fields("Date").Select CDate("2006-02-28 12:00:00 AM")

ActiveDocument.Fields("Date").Select ActiveDocument.Evaluate("date('2006-02-28','YYYY-MM-DD')")

ActiveDocument.Fields("Date").Select ActiveDocument.Evaluate("date('2006-02-28 12:00:00 AM','YYYY-MM-DD')")

ActiveDocument.Fields("Date").Select "2006-02-28 *"

How to select Datetime field values in a macro

Tue, 12 Oct 2010 11:36:03 GMT

OK Solved it now. I assumed that date comparisons would happen by evaluating a date value, so I was trying to convert my string into a date to do the selection. This is wrong. Using .Select does a match on the actual characters in the field, so formatting matters *alot*. My solution was to open up document properties, set the formatting of this field to be YYY-MM-DD and then this line started to work:

ActiveDocument.Fields("Date").Select "2006-02-28"

Thanks to Johannes Sundén for your suggestions.