Help with very simple query

[Sewagerat]

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.

 

[MStef]

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

 

[spaddhu]

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])))

 

[spaddhu]

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));

 

[namliam]

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....
less-than (<), greater-than (>)

 

[Sewagerat]

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

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

 

[spaddhu]

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

 

[Sewagerat]

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.

 

[spaddhu]

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

 

[namliam]

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.

 

[spaddhu]

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

 

[Sewagerat]

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

 

[MStef]

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

 

[spaddhu]

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.

 

[Sewagerat]

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.

 

[namliam]

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

 

[Sewagerat]

Ty 10char