• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 309
  • Last Modified:

firstweekofyear in access 2010

How do you use the constant firstweekofyear in access 2010 DateDiff function?
I am trying to filter if a date result is in previous year, current year or null
0
simernet
Asked:
simernet
  • 4
  • 3
1 Solution
 
Gustav BrockCIOCommented:
That for week calculations, it won't tell you about the year.

Use Year for that:

intYearOfDate = Year(datYourDate)
If intYearOfDate = Year(Date()) Then ' this year.
If intYearOfDate = Year(Date()) - 1 Then ' last year.
Else ' other year.

/gustav
0
 
NorieVBA ExpertCommented:
Not sure what constant you mean.

Anyway you don't need a constant for this, you could use Date /Year in an expression like this.

Switch(IsNull([DateField]), "Null", Year([DateField]) = Year(Date()), "This Year", Year([DateField]) = Year(Date())-1, "last Year")
0
 
simernetAuthor Commented:
In english I am looking to see if a date occured anytime in the current year or if it falls anytime withing the previous year.  I also want to know if it is null.  
I have the Null part IsNull([disenrollDate])
0
Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

 
NorieVBA ExpertCommented:
The expression I posted does that and returns a string indicating if its null, in the current year or form last year.

What do you want have returned?
0
 
simernetAuthor Commented:
I want this as a filter, so True or False.
I am looking for
True,  IsNull,
True,  This year
True, Last calendar year  ex. (01 JAN 2011  -  31 DEC 2011)
False if any year prior.

Cannot hard code in dates as it will float from year to year.
0
 
NorieVBA ExpertCommented:
What I posted didn't hardcode anything.

Anyway, just use this in the criteria for the filter.


Not IsNull([disenrollDate]) AND (Year([isenrollDate]) = Year(Date()) OR [disenrollDate]= Year(Date())-1

The Year functions returns the year of a date and Date returns the current date.

So today Year(Date()) returns 2012  but on this day next year will return 2013 and so on.
0
 
simernetAuthor Commented:
Year([DisenrollDate])=Year(Date())-1 Or Year([DisenrollDate])=Year(Date()) Or IsNull([DisenrollDate])=True

Thanks,
0
 
NorieVBA ExpertCommented:
Oops, don't know where that AND came from.
0

Featured Post

Never miss a deadline with monday.com

The revolutionary project management tool is here!   Plan visually with a single glance and make sure your projects get done.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now