I have a query for which I need to be able to search using less-than (>), greater-than (<), not-equal to (<>).

It is a simple query so the criteria is [Enter]. I want to be able to use the symbols in the pop-up window but when I enter something like this (view untitled.JPG) I get this pop-up (view untitled2.JPG).

I tried using speech marks in all sorts of places as well as spaces, and I also rearranged it but I still get the same pop-up. Please help me because it seems I'm not understand a very simple thing.

Look at "DemoQrySelectAllA2000.mdb" (attachmnet, zip).
Open Form and try.
Look at Form, VBA.
Adapt it as you need.

If you want a single parameter for the criteria, then it's going to be tougher manipulation of strings (because <, > have one char and <> has 2 chars, and hence you don't know how to extract)
Instead I feel you can have two prompts, one for the operator and the other for the value. Then you can do it like the following.

Eg. Table2 : Age | Gender | Education
Query : IIf([Symbol]="<",([Table2].[Age])<[Value],
IIf([Symbol]=">",([Table2].[Age])>[Value],IIf([Symbol]="<>",([Table2].[Age])<>[Value],([Table2].[Age])=[Value])))

Sorry, that is only the where clause of the query.
The full query will look like...
SELECT Table2.Age, Table2.Gender, Table2.Education
FROM Table2
WHERE (((IIf([Symbol]="<",([Table2].[Age])<[Value],IIf([Symbol]=">",([Table2].[Age])>[Value],IIf([Symbol]="<>",([Table2].[Age])<>[Value],([Table2].[Age])=[Value]))))<>False));

I have a query for which I need to be able to search using less-than (>), greater-than (<), not-equal to (<>).
I wont go into details, but I am sure you know this to be wrong.... :eek:
less-than (<), greater-than (>)

namliam, i was just showing the symbols....

Thanks spaddhu, i'll give it a try, looks scary :o

It does need some care, otherwise, it's just a simple where clause, in which you say that if the symbol is "<" then perform field<val, else if symbol is ">" then perform field>val, etc...
Just take care of the paranthesis, and I don't feel it should be troublesome...

Thanks it worked, I think im getting the hang of SQL.

Thanks MStef for replying as well, I will make good use of that file.

Glad to help you - < I'm a Vodafone customer ;) >

Sorry, that is only the where clause of the query.
The full query will look like...
SELECT Table2.Age, Table2.Gender, Table2.Education
FROM Table2
WHERE (((IIf([Symbol]="<",([Table2].[Age])<[Value],IIf([Symbol]=">",([Table2].[Age])>[Value],IIf([Symbol]="<>",([Table2].[Age])<>[Value],([Table2].[Age])=[Value]))))<>False));

Using an IIF in a query where clause is a BAD idea, bad bad bad idea... this is what you have OR statements for....
WHERE
( [Symbol]="<" and [Table2].[Age]< [Value] )
OR ( [Symbol]=">" and [Table2].[Age] >[Value] )
OR ( [Symbol]="<>" and [Table2].[Age]<>[Value] )
OR ( [Symbol]="=" and [Table2].[Age]= [Value] )

Even better would be to build the sql in code.... Something like:
strSQL = ""
strSQL = strSQL & " SELECT Table2.Age, Table2.Gender, Table2.Education "
strSQL = strSQL & " FROM Table2 "
strSQL = strSQL & " [Table2].[Age] " & Me.Symbol & " " & Me.value
Currentdb.Querydefs("YourQuery").sql = strsql
That way you only get the disired hardcoded query that you need, elimating a lot of the CPU needed potentially speeding up your query EXPONENTIALY with the number of records it needs to search.

Edit: Which is actually what MStef is doing in his DB as well.

Correct Namliam. Thanx for the pointing out. I would go by your view.

I find the SQL one a bit odd namliam, I don't understand it haha, I tried using it but Access says it does not recognise the SELECT, or something along those lines :P. The first one you posted looks very neat, I'll probably end up using that one.

I would like to include an ORDER BY clause but I am having trouble because it seems Access is not letting me, please help me almighty gurus :)

Here it is, Look at Demo a new one.
Open Form and try.

For ORDER BY, it is always better to specify it in the Design View. This way you are not going to face any difficulty. Just indicate Ascending/Descending in the Sort row of the required field.

Ok, thanks guys, everything worked well. I don't see how MStef had the ORDER BY clause after WHERE because on my one (the VBA which namliam gave), I try to insert it but a pop-up comes up saying there is text underneath the WHERE clause.

I find the SQL one a bit odd namliam, I don't understand it haha, I tried using it but Access says it does not recognise the SELECT,

By "the sql one" you mean this one?
strSQL = ""
strSQL = strSQL & " SELECT Table2.Age, Table2.Gender, Table2.Education "
strSQL = strSQL & " FROM Table2 "
strSQL = strSQL & " [Table2].[Age] " & Me.Symbol & " " & Me.value
Currentdb.Querydefs("YourQuery").sql = strsql

This goes into VBA where you build the query from scratch... Not into a query directly


I would like to include an ORDER BY clause but I am having trouble because it seems Access is not letting me, please help me almighty gurus :)

SELECT Table2.Age, Table2.Gender, Table2.Education
FROM Table2
WHERE
( [Symbol]="<" and [Table2].[Age]< [Value] )
OR ( [Symbol]=">" and [Table2].[Age] >[Value] )
OR ( [Symbol]="<>" and [Table2].[Age]<>[Value] )
OR ( [Symbol]="=" and [Table2].[Age]= [Value] )
Order by Table2.Age
That should work

Ty :) 10char