Year as a Variable

[Meltdown]

Hi everyone, I have a form with 2 calendar controls, Start and End date. I also have a combo with the months Jan - Dec. When the use selects a month I shove the dates into the calendar controls like this:

If they select March;

Me!startdate = #3/1/2003#
Me!enddate = #3/31/2003#

...and so on.

However I don't want the year hard-coded, I want the year part of the date to come from another combo box that is holding the years 2000 - 2010.

How do I re-write the lines of code above to make the year a variable, coming from the year combo?.

Thanks for any help

 

[a.sinatra]

Me!startdate = "#3/1/" & Me.cboYear & "#"
Me!enddate = "#3/31/" & Me.cboYear & "#"

Me.cboYear would be a combo box that would have a drop down of all the years.
________
SUZUKI GRAND VITARA

 

[Kraj]

You can also use the DateSerial function. I used it to set 'StartDate' and 'EndDate' on one of my forms to always default to '1/1/current year' & '12/31/current year'. You can find many useful combinations in this thread (including variable intervals, last days of the month, etc.:

http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=60281&highlight=date+default

Here is what I used:

=DateSerial(Year(Date()),1,1) --> Specifies Year to come from the Date() function, but Month = 1 and day = 1.

=DateSerial(Year(Date()),12,31) --> Specifies Year to come from the Date() function, but Month = 12 and day = 31.

 

[dcx693]

The ending date for the month won't always be the 31st (or the 30th for that matter). One trick to use it to add a month to your starting date, using the DateAdd function, then subtract 1 day from that.

 

[Meltdown]

Thanks for the replies. I still can't make the year a variable. The calendar control keeps returning a "Invalid Property Value" error when I try all of the following

Me!dtpStart.Value = "#3/1/" & Me!year & "#"
Me!dtpEnd.Value = "#3/31/" & Me!year & "#"

...or

Me!dtpStart.Value = "#3/1/" & Format(Me!year, "yyyy") & "#"
Me!dtpEnd.Value = "#3/31/" & Format(Me!year, "yyyy") & "#"

Is there anything else I can try?

 

[dcx693]

Try these instead:
Me.dtpStart = "#3/1/" & Year(Date()) & "#"
Me.dtpEnd = "#3/31/" & Year(Date()) & "#"

 

[Meltdown]

Hi dcx693, that doesn't work either, I got a Type MisMatch error from the calendar control.

Thanks for trying.

 

[dcx693]

Try:
Me.dtpStart = DateSerial(Year(Date()),3,1)

 

[Meltdown]

No. That doesn't work either, another Type Mismatch. I can't even get a date into a textbox control unless the date is hard-coded like:

Me.textbox = #31/3/2004#...which I don't want

Thanks again

 

[Mile-O]

Me.dtpStart = CDate("3/1/" & Year(Date())
Me.dtpEnd = CDate("3/31/" & Year(Date())

 

[Meltdown]

No joy Mile, your code returns a Syntax Error

Thanks for trying

 

[Mile-O]

They need extra brackets on the end - I got confused because the brackets are removed in the VB Editor so was wondering why they were included:

Me.dtpStart = CDate("3/1/" & Year(Date))
Me.dtpEnd = CDate("3/31/" & Year(Date))

 

[Meltdown]

Sorry Mile, it's still a Type MisMatch, have you managed to shove a date into a control(s) using that code.

Thanks for your help

 

[Mile-O]

Yes. Are you missing a library of any description? Open a module and go to Tools -> References and see if anything gives itself away as being MISSING.

 

[Meltdown]

Hi, no, I'm not missing any references. I've attached a A2K database, when I click the command button I get a Type Mismatch error. Can you please download and tell me if you get the same.

Thanks for your help

 

[Mile-O]

Hi, no, I'm not missing any references. I've attached a A2K database, when I click the command button I get a Type Mismatch error.

Calling the textbox year hasn't helped as it's a reserved word in Access and the function is being bypassed in favour of the textbox.

So, you have to disambiguate.

Me.dtpStart = CDate("3/1/" & VBA.Year(Date))
Me.dtpEnd = CDate("3/31/" & VBA.Year(Date))

 

[Meltdown]

It's working now, thanks a lot Mile-O-Phile for your help, it never occured to me that having the combo called 'year' was going to cause such problems.

Regards
Meltdown

 

[Mile-O]

Get to know your Reserved Words - and remember, everytime you add a reference in Access all of the Keywords that come with that reference become reserved words too.

 

[Meltdown]

Thanks for the useful link

 

[Kraj]

Just to toss in my 2 cents...

There's a handy li'l reference at this link:

http://www.mvps.org/access/general/gen0012.htm

It is a list of commonly used naming conventions, which applies to this post because simply by adding a prefix like 'txt' to any text box controls (ie., 'txtYear') you'll never use a reserved word.