?
Solved

Trying to verify Filter syntax for Access form.

Posted on 2012-03-13
10
Medium Priority
?
325 Views
Last Modified: 2012-03-13
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
Comment
Question by:SeyerIT
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 3
  • 2
  • +1
10 Comments
 
LVL 77

Assisted Solution

by:peter57r
peter57r earned 920 total points
ID: 37715901
I would have gone for..

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

but I'm not 100% confident...
0
 
LVL 74

Accepted Solution

by:
Jeffrey Coachman earned 920 total points
ID: 37715903
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
 
LVL 2

Author Comment

by:SeyerIT
ID: 37716177
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
Prepare for your VMware VCP6-DCV exam.

Josh Coen and Jason Langer have prepared the latest edition of VCP study guide. Both authors have been working in the IT field for more than a decade, and both hold VMware certifications. This 163-page guide covers all 10 of the exam blueprint sections.

 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37716235
Just for fun, can you try it like this:

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

Author Comment

by:SeyerIT
ID: 37716475
No problem;

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

parm
0
 
LVL 74

Expert Comment

by:Jeffrey Coachman
ID: 37716529
OK then, never mind...

I was just curious...
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37716531
No problem Boag..

Thanks again for the help!
0
 
LVL 31

Assisted Solution

by:Helen Feddema
Helen Feddema earned 160 total points
ID: 37716664
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
 
LVL 31

Expert Comment

by:Helen Feddema
ID: 37716676
If you are getting the Parameter Value pop-up, that is because there is a parameter on a query, which should be removed.
0
 
LVL 2

Author Comment

by:SeyerIT
ID: 37716715
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

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

You need to know the location of the Office templates folder, so that when you create new templates, they are saved to that location, and thus are available for selection when creating new documents.  The steps to find the Templates folder path are …
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…
Suggested Courses

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question