Re: Query from a Combo box for both equal to and not equal to.
- From: TomK <TomK@xxxxxxxxxxxxxxxxxxxxxxxxx>
- Date: Wed, 28 Mar 2007 07:26:03 -0700
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" wrote:
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" <TomK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:19DCE9CA-BFC4-4342-8530-ACF423401F26@xxxxxxxxxxxxxxxx
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" wrote:
(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" <TomK@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:A557B22C-27E9-43A6-A705-2283484B2C10@xxxxxxxxxxxxxxxx
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
- References:
- Re: Query from a Combo box for both equal to and not equal to.
- From: John Spencer
- Re: Query from a Combo box for both equal to and not equal to.
- Prev by Date: Re: Identify Duplicates (Group Records First)
- Next by Date: Between Expression searching between alpha / #'s - Using Access Form
- Previous by thread: Re: Query from a Combo box for both equal to and not equal to.
- Next by thread: Between Expression searching between alpha / #'s - Using Access Form
- Index(es):
Relevant Pages
|
