Where clause

[#1]

Hi All,

I've encountered a problem using Last YTD flags in a 4-4-5 calendar. Alignment of dates needs to be based on the day in period for accurate comparsions. I've resolved myself to replace the current calendar with a calendar built in the Business system because I can't seem to find a way to include the day in period in my old calendar creation.

Plan B calls for creating a daily calendar on the business system exploding from/to dates and establishing the day within the period for each date. In the load script I'm setting up the YEAR||Period||day in period to form a test in order to set the FlagLastYTD aligned with the current day in period for this year.

Everything would fall in place if the calendar load in the script would be limited to the last invoice date.

My problem is the placement of the where clause in the attached example.

When I change the script to

[..\Documents\Data\Date context.xls]

(biff, embedded labels, table is [Gened Table$])WHERE([Calendar Date] <= '$(vMaxDate)');

I get:

Error in expression:

')' expected

In the Calendar load

Any help would be appreciated.

Thanks,

Rich DeRocco

[#2]

Don't know if this matters but this should be all you need

WHERE [Calendar Date]<='$(vMaxDate)';

Not even sure you need the string markers there but just try it both ways.  You don't need the parenthesis after WHERE but I'm not sure why it matters.  Also put WHERE on the next line... it's touching the other ')'.  Not sure if that matters either.. but give it a shot.

My guess is that the problem is that the end parenthesis in the "table is [whatever])" and the WHERE clause don't have a space between them.

[Oleg_Troyansky]

Rich,

it has to do with the date comparizons. Your variable vMaxDate may contain either the formatted date string or the numeric part, but not both (variables don't get the priviledge of being dual). It depends on how you assigned it.

Try playing with the condition:

- without quotes, using formatted string?

- without quotes, using numeric values?

I think Excel should work well with the numeric values, since it's using the same calculation formula for dates...

cheers!

[#4]

Thanks to both Trent and Oleg,

Trent for the syntax pointer and Oleg for the comparison values.

It is working now.

Rich