Need to filter an access query to only display values between a certain patameter (1 Viewer)

Finance · Oct 16, 2018

I have a Year field in the Told_Cost_LargeEntity query.
I want to allow users to enter in the year criteria in a form and only values between that criteria will be visible in the query.
Year 1 and Year 2 are two unbound textboxes in an unbound table.

Years is a field in the query with a Number datatype.

Eg:
If user types in
year 1 = 2018
Year 2 = 2020

the query should only display records between 2018 and 2020.

Please help

pbaldy · Oct 16, 2018

In the criteria on the year field:

Between Forms!FormName.FirstTextbox And Forms!FormName.SecondTextbox

replacing the red bits as appropriate

Finance · Oct 16, 2018

I tried doing that but the whole query is just going blank.

Finance · Oct 16, 2018

I thought the Between and And operators cannot be used for fields that have a 'Number' Datatype? is that the reason why the query is going blank?

June7 · Oct 16, 2018

BETWEEN and AND perfectly suited for number datatype.

Post your full query statement. Provide sample data.

Finance · Oct 16, 2018

The query is Total_Costs_largeEntity and the form is strategy form

June7 · Oct 16, 2018

That query does not have any BETWEEN AND parameter. So I guessed and changed query to add:

(CostSheet.Years_For_Strategy) Between [Forms]![Strategy_Form].[FYorCurrentYearStrategy] And [Forms]![Strategy_Form]![TxtTargetYearStrategy]

I enter year values into the two textboxes and it works.

Finance · Oct 16, 2018

Can you send me a copy of the database with the query ? Do you put this expression

PHP:

(CostSheet.Years_For_Strategy) Between [Forms]![Strategy_Form].[FYorCurrentYearStrategy] And [Forms]![Strategy_Form]![TxtTargetYearStrategy]

in the criteria field for years of strategy in the TotalCostslarge EntityQuery ?

Finance · Oct 16, 2018

it worked! thanks June!!!!!!

Finance · Oct 16, 2018

Just one question, how do I make the sheet stay unfiltered by year if there are no inputs in the form ?

isladogs · Oct 16, 2018

Use an If...Else..End If clause.
The If part checks if both date fields are not null and if so, filters the output.
The Else part covers the case where one or both is null.

Finance · Oct 16, 2018

isladogs said:
Use an If...Else..End If clause.
The If part checks if both date fields are not null and if so, filters the output.
The Else part covers the case where one or both is null.

should I put this code in the OnLoad event of the form?

Can this code be written in the form of an expression instead of VBA?

pbaldy · Oct 16, 2018

You can wrap each form reference in the Nz() function, using appropriate year values in each.

isladogs · Oct 16, 2018

As you are on the form Strategy_Form you can simplify using the Me. operator

Code:

 (CostSheet.Years_For_Strategy) Between Me.FYorCurrentYearStrategyAnd Me.TxtTargetYearStrategy

The [] are superfluous here

Here's one possible way of doing it
Place a button on the form and use this as part of the button click code:

Code:

Dim intCurrYear As Integer, intTgtYear As Integer, strFilter As String

intCurrYear = Nz(Me.FYorCurrentYearStrategy,0)
intTgtYear = Nz(MeTxtTargetYearStrategy,0)  

If intCurrYear<>0 And intTgtYear<>0 Then
    strFilter = "CostSheet.Years_For_Strategy Between intCurrYear And intTgtYear
Else
     strFilter = "*"
End If

Now add a function

Code:

Private Function GetFilter()
     GetFilter=strFilter
End Function

Finally in your query, replace the current Between .... And criteria with GetFilter()

However, personally I would scrap the query & do it all in VBA

Finance · Oct 16, 2018

isladogs said:
As you are on the form Strategy_Form you can simplify using the Me. operator

Code:

(CostSheet.Years_For_Strategy) Between Me.FYorCurrentYearStrategyAnd Me.TxtTargetYearStrategy

The [] are superfluous here

Here's one possible way of doing it
Place a button on the form and use this as part of the button click code:

Code:

Dim intCurrYear As Integer, intTgtYear As Integer, strFilter As String intCurrYear = Nz(Me.FYorCurrentYearStrategy,0) intTgtYear = Nz(MeTxtTargetYearStrategy,0) If intCurrYear<>0 And intTgtYear<>0 Then strFilter = "CostSheet.Years_For_Strategy Between intCurrYear And intTgtYear Else strFilter = "*" End If

Now add a function

Code:

Private Function GetFilter() GetFilter=strFilter End Function

Finally in your query, replace the current Between .... And criteria with GetFilter()

However, personally I would scrap the query & do it all in VBA

Thanks for this, Ill try it out!
the query is a master query that feeds into a report. I want the user to be able to filter the report based on the selection of years.
I figured altering the query would be a lesser hassle that creating a parameter filter on the report itself.

pbaldy · Oct 16, 2018

Finance said:
I figured altering the query would be a lesser hassle that creating a parameter filter on the report itself.

It would be.

Finance · Oct 16, 2018

Finance said:
Its saying that GetFilter is an undefined function.

Where did you put the function code? You could try changing it to a public function and place it in a module

Finance · Oct 16, 2018

Finance said:
Where did you put the function code? You could try changing it to a public function and place it in a module

I created a new public module and put the function code in it and I put GetFilter() in the criteria of years_Strategy in the total_largeEntitycosts query.
but its sayings it an undefined function.

isladogs · Oct 16, 2018

Finance said:
I created a new public module and put the function code in it and I put GetFilter() in the criteria of years_Strategy in the total_largeEntitycosts query.
but its sayings it an undefined function.

There's no such thing as a public module.
Use a standard module - not a class module.
Make it a Public function
Also change strFilter definition from Dim to Public and place in the module.
This will make the function reuseable.

Need to filter an access query to only display values between a certain patameter (1 Viewer)

Registered User.

Wino Moderator

Registered User.

Registered User.

AWF VIP

Registered User.

Attachments

AWF VIP

Registered User.

Registered User.

Registered User.

MVP / VIP

Registered User.

Wino Moderator

MVP / VIP

Registered User.

Wino Moderator

Registered User.

Registered User.

MVP / VIP

Similar threads

Users who are viewing this thread