Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 333
  • Last Modified:

Trying to verify Filter syntax for Access form.

Just wanted to see if someone could have a quick look at my syntax I'm using on my filter for my form. It's based off of information on another form which is currently open with data populated. (I used to have the criteria in a query but wanted to move the criteria into the forms filter instead).

Here is the syntax I tried;

[LineItemMonth] = Eval('[Forms]![frmOperatingPlan]![cboMonth]') and [LineItemYear] = Eval('[Forms]![frmOperatingPlan]![txtYear]')

Maybe I need to do some kind of refresh or requery on the form assuming the syntax is correct?

Thanks!
0
Jarred Meyer
Asked:
Jarred Meyer
  • 4
  • 3
  • 2
  • +1
3 Solutions
 
peter57rCommented:
I would have gone for..

[LineItemMonth] = Eval("'" & [Forms]![frmOperatingPlan]![cboMonth] & "'") and [LineItemYear] = Eval("'" & [Forms]![frmOperatingPlan]![txtYear] & "'")

but I'm not 100% confident...
0
 
Jeffrey CoachmanMIS LiasonCommented:
What you posted is not technically a "Filter".
All you are doing there is assigning some values to a field/control...

It is also not clear if this is in a controlsource or in VBA

First do something like this on a button to even see if the value is what you want
msgbox Eval('[Forms]![frmOperatingPlan]![cboMonth]') and [LineItemYear] = Eval('[Forms]![frmOperatingPlan]![txtYear]')

A Filter in Access will typically look Like this:
"SomeField=" & SomeValue
(For a numeric filter value)
ex: "EmployeeID=" & me.SelectedEmployeeID

or like this for a string filter value:
"SomeField=" & "'" & SomeValue & "'"
ex: "State=" & "'" & me.StateName & "'"
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Boag, I tried puttin the code you provided in an on click event to a button on the form and I got a Compile error.. It's highlighting the; msgbox Eval('[Forms]![frmOperatingPlan]![cboMonth]') and [LineItemYear] = Eval('[Forms]![frmOperatingPlan]![txtYear]') (first tick mark)

I have managed to put together something that works based on some syntax you both provided. (I also removed the Eval from this.. Doesn't appear to have been necessary for filtering. I only originally had it on there when I was using it in criteria for the query)


So here goes;

On the OnLoad event for the form I placed the following code;

Private Sub Form_Load()


Me.Filter = "[LineItemMonth] = " & "'" & [Forms]![frmOperatingPlan]![cboMonth] & "'" & " and " & "[LineItemYear] = " & [Forms]![frmOperatingPlan]![txtYear]
Me.FilterOn = True


End Sub


It took me a while to get the syntax just right but it did the trick. I'm guessing I could just apply this to the just the form itself without even needing to put this all in vba but it worked pretty good.. Besides, doing this maybe I can put an if statement in that will filter based on which date form is open that it is reading from.

Thanks for both of yours' help!
0
What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

 
Jeffrey CoachmanMIS LiasonCommented:
Just for fun, can you try it like this:

Me.Filter = "[LineItemMonth] = " & [Forms]![frmOperatingPlan]![cboMonth] & " and " & "[LineItemYear] = " & [Forms]![frmOperatingPlan]![txtYear]
Me.FilterOn = True
0
 
Jarred MeyerProduction ManagerAuthor Commented:
No problem;

I get a "Enter Parameter Value" box for the month I just ran..

parm
0
 
Jeffrey CoachmanMIS LiasonCommented:
OK then, never mind...

I was just curious...
0
 
Jarred MeyerProduction ManagerAuthor Commented:
No problem Boag..

Thanks again for the help!
0
 
Helen FeddemaCommented:
Fancy Filters formSee my Fancy Filters sample database for syntax examples for creating filters based on values of various data types selected from textboxes and combo boxes on a form:

http://www.helenfeddema.com/Files/accarch129.zip
0
 
Helen FeddemaCommented:
If you are getting the Parameter Value pop-up, that is because there is a parameter on a query, which should be removed.
0
 
Jarred MeyerProduction ManagerAuthor Commented:
Thanks for the information Helen. I will definitely put that to good use. I already got managed to resolve this problem though. We were just experimenting when we got the Parameter pop-up
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 4
  • 3
  • 2
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now