Prev: Date Groups
Next: calculating currency formatted fields
From: Bongard on 26 Jun 2008 14:14
For some reason I can't figure this out. I have two fields in my query
(relevant for this question). [Port Num] (numeric field) and [Type]
(text). I need my criteria to say that If [Port Num] = 210 then [Type]
<> "F". What is the suntax to get this to work in the criteria line of
the query? I've tried putting the field [Type] and having the criteria
be Iif([Port Num] = 210, <>"F") but that doesn't seem to work.

Any help would be appreciated
Thanks,
Brian
From: John Spencer on 26 Jun 2008 15:04
Field: PortNum
Criteria: 210

Field: Type
Criteria: <> "F"

That will give you all records except those that have a Portnum of 210 and
Type of "F" with the exception that records with null values will also be
eliminated.

IF you want to get the records with a null value in one or the other field
then you need to use two more lines of criteria where you enter Is Not Null on
line 2 under PortNum and Is Not Null on line 3 under Type.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County

Bongard wrote:
> For some reason I can't figure this out. I have two fields in my query
> (relevant for this question). [Port Num] (numeric field) and [Type]
> (text). I need my criteria to say that If [Port Num] = 210 then [Type]
> <> "F". What is the suntax to get this to work in the criteria line of
> the query? I've tried putting the field [Type] and having the criteria
> be Iif([Port Num] = 210, <>"F") but that doesn't seem to work.
>
> Any help would be appreciated
> Thanks,
> Brian
From: Marshall Barton on 26 Jun 2008 15:36
Bongard wrote:

>For some reason I can't figure this out. I have two fields in my query
>(relevant for this question). [Port Num] (numeric field) and [Type]
>(text). I need my criteria to say that If [Port Num] = 210 then [Type]
><> "F". What is the suntax to get this to work in the criteria line of
>the query? I've tried putting the field [Type] and having the criteria
>be Iif([Port Num] = 210, <>"F") but that doesn't seem to work.


Try adding a calculated field to the query:
([Port Num] = 210 And [Type] = "F")
and set its criteria to:
False

--
Marsh
MVP [MS Access]
From: Bongard on 1 Jul 2008 09:50
On Jun 26, 2:36 pm, Marshall Barton <marshbar...(a)wowway.com> wrote:
> Bongard wrote:
> >For some reason I can't figure this out. I have two fields in my query
> >(relevant for this question). [Port Num] (numeric field) and [Type]
> >(text). I need my criteria to say that If [Port Num] = 210 then [Type]
> ><> "F". What is the suntax to get this to work in the criteria line of
> >the query? I've tried putting the field [Type] and having the criteria
> >be Iif([Port Num] = 210, <>"F") but that doesn't seem to work.
>
> Try adding a calculated field to the query:
>         ([Port Num] = 210 And [Type] = "F")
> and set its criteria to:
>         False
>
> --
> Marsh
> MVP [MS Access]

Thank you Marshall this worked perfectly. John I had lots of other
criteria in the query and could not only include Portfolio 210 so that
suggestion would not have worked in my case.

Thanks for your help guys,
Brian
 | 
Pages: 1
Prev: Date Groups
Next: calculating currency formatted fields