?
Solved

>< query from form

Posted on 2012-09-08
11
Medium Priority
?
462 Views
Last Modified: 2012-09-08
I have a form that has Combo boxes that are used as the criteria for a query.

The subform uses this data to show continuous records based on the query. If we imagine we are sorting picture frames by height and width in the subform

So if I pick a height on the main form height combo, say 300, I get all the available types of frame 300 high. If I select 200 Wide from the Wide Combo, then obviously I see all frame types 300 x 200. The criteria in the query is:
Like [Forms]![MyForm]![Height Combo] & "*"    and or Like [Forms]![MyForm]![Wide Combo] & "*"  
The "& "*" " allows nothing to be selected so I don't have to choose both height and width if I don't want to.

But what I need is >= Like [Forms]![MyForm]![Height] & "*"  so I can see all picture frames greater than or equal too the size selected from the form. Is there any way to do this from the form.
0
Comment
Question by:DatabaseDek
11 Comments
 
LVL 77

Expert Comment

by:peter57r
ID: 38379582
If your current criteria works OK then all you should have to do is remove 'Like' and add '>='
0
 
LVL 40

Expert Comment

by:als315
ID: 38379587
You say nothing about data in your records. Can you show example? Is size stored as text in one field or you have two numeric fields (width and height)?
If you have numeric fields, you can use in query something like this:
IIF(isnull([Forms]![MyForm]![Height]),True,IIF([Forms]![MyForm]![Height]<=[Height],True,False))
Expression will be True also if [Forms]![MyForm]![Height] is not filled
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38379596
Try
>= Nz([Forms]![MyForm]![Height Combo],0)

Open in new window

If none is selected it would show any greater than or equal to zero.
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

Author Comment

by:DatabaseDek
ID: 38379679
I sometimes think it's harder to write the question that to find the answer. The reason that I said it has to be done from the form (and this is the bit I forgot) is that The user might want to see less than or equal too as well.

I had considered using a toggle switch to allow one of three options 1= equals 2 = greater than or equal to and 3 = less than or equal to. then I could use Irog's  >= Nz([Forms]![MyForm]![Height Combo],0) as in IIf (Forms!MyForm!Toggle=1 , = Nz([Forms]![MyForm]![Height Combo],0), iIf(Nz([Forms]![MyForm]![Toggle=2],>= Nz([Forms]![MyForm]![Height Combo],0), iif( iIf(Nz([Forms]![MyForm]![Toggle=3,<= Nz([Forms]![MyForm]![Height Combo],0), or something like, I can make it work. Unless someone has any other ideas.
0
 

Author Comment

by:DatabaseDek
ID: 38379735
Hi, you may have guessed, that didn't work. Any Ideas? anyone
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38379756
You can use the Choose Function too in your query.  If your field is called FrameHeight for example:

WHERE Choose(Forms!MyForm!Toggle,
[FrameHeight]<=Nz([Forms]![Form2]![Combo0],0),
[FrameHeight]=Nz([Forms]![Form2]![Combo0],0),
[FrameHeight]>=Nz([Forms]![Form2]![Combo0],0))=True

Open in new window

0
 

Author Comment

by:DatabaseDek
ID: 38379835
Hi that looks brilliant!

How is the choice made? is it line 1, 2, or 3? as simple as that?
0
 
LVL 29

Expert Comment

by:IrogSinta
ID: 38379841
Yes it is.  If the first parameter of the Choose function evaluates to a 1, the 2rd parameter is selected, if it evaluates to a 2, the 3rd parameter is selected and so forth...
0
 

Author Comment

by:DatabaseDek
ID: 38379844
Hi so the toggle values must be 0, 1, 2.?
0
 
LVL 29

Accepted Solution

by:
IrogSinta earned 2000 total points
ID: 38379846
Actually, 1,2,3...
0
 

Author Closing Comment

by:DatabaseDek
ID: 38379854
So MyToggle must be 0 then?

Never seen a prettier solution for this problem

Brilliant

Thank you.
0

Featured Post

Prep for the ITIL® Foundation Certification Exam

December’s Course of the Month is now available! Enroll to learn ITIL® Foundation best practices for delivering IT services effectively and efficiently.

Question has a verified solution.

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

Traditionally, the method to display pictures in Access forms and reports is to first download them from URLs to a folder, record the path in a table and then let the form or report pull the pictures from that folder. But why not let Windows retr…
Code that checks the QuickBooks schema table for non-updateable fields and then disables those controls on a form so users don't try to update them.
Basics of query design. Shows you how to construct a simple query by adding tables, perform joins, defining output columns, perform sorting, and apply criteria.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Suggested Courses

850 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