2 Replies Latest reply on Mar 6, 2015 7:59 AM by raybaudi

# Calculate Variable

### Title

Calculate Variable

### Post

Hi

I have a field which has the month and year (ex. 02/15)
I need to search for all records from the month before, so I would like to set Field to 01/15. How can I script this?

• ###### 1. Re: Calculate Variable

This is easier to do if you use a date field for your month and year. You can assign an arbitrary day such as 2/1/15 and use data formatting to hide the day value. But then it's simpler to specify the preceding month/year like this:

SetVariable [\$Criteria ; value: Month ( YourTable::DateField ) - 1 & "/" & Year ( YourTable::DateFIeld ) ]

Assuming that you have a text field for this, the code to produce this value is more complex:

SetVariable [\$Criteria ; value: //put expression shown below here ]

Let ( [ vList = Substitute ( YourTable::textField ; "/" ; ¶ ) ;
Mnth = GetValue ( vList ; 1 ) ;
PreMnth = If ( Mnth = 1 ; 12 ; Mnth - 1 ) ;
Yr = GetValue ( vList ; 2 ) ;
PreYr = If ( PreMnth = 1 ; Yr -1 ; Yr )
] ;
PreMnth & "/" & PreYr
) // Let

• ###### 2. Re: Calculate Variable

Another calculation could be:

Let([
T = YourTable::YourTextField ;
M = GetAsNumber ( Left ( T ; 2 ) ) ;
Y = GetAsNumber ( Right ( T ; 2 ) )
];
Case(
M > 1 ; Right ( 0 & M - 1 ; 2 ) & "/" & Right ( 0 & Y ; 2 ) ;
12 & "/" &  Right ( 0 & Y - 1 ; 2 )
)
)