SQL search using Year() function

[jalbright1957]

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.

Me.RecordSource = "SELECT tblJobs.*, tblIsland.* FROM tblJobs INNER JOIN tblIsland ON tblJobs.JobID = tblIsland.JobID WHERE Year([BidDate])=BidYear ORDER BY BidDate ASC;"

[PhilOfWalton]

Try

WHERE CInt(Year(BidDa te)) = 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

[jalbright1957]

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....

WHERE Year([BidDate])=Forms![frmBidYearInput].[txtBidYear] ORDER BY BidDate ASC;"

[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 :

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.

[jalbright1957]

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.

[NeoPa]

Hi Jal.

You're very welcome :-)

-Ade.