Help with Date coding

[StuJol]

I have a frm with 2 text boxes, startdate and enddate. The user enters the
dates, for example 01 April 2005 in the startdate field, and 31 March 2006 in
the end date filed. Exactly 1 year between them. When the system date comes
round to the end date + 1 day (01 April 2006) i want the year part of the
code to change to the next year, for example 01 April 2006 and 31 March 2007.

I've tried as many ways as i can think of but i havent been successful.
Please can someone help. Would it be better haveing 4 text boxes, 2 new ones
for the user to add dates to, for example 01 April and 31 March and leave the
startdate and enddate for the VBA code to write do.

These dates are used many times to filter several qrys.

I could just use a msgbox when the enddate = system date to inform user to
manually change the year but i sure they must be a way for code to do it

Thanks to anyone who looks into this

 

[KARL DEWEY]

Sounds like you are working with Fiscal years.

SELECT DateSerial(DatePart("yyyy",DateAdd("m",-3,Date())),4,1) AS StartDate,
DateSerial(DatePart("yyyy",DateAdd("m",+9,Date())),3,31) AS EndDate

This will roll over with the new Fiscal year.

 

[StuJol]

Thanks for the reply, looks like this code is for the qry, rather than vba.

Can you please explain to me how the code works

 

[KARL DEWEY]

It is SQL for a query but you can use it as Control Source of your TextBox.

=DateSerial(DatePart("yyyy",DateAdd("m",-3,Date())),4,1) for your StartDate

=DateSerial(DatePart("yyyy",DateAdd("m",+9,Date())),3,31) for your EndDate

 

[StuJol]

I assume the ,4,1 and ,3,31 are the results of the example dates i gave you,
can these numbers come from seperate text boxes, the fiscal year isnt going
to be the same for everyone who uses the database.

 

[KARL DEWEY]

I assume the ,4,1 and ,3,31 are the results of the example dates i gave you,
Yes.

can these numbers come from seperate text boxes, the fiscal year isnt going

to be the same for everyone who uses the database.

You could have one set for the company accounting fiscal year and another
pair for a contract fiscal year - example - Our company had accounting year
as April to March but had a contract that required reporting/billing October-
September.

The ("m",-3 and ("m",+9 are the months offset from the calendar year.