By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
429,480 Members | 771 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 429,480 IT Pros & Developers. It's quick & easy.

Comparison Operators with a Subform

P: 1
Access 2000, Windows 2000 Professional

I have a form I've developed that is designed for those not familiar with access and its query tool. WIthin a subform in the form there is presented information from a query I've already created. This query's information is manipulated by the user selecting values from drop down-combo boxes on the form. Its a requery form, really.

You may be familiar with how this works. Its done by using criteria expressions on the query. However, when I try to create a combo box which would give me results for comparison operators such as >,<, =, =<, & => then this is when I have trouble. I have nearly no idea how to get this to work.

I found an example database and form which does exactly what I'm looking to accomplish. But, this example database features an equation in the form which calculates a person's age based on DOB and the Current Date because a age value isn't included in the tables. This equation or expression has been inputted into the Criteria Expression field on the query and is used to find a record of anyone (for example) 35 years or younger. The field is referenced when the user selects the comparison operator from a combo box and then enters an age value into a text box. Then the subform presents all records with age 35 or younger.

IIf([Forms]![Main Form]![cboOperator] Is Null Or [Forms]![Main Form]![txtAge] Is Null,True,IIf([DateOfbirth] Is Null,Null,Eval(DateDiff("yyyy",[DateOfBirth],Date())+(Format(Date(),"mmdd")<Format([DateOfBirth],"mmdd")) & [Forms]![Main Form]![cboOperator] & [Forms]![Main Form]![txtAge])))

To summarise. I need a combo box featuring greater than, equal than, etc. values to filter information in a subform. The subform is connected to an existing query.

I can't seem to post an image of my form at the moment to give you a better idea. Sorry.

Any solutions on how to get this to work? I hope this email makes sense.


Mar 15 '07 #1
Share this Question
Share on Google+
1 Reply

Expert Mod 10K+
P: 12,359
The problem is when you pass the operator to the query, it doesn't see it as an operator but instead as a string. As far as I know, there's no way around this.

You can, however, make a different query for each operator. Or if you want to get fancy, you can change the query def.
Mar 15 '07 #2

Post your reply

Sign in to post your reply or Sign up for a free account.