Today's year from previous or future years

Experts,
My table tblYear is as follows:
YearID (PK)
Year - Number

I need to make sure that people can only select this year from my combo box YearID.Column(1) regardless of other years in the table
Frank FreeseAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

mbizupCommented:
In the Current event of your form, dynamically set the rowsource like this:

Me.cboCombo.Rowsource = "SELECT YearID, [Year] FROM tblYear WHERE [Year] = " Year(Now)
0
mbizupCommented:
There was a typo in that.  Corrected:
Me.cboCombo.Rowsource = "SELECT YearID, [Year] FROM tblYear WHERE [Year] = "  & Year(Now)
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
So, there is only one possible choice ?

mx
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

mbizupCommented:
Alternatively, if you don't want to change the list:

Private Sub cboCombo_BeforeUpdate(Cancel as Integer)
      if cboCombo.column(1) <> Year(now) then
               msgbox "Make another selection"
               me.cboCombo.undo
               cancel = true
      end if
End sub

Open in new window

Change the combo name and/or columns as needed
0
Frank FreeseAuthor Commented:
Year(now) gives me too much information. I just need the year from Now()
0
Frank FreeseAuthor Commented:
mx: Yes, the only choice the user has is to select this year - not a year in the future.
0
mbizupCommented:
Not sure what you mean.

Year(now ) gives you the current year.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
As  in the other Q, generally a combo or list box is for multiple choices.  Couldn't you just have a text box that is locked and defaults to the current Year ?

mx
0
mbizupCommented:
IE: for today,

Year (now ) gives you 2011
0
Rey Obrero (Capricorn1)Commented:


why not just use a default value (current year) for your combo box, you can set it in the load event of the form.

post back if you are interested in doing this.
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
fh_freese:
Assuming this is a required field ... AND ... there is only one possible choice (right?), then why not just have a Locked text box (so user can see this) ... and make it Default Value the Current Year.  This was ... *no* action is required on the User's part ... no validation is required.

mx
0
Nick67Commented:
<Year(now) gives me too much information. I just need the year from Now() >
That's why you have it as the criteria in the combobox.
You aren't returning the value of the combobox anyway  

YearID.Column(1)

so if your combobox isn't showing what you'd like, what's in the table?

Here's a sample
year.mdb
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Frank FreeseAuthor Commented:
mx: Not exactly.
An individuals bonus begins on the month and year they started. Therefore, in the employee file for this db I capture the month and year of employment as separate fields. By selecting the month and year against the people in a store I need to make sure that only those that meet the time crriteria qualify for a bonus. What I am trying to avoid is someone selecting a month that is not previous month from the month they are working in and a year that is only the current year..  
0
DatabaseMX (Joe Anderson - Microsoft Access MVP)Database Architect / Systems AnalystCommented:
Sooo ... why are the users making this selection at all then?  Seems it would make more sense just to have a monthly report that is run ... showing who is up for a bonus that month ..?

mx
0
mbizupCommented:
Have you tried either of the suggestions I posted?  These both limit selections to the current year,  and can be applied conditionally if needed.
0
Nick67Commented:
<capture the month and year of employment as separate fields>
Maybe.
Or maybe you just have duplicate fields.
If both of those fields are Date/Time, they may very well contain the same data.
Access Date/Times ALWAYS store a full Day, month, year and time value.

Format changes what you SEE, not what you store.

If you had just stored a HireDate, you could have had a nice simple criteria like
HireDate < DateSerial(Year(Date())-1,Month(Date()),Day(Date()))
If it was for folks hired at least a year ago

HireDate < DateSerial(Year(Date()),Month(Date()) - 3 ,Day(Date()))
or 3 months ago

HireDate < DateSerial(Year(Date()),Month(Date()),Day(Date()) - 30 )
or 30 days ago.

Now you're hacking around with years and months--and you didn't need to be.
0
Frank FreeseAuthor Commented:
mx: the purpose for the db is to generate the amount of bonus due. There are several criteria that takes place and everything is done and caluculated by hand.
mbizup: the possible solution you've offered aligns closely to what nick67 has offered up. Too many meetings today but have not forgotten you.
capricorn: I've thought about that. Haven't figured out how to tie it to my table that saves the information I'll use to calculate bonuses.
Nick67: none of my fields are date/time. Either text for Month or number for Year.
Folks,
These are fantastic comments and suggestions and I see the answer is here in this thread. I am very grateful for Experts Exchange. I'll close the question and hopefully, assign points fairly (wish I had 1,000 to give).
0
Frank FreeseAuthor Commented:
thanks everyone
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.