A Microsoft Office (Excel, Word) forum. OfficeFrustration

If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below.

Go Back   Home » OfficeFrustration forum » Microsoft Access » Database Design
Site Map Home Register Authors List Search Today's Posts Mark Forums Read  

Writing and IIf statement in Access?



 
 
Thread Tools Display Modes
  #11  
Old August 12th, 2008, 11:19 AM posted to microsoft.public.access.tablesdbdesign
Roland
external usenet poster
 
Posts: 74
Default Writing and IIf statement in Access?

Thanks John, both work like a dream

"John W. Vinson" wrote:

On Fri, 8 Aug 2008 07:30:01 -0700, Roland
wrote:

Sorry to necro this tread, but I am having a similar problem with a report
that is using several IIf statements within textboxes and I am now trying to
add a second possiblity. (there are a total of 14 Households, but only 2
require their departments to be seperated)

This is what I had orginally for just 1 household
=IIf([HouseholdNumber]=1,"Department: " & [Department],"")

I have tried to add a second household using the above suggestion
=IIf([HouseholdNumber]=1,"Department: " & [Department],""),
IIf([HouseholdNumber]=7,"Department: " & [Department],"")
but this fails. I have also tried And and Or before the 2nd IIf


The first argument can be a logical expression. Try

=IIf([HouseholdNumber]=1 OR [HouseholdNumber] = 7,"Department: " &
[Department],"")

You can also use the IN() syntax:

=IIf([HouseholdNumber] IN (1,7),"Department: " & [Department],"")
--

John W. Vinson [MVP]

  #12  
Old April 3rd, 2010, 03:17 AM posted to microsoft.public.access.tablesdbdesign
Rhonda
external usenet poster
 
Posts: 150
Default Writing and IIf statement in Access?

I am doing a if then statement in Access with multiple like statements. I
think I am close, but I am getting a circular reference error...

here my query
1YTDDEPRAMT: IIf([ASSETCLASSID] Like
"*-CR",[1YTDDEPRAMT]*-1,IIf([ASSETCLASSID] Like
"*DEPLOYC",[1YTDDEPRAMT]*-1,IIf([ASSETCLASSID] Like
"*BASIC-C",[1YTDDEPRAMT]*-1,[1YTDDEPRAMT])))


"Jason" wrote:

Trust me I know the feeling. I'm still in the "keep beating on it until it
works" phase of of my Access education. I just knew the answer to your
problem because I use "IF" a ton in Excel. Anyway, glad I could help.

"Roger Drews" wrote:

Jason
Thank you that solved my problem, I am new at using Access and this was very
helpful.

"Jason" wrote:

Roger,

There are 2 problems. One is that you are missing the last part of your 2nd
conditional statment. The second is that you appear to be testingfor more
than 2 conditions.

Based on what you have written, try this:

field name: = IIf([PT1]=0,[PC1]-[IC1],[PT1]-[IC1])

You only need the aditional IIf statement if you are testing for a 3rd
condition:

field name: = IIf([PT1]=0,[PC1]-[IC1],IIf([PT1]0,[PT1]-[IC1],[XX1]-[IC1]))

Just remember that there are 3 parts: 1) the condition you are testing for
2) value if true 3) value if false false

"Roger Drews" wrote:

I am trying to write an IIf statement to use in a calculation where if PT1 is
zero it will use PC1 to determine the value. Here is the state I have tired
but I cannot get it to work. What am I doing wrong?
IIf([PT1]=0, [PC1]-[IC1], IIf([PT1]0,[PT1]-IC1])

  #13  
Old April 5th, 2010, 06:14 AM posted to microsoft.public.access.tablesdbdesign
John W. Vinson
external usenet poster
 
Posts: 18,261
Default Writing and IIf statement in Access?

On Fri, 2 Apr 2010 19:17:01 -0700, Rhonda
wrote:

I am doing a if then statement in Access with multiple like statements. I
think I am close, but I am getting a circular reference error...

here my query
1YTDDEPRAMT: IIf([ASSETCLASSID] Like
"*-CR",[1YTDDEPRAMT]*-1,IIf([ASSETCLASSID] Like
"*DEPLOYC",[1YTDDEPRAMT]*-1,IIf([ASSETCLASSID] Like
"*BASIC-C",[1YTDDEPRAMT]*-1,[1YTDDEPRAMT])))



You're trying to set the value of 1YTDDEPRAMT based on the value of
1YTDDEPRAMT.

You cannot define a value based on itself.

Use a different fieldname before the colon.

--

John W. Vinson JVinson *at* Wysard Of Info *dot* com
 




Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

vB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Forum Jump


All times are GMT +1. The time now is 10:30 PM.


Powered by vBulletin® Version 3.6.4
Copyright ©2000 - 2024, Jelsoft Enterprises Ltd.
Copyright ©2004-2024 OfficeFrustration.
The comments are property of their posters.