Using Forms to Select Report/Query Results and Options

[UberZoot]

Ladies and Gentlemen,
I have been dodging the subject for months, but cannot get around it any longer. I have researched the subject in manuals, but it does not make sense to me. How can I build a form that utilizes combo, list, check, or text boxes to select the criteria for a report? For example, I have a report built fom a query that tracks Date, Name, and Score for test results. I am currently using Between [Start Date] And [End Date] in the criteria section of the Date column in the query to offer the user some control over the report. How can I build my own forms to offer these options and more (such as print preview, further criteria to narrow/customize the selection, etc.)? This issue has been bugging me for a while and I sure do appreciate the help. I'm only a Tech User so I appreciate the patience as well.

Thanks,
UberZoot

 

[glalsop]

You can reference fields on your forms from queries or reports with the following syntax:

[Forms]![YourFormName]![YourFieldName]

So if you create a form with 2 text boxes, txtStartDate and txtEndDate, you could put this in the criteria section for your date field in a query:

Between [Forms]![YourFormName]![txtStartDate] And [Forms]![YourFormName]![txtEndDate]

-Gary

 

[Loktar]

You can get the value of all the controls in your form to create a fairly complicated search string. Say you want to search for scores over 70 from people named Bob.

Here some example values should be able to get from your forms controls:

Me.[Name] = "Bob"
Me.[MinScore] = 70

Then, using some code you could construct a search query such as:

"[Name] Like """ & Me.[Name] & """ And [MinScore] >= " & Me.[MinScore]

I'm not sure about the syntax for the >= part, but you get the idea. This string could then be passed as the stLinkCriteria in an DoCmd.OpenForm command where the new form would only show you the data you want to see.

Let me know if this points you in the right direction, or if you need more help getting that string assembled.

 

[mhuston]

You need a Filter. MS has a couple of KB articles on it, or you can search on MS Access Report Filters.