Using Between in VBA

[jmofstead]

Hello all, I am trying to write code that would automatically assign a training year depending on when an employee was hired and for some reason I cannot get it to work.
Here is what I am looking to do:
In our form, we want to automate the training year.
So what I've done is pulled the Hire date into this form and when a person assigns training to a new employee I have selected the On Update and my code is:
If me.Hiredate >#9/30/10 Then me.trainingyear = #1/1/2010#
Elseif me.Hiredate<#10/1/10# Then Me.trainingyear = #1/1/2011#
ElseIf me.hiredate>#9/30/11# Then me.trainingYear = #1/1/2011#
ElseIf me.hiredate>#10/1/11# Then me.trainingyear=#1/1/12#

and so on.
This would pull up the first two dates fine, but if I tested a hire date of 10/1/11 it would give me 1/1/11 and not 1/1/12

So then I tried a between statement, and that didn't even work at all, I kept getting an error needing a sub or function.

Anyhelp would be great. I know that my If statment isn't very good, so if you have advice on how to make it better, I would sure appreciate it.
Thanks!
Julie

 

[rainman89]

you need to do >= 10/1/10 for it to work

Edit that wont work either.

The issue is that you have a bunch of differnt possibilities.

If me.Hiredate >#9/30/10 Then me.trainingyear = #1/1/2010#
Elseif me.Hiredate<#10/1/10# Then Me.trainingyear = #1/1/2011#
ElseIf me.hiredate>#9/30/11# Then me.trainingYear = #1/1/2011#
ElseIf me.hiredate>#10/1/10# Then me.trainingyear=#1/1/12#

and so on.
This would pull up the first two dates fine, but if I tested a hire date of 10/1/11 it would give me 1/1/11 and not 1/1/12

10/1/11 is greater than 9/30/11 and greater than 10/1/10 so it will show for both and since it gets to the 9/30/11 one first it takes that

 

[jmofstead]

Do you have any suggestions on how to make it better? I've always wondered if there was a way to have a set month and day, but the year be like an astrick...not sure if that would work at all...
I tried adding = but it's still not working.
Esentially what I was hoping to do, but it's beyond my limited knowlegde of coding is to say if the hire date is between 1/1 and 9/30 of any year, assign the training date as 1/1 of this current year. If the hire date is between 10/1 and 12/31 assign the training date to be 1/1 of next year.
Is that a possibility?

you need to do >= 10/1/10 for it to work

Edit that wont work either.

The issue is that you have a bunch of differnt possibilities.


10/1/11 is greater than 9/30/11 and greater than 10/1/10 so it will show for both and since it gets to the 9/30/11 one first it takes that

 

[pbaldy]

You don't want to code it like that anyway, or else you have to keep changing the code. You can simply test for the month of the hire date being > 9; if so, add 1 to the current year.

 

[Brianwarnock]

You need to do a little bit more than Paul suggests as you wish the dates to be 1/1/year.
I would look up Dateserial

Brian