Via Code Create a List of Years

[Cleis]

I would Like to create a Func that will Take todays date =Year(Date)) and add one and subtract 1. I would then take this func and place it within a Combo box as the control source. The end result would be:

2003
2002
2001

Any Help would be appriciated!


Thanks!

Rich

 

[PaulBricker]

You don't need a function to do this. Put this in the Form Open Event


Dim myStr As String
myStr = Year(Date) + 1 & ";" & Year(Date) & ";" & Year(Date) - 1
Me.Combo0.RowSourceType = "Value List"
Me.Combo0.RowSource = myStr

Paul

 

[raskew]

Function yearloop() As String
'creates a string to be used as combo-box's value list
Dim datehold As Date, strSQL As String
Dim n As Integer

For n = -1 To 1
   datehold = DateSerial(year(Date) + n, 1, 1)
   strSQL = strSQL & Format(datehold, "yyyy") & "; "
Next n

yearloop = strSQL

End Function

 

[Cleis]

Weird???

Why do both of these work only if you delete the Date() Func. In other words:

If you change Year(Date) to Year() it works whereas if you try to run Year(date()) you get a type mismatch??

Thanks Again!!!!!


Rich

 

[PaulBricker]

Hard to say. I tested mine in a couple different events before I posted it. I'm sure Bob did also. Year() by itself shouldn't return anything. When I run
?Year()
in the debug window I get an error "Argument not optional". It's a function that needs an argument, in our case the Date() function, which doesn't need an argument.
What version of Access are you using?
What else are you doing at the same time or in some code.

Paul

 

[raskew]

Ditto what Paul said.

The year() function returns an integer, and to do so it must have an argument.

Try this from the debug window:

'testing Paul's solution
myStr = Year(Date) + 1 & ";" & Year(Date) & ";" & Year(Date) - 1
? myStr
2003;2002;2001

'testing my solution
? yearloop
2001; 2002; 2003;

'testing the dateserial() function
'using both date() and now()

? dateserial(year(date()),1,1)
1/1/02
? dateserial(year(now()),1,1)
1/1/02

' testing the year() function
? year(date())
2002
? year(now())
2002

Couple of thoughts:

(1) Are you able to make dateserial() work under any circumstances?
...likewise
(2) Are you able to make year() work under any circumstances?

If the answer to either question is No, I'd suspect
a reference problem.

Bob

 

[Cleis]

Well it gets better!

If I go you the immediate window and type ?Year(date), I get 2002. If I type ?Year(date)+1 I get 2003. This is great. However, If I copy and paste either I your code snippets and try to run the form I'm getting a Type mismatch????

If I call either code samples from the immediate window, I get nothing; no value?

?myStr
" "no return???????/


Why would this be??????


Very confused and frustrated!!!


Rich

 

[Cleis]

Further:

I created a new db and now it works? If I then import the test form with the code into my project in now works?? Yet the code does not work on the original form??? I checked my ref and everything is as it should be??? I should also mention that I stripped everything off the old form and it still doesnt work????

Not sure their is an answer to this one??? Can this be summed up as an anomoly???

Thanks for your help!!!!!

Rich

 

[PaulBricker]

It's not the first time importing into a new database has cured a problem. Hard to say what the issue was, but it's good to know you got it working.

Paul