Improve company productivity with a Business Account.Sign Up

x
?
Solved

firstweekofyear in access 2010

Posted on 2012-03-22
8
Medium Priority
?
310 Views
Last Modified: 2012-03-22
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
Comment
Question by:simernet
  • 4
  • 3
8 Comments
 
LVL 53

Expert Comment

by:Gustav Brock
ID: 37753864
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
 
LVL 37

Expert Comment

by:Norie
ID: 37753904
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
 
LVL 1

Author Comment

by:simernet
ID: 37754051
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
Get 10% Off Your First Squarespace Website

Ready to showcase your work, publish content or promote your business online? With Squarespace’s award-winning templates and 24/7 customer service, getting started is simple. Head to Squarespace.com and use offer code ‘EXPERTS’ to get 10% off your first purchase.

 
LVL 37

Expert Comment

by:Norie
ID: 37754063
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
 
LVL 1

Author Comment

by:simernet
ID: 37754166
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
 
LVL 37

Accepted Solution

by:
Norie earned 2000 total points
ID: 37754248
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
 
LVL 1

Author Comment

by:simernet
ID: 37754347
Year([DisenrollDate])=Year(Date())-1 Or Year([DisenrollDate])=Year(Date()) Or IsNull([DisenrollDate])=True

Thanks,
0
 
LVL 37

Expert Comment

by:Norie
ID: 37754435
Oops, don't know where that AND came from.
0

Featured Post

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.

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.

Join & Write a Comment

Implementing simple internal controls in the Microsoft Access application.
In this article, we will show how to detach and attach a database and then show how to repair a corrupt database and attach it, If it has some errors. We will show how to detach and attach using SSMS or using T-SQL sentences.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

608 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question