Query from a Combo box for both equal to and not equal to.

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Ihave a combo box which has the options 'H', 'no H' and 'All'
I want it to 'filter the base column'

In my query i basically want:

IIf([Forms]![Main]![Combo] = "no H",<>"H","H")

Or [Combo]="All"

I did have
WHERE (((Gang.Base)=[Forms]![Main]![Combo])) OR
((([Forms]![Main]![Combo])="All"))
which works fine but when i try to incorporate an iif statement it goes
wrong. The problem seams to be the <> bit. I don't think im getting the
syntax right but everything i've tried has failed.

Thanks for any help

Tom
 
(SomeField = "H" and [Forms]![Main]![Combo] = "H")
OR (SomeField <> "H" and [Forms]![Main]![Combo] = "no H")
OR ([Forms]![Main]![Combo] = "ALL")


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..
 
I can't get it to work:
atm i have

WHERE ((([Gang]![Base]="H Base" And [Forms]![Main]![Combo]="H base") Or
([Gang]![Base]<>"H Base" And [Forms]![Main]![Combo]="No H base") Or
([Forms]![Main]![Combo]="All")))

But it never returns any results.

I wasn't sure what you ment by SomeField i assume it is the Base filed i am
tring to filter?

Thanks for any help

Tom


John Spencer said:
(SomeField = "H" and [Forms]![Main]![Combo] = "H")
OR (SomeField <> "H" and [Forms]![Main]![Combo] = "no H")
OR ([Forms]![Main]![Combo] = "ALL")


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

TomK said:
Ihave a combo box which has the options 'H', 'no H' and 'All'
I want it to 'filter the base column'

In my query i basically want:

IIf([Forms]![Main]![Combo] = "no H",<>"H","H")

Or [Combo]="All"

I did have
WHERE (((Gang.Base)=[Forms]![Main]![Combo])) OR
((([Forms]![Main]![Combo])="All"))
which works fine but when i try to incorporate an iif statement it goes
wrong. The problem seams to be the <> bit. I don't think im getting the
syntax right but everything i've tried has failed.

Thanks for any help

Tom
Click to expand...
Click to expand...
 
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

Since I did not know the name of your field I just made up a field name.

The following should work IF the values returned by the combobox are what
you say. Try adding Forms!Main!Combo to the select list and seeing what is
actually being returned.
SELECT Gang.*, [Forms]![Main]![Combo]
FROM Gang

If you are getting the values you expect then the following should work

SELECT Gang.*, [Forms]![Main]![Combo]
FROM Gang
WHERE ([Gang].[Base]="H Base" And [Forms]![Main]![Combo]="H base") Or
([Gang].[Base]<>"H Base" And [Forms]![Main]![Combo]="No H base") Or
([Forms]![Main]![Combo]="All")


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

TomK said:
I can't get it to work:
atm i have

WHERE ((([Gang]![Base]="H Base" And [Forms]![Main]![Combo]="H base") Or
([Gang]![Base]<>"H Base" And [Forms]![Main]![Combo]="No H base") Or
([Forms]![Main]![Combo]="All")))

But it never returns any results.

I wasn't sure what you ment by SomeField i assume it is the Base filed i
am
tring to filter?

Thanks for any help

Tom


John Spencer said:
(SomeField = "H" and [Forms]![Main]![Combo] = "H")
OR (SomeField <> "H" and [Forms]![Main]![Combo] = "no H")
OR ([Forms]![Main]![Combo] = "ALL")


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

TomK said:
Ihave a combo box which has the options 'H', 'no H' and 'All'
I want it to 'filter the base column'

In my query i basically want:

IIf([Forms]![Main]![Combo] = "no H",<>"H","H")

Or [Combo]="All"

I did have
WHERE (((Gang.Base)=[Forms]![Main]![Combo])) OR
((([Forms]![Main]![Combo])="All"))
which works fine but when i try to incorporate an iif statement it goes
wrong. The problem seams to be the <> bit. I don't think im getting the
syntax right but everything i've tried has failed.

Thanks for any help

Tom
Click to expand...
Click to expand...
Click to expand...
 
Here is the full SQL statement

SELECT Gang.Date, [Not Cast].Reason, Sum(Volume_list.Cubic) AS VolumeSum,
Count([Not Cast].Unit) AS NotCastCount
FROM Volume_list INNER JOIN (Gang INNER JOIN [Not Cast] ON Gang.GangID =
[Not Cast].GangNo) ON Volume_list.Unit = [Not Cast].Unit
WHERE (((Gang.Base)="H Base") AND (([Forms]![Main]![Combo])="H Base")) OR
(((Gang.Base)<>"H Base") AND (([Forms]![Main]![Combo])="No H base")) OR
((([Forms]![Main]![Combo])="All"))
GROUP BY Gang.Date, [Not Cast].Reason
HAVING (((Gang.Date) Between [Forms]![Main]![Date From] And
([Forms]![Main]![DateTo]-1)));


The problem im having is with the Combo box. I can get it to work with
either being able to select 'H base' and 'All' but 'No H base' wont work.. Or
with 'no H base' working and 'H base' not.

I have checked spelling and case sensitivity.

Thanks for your help

John Spencer said:
Please copy and post the SQL of your query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

Since I did not know the name of your field I just made up a field name.

The following should work IF the values returned by the combobox are what
you say. Try adding Forms!Main!Combo to the select list and seeing what is
actually being returned.
SELECT Gang.*, [Forms]![Main]![Combo]
FROM Gang

If you are getting the values you expect then the following should work

SELECT Gang.*, [Forms]![Main]![Combo]
FROM Gang
WHERE ([Gang].[Base]="H Base" And [Forms]![Main]![Combo]="H base") Or
([Gang].[Base]<>"H Base" And [Forms]![Main]![Combo]="No H base") Or
([Forms]![Main]![Combo]="All")


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

TomK said:
I can't get it to work:
atm i have

WHERE ((([Gang]![Base]="H Base" And [Forms]![Main]![Combo]="H base") Or
([Gang]![Base]<>"H Base" And [Forms]![Main]![Combo]="No H base") Or
([Forms]![Main]![Combo]="All")))

But it never returns any results.

I wasn't sure what you ment by SomeField i assume it is the Base filed i
am
tring to filter?

Thanks for any help

Tom


John Spencer said:
(SomeField = "H" and [Forms]![Main]![Combo] = "H")
OR (SomeField <> "H" and [Forms]![Main]![Combo] = "no H")
OR ([Forms]![Main]![Combo] = "ALL")


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

Ihave a combo box which has the options 'H', 'no H' and 'All'
I want it to 'filter the base column'

In my query i basically want:

IIf([Forms]![Main]![Combo] = "no H",<>"H","H")

Or [Combo]="All"

I did have
WHERE (((Gang.Base)=[Forms]![Main]![Combo])) OR
((([Forms]![Main]![Combo])="All"))
which works fine but when i try to incorporate an iif statement it goes
wrong. The problem seams to be the <> bit. I don't think im getting the
syntax right but everything i've tried has failed.

Thanks for any help

Tom
Click to expand...
Click to expand...
Click to expand...
 
Hi,

It does work.. I was just being a bit stupid. (I had an invalid date)

Thanks for you help

It is much appriciated

Tom
 
Back
Top