Expression do not keep date format

[exASHacto]

Hi all,

I have a HUGE query (about 5 A4 pages). This query is the source for a Excel Pivot table.

The Pivot tables ONLY contain row labels --> no values.

Most of the fields in the query are expressions like: iif(isnull([FIELD];"";[FIELD]) otherwise I'll recieve cells containing "(Blank)" in my Pivot table which confuses the users. This works fine except for the dates. The dates are formatted to text! In the Pivot table the users can't filter properly on the dates (e.g. sort by oldest to newest etc.). ATM the dates are sorted by the day no matter month and year.

I have tried setting the fields Format (Property Sheet) to Short Date and Long Date. And I tried changing the expressions to iif(isnull([FIELD];"";Format([FIELD];"Long Date")). I have also tried with "dd-mm-yyyy".

What to do??

When I run the query and try one of the filters I get the following error:
"Syntax error (missing operator) in query expression 'name'.". There are no errors in the Pivot table.


I really hope you can help me on this one. I would prefer if it could be done in the expression builder.

thanks
Anders

 

[Isskint]

Dates always seem to cause problems. One 'trick' i was advised to use when copying, calculating, referencing etc dates is the DATEVALUE() function. It will coerce any valid date string into a GeneralDate format. So try iif(isnull([FIELD]),"",Datevalue([FIELD]))

Incidentally, it may just be in this post, but you are missing brackets and comma's. Iif(isnull([FIELD];"";[FIELD]) should read Iif(isnull([FIELD]),"",[FIELD])

 

[Pat Hartman]

When you use an IIf() that evaluates a date ans has as one of the return values a non-date datatype, the result will ALWAYS be a string. If you have data that has missing dates, either leave them as null or replace them with some default valid date value.

 

[exASHacto]

My solution was to remove the "(Blank)" in the pivottable, so I didn't have to use the iif(isnull...

I removed the "(Blank)" by overwriting the text with a space in the pivottable.

thanks for your time

Isskint >> My separator is a ; not a , <-- I guess it has something to do with the regional settings.