Solved

Posted on 2006-07-03
Medium Priority
455 Views
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
Question by:Carl2002
[X]
###### Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

• Help others & share knowledge
• Earn cash & points

LVL 14

Assisted Solution

bluelizard earned 800 total points
ID: 17029519

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

use these

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

--bluelizard
0

LVL 35

Accepted Solution

Raynard7 earned 1200 total points
ID: 17029528
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

## Featured Post

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

The Windows Phone Theme Colours is a tight, powerful, and well balanced palette. This tiny Access application makes it a snap to select and pick a value. And it doubles as an intro to implementing WithEvents, one of Access' hidden gems.
We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
###### Suggested Courses
Course of the Month10 days, 14 hours left to enroll