Date range parameter query

[timbits35]

Hi,

I have a query that is hard coded with a date range.

SELECT tblCase.CaseId, tblCase.ReqReceived, tblCase.Letter_AMPI,
FROM tblCase
WHERE (((tblCase.Letter_AMPI) Between #4/1/2014# And #3/31/2015#)) OR (((tblCase.ReqReceived) Between #4/1/2014# And #3/31/2015#))
ORDER BY tblCase.CaseId;

I would like to create a parameter query that allows me to only enter the year and the query would append the rest of the date range. So for example, if I prompt the user to enter the date and they enter 2014, the query would know that it means Between #4/1/2014# And #3/31/2015# or if I enter 2015, it means Between #4/1/2015# And #3/31/2016#.

As well the date would need to go into both fields ReqReceived and Letter_AMPI.

Thank you

 

[llkhoutx]

You can build the query SQL string using ADO or DAO code, prompt the user for the year, and build the string programmatically and store in the query name.

This will get you started.

 

[timbits35]

Thank you very much for your answer. However, this goes above and beyond my capabilities and understanding. If someone can provide a simpler answer as to how to prompt for a year eg. 2014 and then convert this year to a date range of between 04/01/2014 and 03/31/2015 or if the person enters 2015 then between 04/01/2015 and 03/31/2016.

Thank you.

 

[Galaxiom]

If you use a parameter, prompting twice will be unavoidable so better put the year into a textbox on a form.

WHERE [datefield] Between DateSerial(Forms!formname.textboxname,4,1) And DateSerial(Forms!formname.textboxname + 1,3,31)