Parameter For Month and Year in Access Query 1

[nyamrembo]

Hello,
Please help, I have a table that has several date fields and I am trying to create a parameter that would filter a date by month and year. For example, If I want to find customers who dined with us in December 2008, I want be able to enter 12/2008 in a parameter box and pull those customers only. The other thing is that I am not good at coding, so it there is criteria i can set on the query criteria field would be great.

Thanks Bunches!

Nyamrembo

 

[dhookom]

Please review faq701-6763. I would use a form with controls for beginning and ending dates. You wanted, you could have these populated from a value selected in a combo box with columns for begin and end dates.



Duane
Hook'D on Access
MS Access MVP

 

[nyamrembo]

I don't know if this makes sense because what I am trying to do is to filter part of a whole date by month and year. I am not interested in the day of the month. So, lets say the date in the table is 1/15/2010,what i am interested in is the month of January and the year 2010. I am not interested in the day because I want only customers who visited the restaurant in the month of January 2010 eg, 1/2010 or 2/2009 etc?
Do you think I can create a parameter to enter the month as one parameter then year as another parameter? Hope I have not confused you even more.
Thanks again,

 

[dhookom]

I would create a combo box [cboYrMth] with a row source like:

SELECT DISTINCT Format([YourDateField],"yyyy-mm")
FROM tblYourTable
ORDER BY 1;

Your query might then be something like:

SELECT *
FROM tblYourTable
WHERE Format([YourDateField],"yyyy-mm") = Forms!frmYourForm!cboYrMth;

Duane
Hook'D on Access
MS Access MVP

 

[nyamrembo]

You Guys are Awesome! Your suggestions gave me the options I was looking for. I have decided to just use a parameter instead of the combo because the combo list was way too long. This way, I can just enter the month and year i have in mind and it pulls up the list. It's working perfectly so far from the testing I have been doing.

 

[nyamrembo]

So, now my parameter date filter is working perfectly. But my goal is to get the filtered customer names printed as labels. For that, I have created a tabular form that has check boxes and a print command button. This was working fine before I created the parameter. The problem I am facing now is that I am being prompted to enter the date filter again on the report when I click the command button to preview and print the report. Is there away to go around this and get the report to work without having to filter the date twice on the form and on the report? I just want to filter the list on the form then click the print command button and pull up the report. Too confusing...?

 

[dhookom]

Is there away to go around this and get the report to work without having to filter the date twice on the form and on the report?

Please review FAQ701-6763

Duane
Hook'D on Access
MS Access MVP