>< query from form

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.
Derek BrownMDAsked:
Who is Participating?
 
IrogSintaCommented:
Actually, 1,2,3...
0
 
peter57rCommented:
If your current criteria works OK then all you should have to do is remove 'Like' and add '>='
0
 
als315Commented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
IrogSintaCommented:
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
 
Derek BrownMDAuthor Commented:
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
 
Derek BrownMDAuthor Commented:
Hi, you may have guessed, that didn't work. Any Ideas? anyone
0
 
IrogSintaCommented:
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
 
Derek BrownMDAuthor Commented:
Hi that looks brilliant!

How is the choice made? is it line 1, 2, or 3? as simple as that?
0
 
IrogSintaCommented:
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
 
Derek BrownMDAuthor Commented:
Hi so the toggle values must be 0, 1, 2.?
0
 
Derek BrownMDAuthor Commented:
So MyToggle must be 0 then?

Never seen a prettier solution for this problem

Brilliant

Thank you.
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.

All Courses

From novice to tech pro — start learning today.