I am creating a dynamic Sales Report Generator. This will allow someone to come to the layout and select whether they wish to view 1st Quarter or just January Sales. I want it to default to the current year, unless it is in the quarter(s) or month(s) prior, then it will default to the prior year.

 

My problem is when searching via a date field, you cannot do this:

 

"01/01/year(Get(CurrentDate))...01/31/year(Get(CurrentDate))"

nor

"01/01/year(Get(CurrentDate))-1...03/31/year(Get(CurrentDate))-1"

 

Is this possible?

 

How I am accomplishing this is creating a script that looks at a field and then the OnObjectModify applied script runs whenever I select one of the options from the dropdown. Once it applies the above look ups (that don't currently work) it will sort the records as well.

 

Basically my problem is the look up. Let statement's don't seem to fix the issue. Any thoughts?

Maybe this Custom Function will help you. Link

You're missing a long list of things.

 

First of all, in the narrowest view, your calc should be:

 

"01/01/" & year(Get(CurrentDate)) & "..." & "01/31/" & year(Get(CurrentDate))

 

You don't describe what you're doing with Let statements but they are probably involving the same kind of error as above.

Here's one that works:

 

Let( YYYY = year( get( currentDate)); "01/01/" & YYYY & "..." & "01/31/" & YYYY )

 

There is lots more missing in your description, including what you really mean by "lookups" and how you are writing your script.

 

First of all, in the narrowest view, your calc should be:

 

"01/01/" & year(Get(CurrentDate)) & "..." & "01/31/" & year(Get(CurrentDate))

 

I would suggest a method that does not depend on the locale date setting:

Let ( 
y = Year ( Get ( CurrentDate ) )
;
Date ( 1 ; 1 ; y ) & ".." & Date ( 1 ; 31 ; y )
)

Michael; I completely agree that is a more robust approach. I was just trying to show the concatenation problem and the distinction between strings and calculations.

Brilliant guys, the let worked. I had thought about a Let statement, but didn't try it. Thanks, this works great. Appreciate the help!