|
|
|
date: Thu, 28 Aug 2008 10:56:09 -0700,
group: microsoft.public.access.queries
back
Iif not working
Hi, I'm new here. But this is driving me crazy! What is wrong with this IIF
statement? Sometimes, if I try to use "|" because of errors with a "," and
it works. But then I go back into design, Access does not recognize it. I
change the "|" back to "," and it still tells me it's an invalid character.
And if I am in the "Design" mode, it does not let me go back to SQL.
PrmtTypeXY: IIf ([Prmt_Type]<>"Building",
IIf([prmt_type]="zoning",[prmt_type],"OTHER"),[prmt_type])
What am I doing wrong? Thank you.
--
Noreen D P
PA - GIS Technician
date: Thu, 28 Aug 2008 10:56:09 -0700
author: Noreen
Re: Iif not working
Noreen wrote:
> Hi, I'm new here. But this is driving me crazy! What is wrong with
> this IIF statement? Sometimes, if I try to use "|" because of errors
> with a "," and it works.
Well, first of all, I don't understand what you are doing with that "|"
character ... what gave you the idea to try to use it?
Could you show us the statement where you used it and it "worked"?
> But then I go back into design, Access does
> not recognize it. I change the "|" back to "," and it still tells
> me it's an invalid character. And if I am in the "Design" mode, it
> does not let me go back to SQL.
>
> PrmtTypeXY: IIf ([Prmt_Type]<>"Building",
> IIf([prmt_type]="zoning",[prmt_type],"OTHER"),[prmt_type])
>
> What am I doing wrong? Thank you.
I don't see anything wrong with this expression, sorry.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
date: Thu, 28 Aug 2008 14:10:11 -0400
author: Bob Barrows [MVP] com
RE: Iif not working
I think your formulation is not what you intended.
PrmtTypeXY: IIf ([Prmt_Type]<>"Building",
IIf([prmt_type]="zoning",[prmt_type],"OTHER"),[prmt_type])
This is how I see yours working --
If [Prmt_Type] not equal "Building" then check more else [prmt_type]
which will be "Building".
then check more --
If [Prmt_Type] equal "zoning" then "zoning" else [prmt_type]
TRUTH TABLE --
If [prmt_type] -- Results
Building - Building
zoning - zoning
anything - anything
--
KARL DEWEY
Build a little - Test a little
"Noreen" wrote:
> Hi, I'm new here. But this is driving me crazy! What is wrong with this IIF
> statement? Sometimes, if I try to use "|" because of errors with a "," and
> it works. But then I go back into design, Access does not recognize it. I
> change the "|" back to "," and it still tells me it's an invalid character.
> And if I am in the "Design" mode, it does not let me go back to SQL.
>
> PrmtTypeXY: IIf ([Prmt_Type]<>"Building",
> IIf([prmt_type]="zoning",[prmt_type],"OTHER"),[prmt_type])
>
> What am I doing wrong? Thank you.
> --
> Noreen D P
> PA - GIS Technician
date: Thu, 28 Aug 2008 11:30:12 -0700
author: KARL DEWEY
RE: Iif not working
TRUTH TABLE --
If [prmt_type] -- Results
Building - Building
zoning - zoning
anything - OTHER
--
KARL DEWEY
Build a little - Test a little
"KARL DEWEY" wrote:
> I think your formulation is not what you intended.
> PrmtTypeXY: IIf ([Prmt_Type]<>"Building",
> IIf([prmt_type]="zoning",[prmt_type],"OTHER"),[prmt_type])
>
> This is how I see yours working --
> If [Prmt_Type] not equal "Building" then check more else [prmt_type]
> which will be "Building".
> then check more --
> If [Prmt_Type] equal "zoning" then "zoning" else [prmt_type]
>
> TRUTH TABLE --
> If [prmt_type] -- Results
> Building - Building
> zoning - zoning
> anything - anything
>
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "Noreen" wrote:
>
> > Hi, I'm new here. But this is driving me crazy! What is wrong with this IIF
> > statement? Sometimes, if I try to use "|" because of errors with a "," and
> > it works. But then I go back into design, Access does not recognize it. I
> > change the "|" back to "," and it still tells me it's an invalid character.
> > And if I am in the "Design" mode, it does not let me go back to SQL.
> >
> > PrmtTypeXY: IIf ([Prmt_Type]<>"Building",
> > IIf([prmt_type]="zoning",[prmt_type],"OTHER"),[prmt_type])
> >
> > What am I doing wrong? Thank you.
> > --
> > Noreen D P
> > PA - GIS Technician
date: Thu, 28 Aug 2008 11:55:02 -0700
author: KARL DEWEY
Re: Iif not working
If that is what you want to achieve. Either will give you the same as before.
Most folks write a nest IIF statement as Condition to test, Results if true,
((Condition to test, Results if true, Results if not true)).
You wrote it --
Condition to test, ((Condition to test, Results if true, Results if not
true)), Results if true.
Can you show a truth table for what you want as results?
--
KARL DEWEY
Build a little - Test a little
"Ron2006" wrote:
> How about writing it this way:
>
>
> PrmtTypeXY: iif (([Prmt_Type]="Building" OR [Prmt_Type]="zoning"),
> [prmt_type],"OTHER")
>
>
> or the longer format
>
>
> PrmtTypeXY: iif ([Prmt_Type]="Building",
> [prmt_type],iif([Prmt_Type]="zoning",[prmt_type],"OTHER"))
>
>
> Ron
>
date: Thu, 28 Aug 2008 12:41:01 -0700
author: KARL DEWEY
Re: Iif not working
The ) was correct for the syntax used --
IIF(Condition, (condition, true results, false results), false results)
When you moved the ) it became --
IIF(Condition, (condition, true results, false results, false results))
--
KARL DEWEY
Build a little - Test a little
"Lord Kelvan" wrote:
> PrmtTypeXY: IIf ([Prmt_Type]<>"Building",
> IIf([prmt_type]="zoning",[prmt_type],"OTHER",[prmt_type]))
>
> you had the ) in the wrong place
> with nested iifs you put all closing ) at the end of the statement not
> in the middle of it
>
> hope this helps
>
> Regards
> Kelvan
>
date: Thu, 28 Aug 2008 13:49:01 -0700
author: KARL DEWEY
Re: Iif not working
Please forgive me for not getting back sooner, but I was away without a
computer and I wasn't expecting such quick answers. So, thank you one and
all. I will again try this with Ron's answer (what I wanted) and see what
happens.
SQL had given me the error previously and told me to try "|" . Well, it
worked once...
I will let you know how it works out...
Noreen D P
PA - GIS Technician
"Ron2006" wrote:
> How about writing it this way:
>
>
> PrmtTypeXY: iif (([Prmt_Type]="Building" OR [Prmt_Type]="zoning"),
> [prmt_type],"OTHER")
>
>
> or the longer format
>
>
> PrmtTypeXY: iif ([Prmt_Type]="Building",
> [prmt_type],iif([Prmt_Type]="zoning",[prmt_type],"OTHER"))
>
>
> Ron
>
date: Tue, 9 Sep 2008 08:32:02 -0700
author: Noreen .(donotspam)
RE: Iif not working
Yes, this is what I am trying to do...
--
Noreen D P
PA - GIS Technician
"KARL DEWEY" wrote:
> I think your formulation is not what you intended.
> PrmtTypeXY: IIf ([Prmt_Type]<>"Building",
> IIf([prmt_type]="zoning",[prmt_type],"OTHER"),[prmt_type])
>
> This is how I see yours working --
> If [Prmt_Type] not equal "Building" then check more else [prmt_type]
> which will be "Building".
> then check more --
> If [Prmt_Type] equal "zoning" then "zoning" else [prmt_type]
>
> TRUTH TABLE --
> If [prmt_type] -- Results
> Building - Building
> zoning - zoning
> anything - anything
>
> --
> KARL DEWEY
> Build a little - Test a little
>
>
> "Noreen" wrote:
>
> > Hi, I'm new here. But this is driving me crazy! What is wrong with this IIF
> > statement? Sometimes, if I try to use "|" because of errors with a "," and
> > it works. But then I go back into design, Access does not recognize it. I
> > change the "|" back to "," and it still tells me it's an invalid character.
> > And if I am in the "Design" mode, it does not let me go back to SQL.
> >
> > PrmtTypeXY: IIf ([Prmt_Type]<>"Building",
> > IIf([prmt_type]="zoning",[prmt_type],"OTHER"),[prmt_type])
> >
> > What am I doing wrong? Thank you.
> > --
> > Noreen D P
> > PA - GIS Technician
date: Tue, 9 Sep 2008 09:29:02 -0700
author: Noreen .(donotspam)
Re: Iif not working
Oops, the statement still gave me errors. I didn't notice that Ron's answer
had = "Building" instead of <> "Building". But I tried most answers and
still got the same errors. It's like it's reading a different language or
something, because double quotes were wrong, too.
All those answers still gave me errors! Help!
BTW, how do I show you my results? I can't paste and I can't attach...
--
Noreen D P
PA - GIS Technician
--
Noreen D P
PA - GIS Technician
"Noreen" wrote:
> Please forgive me for not getting back sooner, but I was away without a
> computer and I wasn't expecting such quick answers. So, thank you one and
> all. I will again try this with Ron's answer (what I wanted) and see what
> happens.
>
> SQL had given me the error previously and told me to try "|" . Well, it
> worked once...
>
> I will let you know how it works out...
>
> Noreen D P
> PA - GIS Technician
>
>
> "Ron2006" wrote:
>
> > How about writing it this way:
> >
> >
> > PrmtTypeXY: iif (([Prmt_Type]="Building" OR [Prmt_Type]="zoning"),
> > [prmt_type],"OTHER")
> >
> >
> > or the longer format
> >
> >
> > PrmtTypeXY: iif ([Prmt_Type]="Building",
> > [prmt_type],iif([Prmt_Type]="zoning",[prmt_type],"OTHER"))
> >
> >
> > Ron
> >
date: Tue, 9 Sep 2008 09:48:01 -0700
author: Noreen .(donotspam)
Re: Iif not working
Post your query SQL.
Open in design view, click on VIEW - SQL View, highlight all, copy, paste in
a post.
--
KARL DEWEY
Build a little - Test a little
"Noreen" wrote:
> Thanks for the trick -- it's very helpful. But my () DO match up...
> --
> Noreen D P
> PA - GIS Technician
>
>
> "John Spencer" wrote:
>
> > Make sure your parentheses match up. You should have the same number of left
> > and right parentheses. I think you are missing one at the end.
> >
> > IIf([Prmt_Type]<>'Building',(IIf([prmt_type]='zoning',[prmt_type],'OTHER'),[prmt_type]))
> >
> > A little trick to make sure you have the correct number (not necessarily in
> > the correct spot) is to start at the left and add one for each "(" and
> > subtract one for each ")". IF you get to the end and the result is not zero,
> > then you need to figure out what is missing. Positive number equates to too
> > few ")" and a negative number equates to too many ")". Or too many or too few
> > "(".
> >
> > John Spencer
> > Access MVP 2002-2005, 2007-2008
> > The Hilltop Institute
> > University of Maryland Baltimore County
> >
> > Noreen wrote:
> > > I'm sorry for all the notes, but I realized I was using the wrong field for
> > > the second IIF statement. But I get the same error.
> >
date: Tue, 9 Sep 2008 12:30:58 -0700
author: KARL DEWEY
Re: Iif not working
I find it helpful to break out nested statements like so:
IIf
(
[Prmt_Type]<>'Building',
( <---
IIf
(
[prmt_type]='zoning',
[prmt_type],
'OTHER'
),
[prmt_type]
)
... which makes it pretty obvious that you failed to close the second
parenthesis. There was no reason to start enclosing the entire nested
iif in parentheses. This should work:
IIf
(
[Prmt_Type]<>'Building',
IIf
(
[prmt_type]='zoning',
[prmt_type],
'OTHER'
),
[prmt_type]
)
... which, after removing the whitespace, translates to this
single-liner:
IIf([Prmt_Type]<>'Building',IIf([prmt_type]='zoning',[prmt_type],'OTHER'
),[prmt_type])
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
date: Tue, 9 Sep 2008 15:34:59 -0400
author: Bob Barrows [MVP] com
Re: Iif not working
Bob,
Thanks. I was looking at my 1st question on 8/28 instead of one of them
today. At any rate, both give me that error.
And Karl,
Thank you, I know how to do the copy/paste thing you referenced, but I was
referring to a dialog box with the error in it and a copy from hitting the
"Print Screen" and then pasting. (This didn't work)
--
Noreen D P
PA - GIS Technician
"Bob Barrows [MVP]" wrote:
> I find it helpful to break out nested statements like so:
> IIf
> (
> [Prmt_Type]<>'Building',
> ( <---
> IIf
> (
> [prmt_type]='zoning',
> [prmt_type],
> 'OTHER'
> ),
> [prmt_type]
> )
> .... which makes it pretty obvious that you failed to close the second
> parenthesis. There was no reason to start enclosing the entire nested
> iif in parentheses. This should work:
>
> IIf
> (
> [Prmt_Type]<>'Building',
> IIf
> (
> [prmt_type]='zoning',
> [prmt_type],
> 'OTHER'
> ),
> [prmt_type]
> )
>
> .... which, after removing the whitespace, translates to this
> single-liner:
>
> IIf([Prmt_Type]<>'Building',IIf([prmt_type]='zoning',[prmt_type],'OTHER'
> ),[prmt_type])
>
>
>
>
> --
> Microsoft MVP -- ASP/ASP.NET
> Please reply to the newsgroup. The email account listed in my From
> header is my spam trap, so I don't check it very often. You will get a
> quicker response by posting to the newsgroup.
>
>
>
date: Tue, 9 Sep 2008 13:10:01 -0700
author: Noreen .(donotspam)
|
|
