By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
426,045 Members | 1,743 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 426,045 IT Pros & Developers. It's quick & easy.

SQL search using Year() function

P: 25
I have a popup form opened as acDialog which prompts the user to enter just the year with which to display all relevant records in a continuous form. The variable BidYear passes the correct value back to the main form where the SQL code is executed but Access throws up another input box to enter a year. If I remove the variable BidYear and instead type in a year such as 2016 in its place, all records whose date field contain a date in 2016 are displayed. I found one article that states to enclose the variable in single quotes but all that does is display 0 records.
Expand|Select|Wrap|Line Numbers
  1. Me.RecordSource = "SELECT tblJobs.*, tblIsland.* FROM tblJobs INNER JOIN tblIsland ON tblJobs.JobID = tblIsland.JobID WHERE Year([BidDate])=BidYear ORDER BY BidDate ASC;"
Apr 5 '17 #1

✓ answered by NeoPa

You may find Literal DateTimes and Their Delimiters (#) helpful.

I would advise filtering by the date value itself if it's indexed for more efficient SQL.

EG :
Expand|Select|Wrap|Line Numbers
  1. WHERE ([DateField] Between #2017-1-1# And #2017-12-31#)
Obviously your code would need to create that string based on wherever you have your year value stored.

Share this Question
Share on Google+
5 Replies


PhilOfWalton
Expert 100+
P: 1,430
Try

WHERE CInt(Year(BidDate)) = CInt(BidYear)

This should ensure they are both integers.
Note that you will have a problem if the operator does not enter a 4 DIGIT number in the input box.

My own preference would be to have a table of relevant years - 2000, 2001, 2002..... 2017, 2018 and a Combo box to select the appropriate year.

Phil
Apr 5 '17 #2

P: 25
Phil, thanks for your reply. I tried that but got the same result of Access popping up with another parameter prompt. I did discover that by changing the Where clause to reflect the form instead of the variable, it worked as expected. I actually did start out with a combo box in the header but the form was too crowded with just that little extra....
Expand|Select|Wrap|Line Numbers
  1. WHERE Year([BidDate])=Forms![frmBidYearInput].[txtBidYear] ORDER BY BidDate ASC;"
Apr 5 '17 #3

NeoPa
Expert Mod 15k+
P: 31,419
You may find Literal DateTimes and Their Delimiters (#) helpful.

I would advise filtering by the date value itself if it's indexed for more efficient SQL.

EG :
Expand|Select|Wrap|Line Numbers
  1. WHERE ([DateField] Between #2017-1-1# And #2017-12-31#)
Obviously your code would need to create that string based on wherever you have your year value stored.
Apr 6 '17 #4

P: 25
I actually started it this way but wasn't 100% sure of how to convert the year that was input into the sql string. I did see several articles about using variables within the sql code but could not get the global variable to pass the value and avoid sending up a second prompt. Interestingly enough that by referring to the form worked first time. Thanks for the article tip, is very helpful.
Apr 6 '17 #5

NeoPa
Expert Mod 15k+
P: 31,419
Hi Jal.

You're very welcome :-)

-Ade.
Apr 6 '17 #6

Post your reply

Sign in to post your reply or Sign up for a free account.