filtering a calculated control in a report 1

[kevin531]

is it possible to filter a calculted control in a report? what i have is a maintenance forcast. i when the report is run i only want it to produce records within the next week. what i would filter by is the calculated fields. is this possible? any advise would be great. thanks.
-kevin531

 

[pdldavis]

Hi, the way I do this is place two unbound text boxes on a form - call them txtStartDate & txtEndDate.

The query for your report probably has a date field. In the critera section of your date field place:

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

You can right click in the criteria field and use the expression builder to do this as well.

Also, you can put two unbound text boxes in your report header that reference the dates you typed in on the form as your report run dates:

=forms!yourform.txtStartDate
=forms!yourform.txtEndDate

-so you can easily see between what dates the report was run.

Hope that helps

 

[RickSpr]

Create a query that includes the report's current recordset, and add columns that calculate the values you want. Then base the report on that query. You can then filter on the calculated fields just like any other fields.

This assumes that you calculations are simple expressions. If you're doing a more complicated calculation involving logic, you'll have to create a function in a standard module that does the calculation, and call that function from the query. For example, you could add a column to the query with its Field row set to:
CalcValue: MyFunc(Fld1, Fld2, Fld3)
You will then have a field named "CalcValue" which you can bind to a control on the form, and use in a filter. 'MyFunc' must be a function in a standard module that takes 3 arguments and returns the calculated value. Rick Sprague
Want the best answers? See faq181-2886
To write a program from scratch, first create the universe. - Paraphrased from Albert Einstein

 

[kevin531]

pdldavis,
sorry it took me so long to get back to you. the weekend and all.
i've followed your suggestion and it makes sense. however, when i try to enter:

"=Between [Forms]![forcast form]![txtStartDate] And [Forms]![forcast form]![txtEndDate]"

i get an error. it says that invalid syntax has been entered. i tried to do this with the expression builder also and it returned the same result. as far as i know, that is the correct expression. any ideas would be great.

 

[kevin531]

Sorry, i figured it out. i had to remove the equals sign to get it to work. i also had to remove the sumation that was there. but it works now. thanks.

 

[pdldavis]

Whoops, sorry about the = sign. Glad it worked for you. The summation - something of yours? or something I posted above?

 

[kevin531]

something of mine. it was an expression.
-kevin531