Need to filter an access query to only display values between a certain patameter

[Finance]

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]

In the criteria on the year field:

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

replacing the red bits as appropriate

 

[Finance]

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

 

[Finance]

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]

BETWEEN and AND perfectly suited for number datatype.

Post your full query statement. Provide sample data.

 

[Finance]

The query is Total_Costs_largeEntity and the form is strategy form

 

[June7]

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]

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

(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]

it worked! thanks June!!!!!!

 

[Finance]

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

 

[isladogs]

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]

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]

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

 

[isladogs]

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

 (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:

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

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]

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

 (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:

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

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]

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

It would be.

 

[Finance]

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]

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]

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.