Add query criteria to report.

[mafhobb]

Hi

I have a report that gets all of its data from a query. The query has the following criteria in one field: >Date()-[Time Period in Days?]" which means that the user is prompted for a value for the query to be generated.

How do I capture that value so it can be included in the report?

Say " Your searched time period was: Time Period in Days?" in a label or text field on the report?

Thanks

Mafhobb

 

[John Big Booty]

Given that the report is based on the query that is requesting the criteria, you will be unable to pick up that criteria from the report itself, you will need to pick up the criteria from, say the form that is calling the report.

 

[mafhobb]

You mean I won't be able to pick up the criteria from the query to add it to the report created by the same query, given that the query is the one asking for the criteria, right?

I already have a few fields going from the form to the report. It is just this last piece of criteria that I can't get as it is requested directly from the query, not coming from a field on the form.

mafhobb

 

[John Big Booty]

Ah sorry my bad, I thought you wanted to pick up the criteria from the Report. However if you are wanting to show the criteria on the report, it should be just a matter of putting that field (the one with the criteria in it) on the report.

 

[mafhobb]

Yes, I already have a few fields on the report that way.

The problem comes when the criteria needs to be entered in a query created in Design view so that the query brings up a dialog box for a value when triggered. This value is not on a form, it is only part of the criteria for the query.

I have tried to figure out how to have this criteria be part of the form, but I have had no luck.

mafhobb

 

[JohnLee]

Hi,

I assume that the criteria is in the criteria row beneath the field that contains your data.

In your report design view, and I assume you want the criteria value to appear just once, move the field to the Report Header section of your report and place it where you want.

Now whenever the report is run the user will be prompted for the criteria the report will be generated with the criteria appearing only once in the Report Header Section.

Hope this helps.

John

 

[mafhobb]

Hi. Thank you for your help.

I think I need to explain myself a bit better. Sorry for the confusion, but here is a more detailed explanation.

-The image "input Form" shows the form I use. By entering data in field 1 and fields 3, I fill the listbox with values using an SQL statement. This SQL statement is based on a query that is used on several forms.

-When I click on "print", another query is opened, shown in image "query". This query gets part of its criteria from box 1 in the form, but because I did not know how to "grab" the text "One Month" (One Week, Two weeks,...) and turn it into a number value to use in the "SubcallDate" field in the query, I resorted to simply having the query ask for the value "Time Period in Days" in a dialog box. The user the re-enters a time period in days that the query uses to return results.

-These results are used to fill out the report. Everything is working well, except that I would like to find a way for that "Time Period In Days" value entered when running the query to show on the report. At this point, because of the way it all works, I cannot rely on the value in box 3 in the input form as it may not be necessarily the same as the number entered by the user in the query.

mafhobb

 

[JohnLee]

Hi,

Is your report based on that query? If it is, then is the field "SubCalDate" included in your report, if it is, then my previous post should work.

John

PS

Having looked at the report again, I assume that the "you Searched For: #Name?" is where you want the criteria to appear. Whatever field holds that position should be replaced with the "SubCalDate" [provided it is showing in your report design field list.

 

[mafhobb]

You a right! It works!

Thanks a bunch!!!

Mafhobb