Help with report criteria based on user entries

[atadbitslow]

I have a report that is to be used to display 1 of 3 periods in 3 consecutive years. There are 3 periods per year, 1,2,3 and the user will choose which period from an option box on a form, and then enter the starting year in a text box. So if a user selects period 2, and starting year 1999, I need data for 1999-2, 2000-2, 2001-2 on the report. (I have a field called app_period in the report's recordsource query that will need the 1999-2, 2000-2, 2001-2 to be in the criteria.)

How can I translate the user's entries correctly into the recordsource query?

Thanks!

 

[randysmid]

Hi,
Are you aware that you can set the "criteria" for all the fields in a query to the form? For instance, your query would have a field called "Period". Underneath it, you can right-click on the "criteria" line and select "Build". You can then set the criteria to a value found on the form. For instance, let's say your form is called "rptPeriodRept' and the input listbox for period is called "lstPeriod". Here is what the corresponding criteria would look like:
[Forms]![rptPeriodRept]![lstPeriod]

By the way, the year ranges could also be set in a list box, e.g. "1999-2001", "2000-2002", etc. Your VBA code then would set 3 non-visible text boxes on the form with the years desired, e.g., "1999", "2000", and "2001". The criteria in the query for the Year field would look like this:
[Forms]![rptPeriodRept]![txtYear1] or
[Forms]![rptPeriodRept]![txtYear2] or
[Forms]![rptPeriodRept]![txtYear3]

Your form would then call the report, and of course, the report recordsource is based on the query in which you just set the different criteria.

HTH,
Randy Smith
California Teachers Association