[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 221
  • Last Modified:

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
0
Frank Freese
Asked:
Frank Freese
  • 6
  • 5
  • 4
  • +2
4 Solutions
 
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 MVP, Access and Data Platform)Commented:
So, there is only one possible choice ?

mx
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
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 MVP, Access and Data Platform)Commented:
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 MVP, Access and Data Platform)Commented:
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
 
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 MVP, Access and Data Platform)Commented:
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

Featured Post

Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

  • 6
  • 5
  • 4
  • +2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now