• Status: Solved
• Priority: Medium
• Security: Public
• Views: 460

I have a field on a form called [currentperiod] and it holds a number from 1-12 dependant on the month of the year a report is running for. I need to be able to subtract 1 or 2 from this number to be able to produce comaprison monthly reports.

ie:

If I'm running a report for period 10, I need to be able to do periods 8 & 9 also not a problem as I can just subtract one. however when I reach period 1 next year I need to be able to have the reports generated for periods 11 & 12 of the prior year. I have tried dateadd("m",-1,[currentperiod]) but it does not work all the time.

Is there anything I can do?
0
Carl2002
2 Solutions

Commented:

=[currentperiod] - 1
and
=[currentperiod] - 2

use these

=iif([currentperiod]>1;([currentperiod] - 1);([currentperiod] + 11 ))
and
=iif([currentperiod]>2;([currentperiod] - 2);([currentperiod] + 10 ))

--bluelizard
0

Commented:
Is currentperiod a date - or is it a number from 1 to 12?

It doese not look like it is a date,

I would use the logic

MonthsBeforeTime = (([currentperiod] + 12 - monthsPrior - 1) Mod 12) + 1

Where monthsPrior is the number of months.

This will effectively treat the numbers as "wrapping" around the number 12
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.