Define in VBA on which field to filter (1 Viewer)

droops14

New member
Local time
Today, 00:11
Joined
May 21, 2019
Messages
8
HI all,
a form contains records with 12 variables for each month of the year. Based on a dropdown list filter on the form header, I want to define on which month the user wants to filter. So if the user selects march from the drop down, the records on the form should be filtered on the variable [march] so the variable to filter on is dynamic and I don't know how to define that in vba? Can someone help?
in sql this would be like "select * from [tablename] where [variablename] = a condition" but how to define a variablename that is dynamic based on a dropdown selection....
Many thanks!
Erik
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:11
Joined
Oct 29, 2018
Messages
21,358
Hi Erik. Welcome to the forum. Having 12 fields to represent the months of the year may not sound like a normalized table structure but here's one possible way to do what you're asking.
Code:
strSQL = "SELECT * FROM TableName WHERE " & Me.ControlName & "=" & Me.ConditionBox
Or, if you're simply filtering the same data on the form, then you could try something like:
Code:
Me.Filter = """" & Me.FieldSelection & "=" & Me.SomeCondition & """"
Me.FilterOn = True
Hope it helps...
 

droops14

New member
Local time
Today, 00:11
Joined
May 21, 2019
Messages
8
Thanks theDBGuy for your quick response...

I'm just an amateur in this but learning quick. :) Just to further clarify, the value they select from the dropdownfield on the form is stored in the unbound field called "Select_Month".... the value in this field will contain the name of the variable I want to filter on. So if they selected "March"... the select filter should be "select * from [tablename] where [march] > 0.2"

I'm not sure how this fits into your suggestion of using me.fieldselection and me.somecondition. Could you maybe provide the exact sql based on adddtional specifics above??? I know I'm asking a lot ;-)

Cheers
Erik
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:11
Joined
Oct 29, 2018
Messages
21,358
Hi Erik. So, are you filtering the data on the same form? If so, you can use the second suggestion above. For example:
Code:
Me.Filter = """[" & Me.Select_Month & "]>0.2"""
Me.FilterOn=True
 

droops14

New member
Local time
Today, 00:11
Joined
May 21, 2019
Messages
8
yes it is on the same form. I used the code and it does not give an error but also doesn't filter as expected. The variables are defined as Number fields so that could not be the issue. If I test the filter in a query it perfectly filters on values >0.2..... not sure what could be the problem... I think we are close :)
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:11
Joined
Oct 29, 2018
Messages
21,358
yes it is on the same form. I used the code and it does not give an error but also doesn't filter as expected. The variables are defined as Number fields so that could not be the issue. If I test the filter in a query it perfectly filters on values >0.2..... not sure what could be the problem... I think we are close :)
Hi. Do a Debug.Print Me.Filter and see what you got. Also, can you post the record source of the form? Thanks.
 

droops14

New member
Local time
Today, 00:11
Joined
May 21, 2019
Messages
8
sorry to disappoint you but I don't know how to do that. Is that just putting that debug.print me.filter statement at the end of the code? If so, I did that but nothing happens when I run the code....:banghead:
 

droops14

New member
Local time
Today, 00:11
Joined
May 21, 2019
Messages
8
ah and the record source is a table called "RAT availability"
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:11
Joined
Oct 29, 2018
Messages
21,358
sorry to disappoint you but I don't know how to do that. Is that just putting that debug.print me.filter statement at the end of the code? If so, I did that but nothing happens when I run the code....:banghead:
Okay, to make sure the code is even running, try it this way.
Code:
Me.Filter = """[" & Me.Select_Month & ">0.2"""
Me.FilterOn = True
MsgBox Me.Filter
If you don't see a message box, then the code didn't run.
 

droops14

New member
Local time
Today, 00:11
Joined
May 21, 2019
Messages
8
Hi again,
I got a little step further I think. I ran following


Dim strSQL As String
strSQL = "SELECT * FROM availability_form WHERE " & Me.Select_Availability2 & "" & ">0.2"""
DoCmd.ApplyFilter strSQL

But it gave following error:
Syntax error in string in query expression '[Month 1]>0.2""

It seems it does translate already in the correct variable [Month 1] but the filter expression doesn't work.... it should be something small....
Erik:rolleyes:
 

gemma-the-husky

Super Moderator
Staff member
Local time
Today, 07:11
Joined
Sep 12, 2006
Messages
15,614
bear in mid that "month" is a bit tricky, because you are pulling out part of a date (I imagine)

you also have the year to consider, as there will be an April in every year

I would filter based on a date range

either have a dropdown with the months, or use a date picker, so if a user picked 22/04/2019 (or April 2019), then I would filter for dates between 01/04/2019 and 30/04/2019.

Alternatively, I would consider adding another field to your table to store just the month part of the date, Then you can index the field, and filter for the month number directly

if you don;lt make it easy for Access, you (ie Access) will have to read and process every date to determine whether you need it or not, which is a much slower process.
 

Gasman

Enthusiastic Amateur
Local time
Today, 07:11
Joined
Sep 21, 2011
Messages
14,049
Hi again,
I got a little step further I think. I ran following


Dim strSQL As String
strSQL = "SELECT * FROM availability_form WHERE " & Me.Select_Availability2 & "" & ">0.2"""
DoCmd.ApplyFilter strSQL

But it gave following error:
Syntax error in string in query expression '[Month 1]>0.2""

It seems it does translate already in the correct variable [Month 1] but the filter expression doesn't work.... it should be something small....
Erik:rolleyes:

Don't you have too many " in that expression.?
Code:
strSQL = "SELECT * FROM availability_form WHERE " & Me.Select_Availability2 & ">0.2"
Debug.Print strSQL
 

droops14

New member
Local time
Today, 00:11
Joined
May 21, 2019
Messages
8
Hi Gasman
yes your suggestion works and I've got my solution now! Great!
Took me couple hours for probably something easy for a specialist but I'm learning!
Me happy!
Thanks man!
Erik
 

theDBguy

I’m here to help
Staff member
Local time
Today, 00:11
Joined
Oct 29, 2018
Messages
21,358
Hi Gasman
yes your suggestion works and I've got my solution now! Great!
Took me couple hours for probably something easy for a specialist but I'm learning!
Me happy!
Thanks man!
Erik
Hi Erik. Congratulations! Glad to hear you got it sorted out. Good luck with your project. Thanks for the assist, Gasman!
 

Users who are viewing this thread

Top Bottom