Access Form Control Source coding for Date field

Posted on 2015-01-19
Medium Priority
Last Modified: 2015-01-20
I have a daily down load of data that I do year around. This data does not need to include multiple years. So, every January this data search includes records from the previous year, which I do not need to carry over. I'd like it to only download the current year. However, the problem is that the source website will only allow me to download 1000 records maximum. Currently, there are about 500 records permonth so I just can't leave the control source set to 1-1-Current year.

Currenlty I am using
=DateSerial(Year(Date()),Month(Date()),Day(Date()))-90 (This is always 90 days less than "today's date")

However, I would like to change this.

I need the date to be January 1, Current Year. (1-1-2015) until 90 days of the current year have expired. Then I need it to be 90 days less than the current date. So every year it changes automatically with the calendar and will not include records from the previous year.
Question by:ggodwin
  • 2
  • 2
LVL 41

Accepted Solution

als315 earned 2000 total points
ID: 40558329
You can use this code for your field:
=IIf(CDate("1/1/" & Year(Now()))>DateAdd("d",-90,Now()),CDate("1/1/" & Year(Now())),DateAdd("d",-90,Now()))

Open in new window


Author Comment

ID: 40558612
Where does that code get entered at?
LVL 41

Expert Comment

ID: 40559082
You can enter it to your field on a form, replacing your code:

Author Closing Comment

ID: 40559826
Worked perfectly

Featured Post

Easily Design & Build Your Next Website

Squarespace’s all-in-one platform gives you everything you need to express yourself creatively online, whether it is with a domain, website, or online store. Get started with your free trial today, and when ready, take 10% off your first purchase with offer code 'EXPERTS'.

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

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.
A quick solution showing how to control and open a POS Cash Register Drawer using VBA with MS Access.
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …
Enter Foreign and Special Characters Enter characters you can't find on a keyboard using its ASCII code ... and learn how to make a handy reference for yourself using Excel ~ Use these codes in any Windows application! ... whether it is a Micr…

597 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