query filter using < less than

[ypma]

Happy New Year to One and all .

My problem is as follows : I am using a combo box as a filter for a query and there are 6 Sources I wish to filter.
1,2,3,4,5 <6

Filtering 1 to 5 is no problem, the problem come when I select <6
The table source field is a text .
I tried < "6"

I could get by if the 6th option was " " or Blank but that does not appear to work. or even no filter

Any pointers would be appreciated.
Regards Ypma

 

[llkhoutx]

<6 is a string, 1,2,3,... are numeric. I presume the combo box is a text control. What is the data type of the column being filtered?

Something like this might work, using 1,2,3,4,5,6 as combo box values (assuming your column is numeric), the the filter would be iif( [properly reference combo box control] = 6, "<" & 6, [properly reference combo box control] ).

 

[ypma]

IIkhoutx, thank you for replying to my question.

Will try and explaing my question better.

The source field I am filtering on is text , The 1 to 5 filters are sources , had to be text as other sources that I am not interested in are names of sources .

I was trying to be cleaver and allow the filter to select all records less that 6 as the 6th option from the combo box.

Hope this is clearer as to what i am trying to acheive

Regards Ypma.

 

[arnelgp]

on your query use this criteria:

Where [field] & " " & Replace(Replace(Combo, Combo, "=" & "'" & Combo & "'"), "='<", "<'")

replace the blue text with the correct fieldname and combobox name.

 

[ypma]

[FONT=&quot]Aranelgp. [/FONT]
[FONT=&quot]Thank you for your suggestion. I don't seem to be able to explain what I am trying to achieve so my apologies. Regrettably I have reached my “sell by date “as I am the wrong side of 75 .[/FONT]
[FONT=&quot]Basically the user wishes to review separate reports for status 1 to 5 plus a report showing all 5 status. “Hence my <6” To this end I have produce a combo box to allow the user to select his preference.
[/FONT]
[FONT=&quot]The selected source is entered into the citerea of the source field in the data query via this combo box . Status 1 to 5 work but how do enter the <6 critiera via my combo box ?
[/FONT]
Note . Here in the UK my day is just beginning.
[FONT=&quot]Hope you can advise .[/FONT]

 

[Galaxiom]

Strings can be treated with greater than or less than operators but they behave differently from numbers because they are treated alphabetically.

The following expressions are True:
"10" < "6"
"500 < "6"
"601" > "6"

Hope that gives you the idea.

You can select values from "1" to "5" like this:

WHERE fieldname IN("1", "2", "3", "4", "5")

 

[Galaxiom]

An alternative is to convert the tested string to a number.

WHERE CInt(fieldname) < 6

 

[ypma]

Galaxiom: Thank you for your suggestions, I wish the user to be able to select <6 from the combo box. If I was to enter <"6" directly into the critieria of the source field it works fine , but not via the combo box . Can a string be entered into the criteria of a query field using Combo box ?

Regards Ypma

See demo

 

[ypma]

Thanks to all who gave advice, it got me thinking in the right direction.

I solved my particular problem by adding a “from” and “to” unbound text boxes alongside the combo box. The after update of the combo box populated the two text boxes. I then used the between parameter in the criteria of the source field of the query.