IFs ANDs ORs

[maeling]

My first question is how do you say or in an IIF statement. If you want the statement to return from two fields with different data. For example

Month year date part
decemeber 2003 2003
january 2004 2004

I would like my statement to return both these values howver my statement doesn't work.

Year: IIf([year]=[date part],IIf([close month]="december","2004","2004&quot)

I was expecting to have another column with 2004 for both records - can anybody help ?????? Please

 

[SQLBI]

Hi,

Your syntax should be

IIF(OR(1st Test,2nd Test),IfTrue,IfFalse)

So your code will be something like this

[code]IIf(OR([year]=[date part],IIf([close month]="december"),"2004","2004")

I'm not sure why you need IIF(Or()) though as your return values above are both the same.

Hopefully this will point you in the right direction.




Leigh Moore
Solutions 4 MS Office Ltd

http://www.solutions4msoffice.co.uk

 

[maeling]

Hi Leigh,
thanks for your prompt reply, however it doesn't like the syntax and is highlighting the OR - I am using Access 2000 if that has any bearing.

If I could shed some more light on the nature of my query maybe you could advise me further. I have an accounting period that runs Decemeber2003 to Novemeber2004. This period is known as 2004. If I want to report on this table and use the date field as the parameter and enter 2004 I obviously won't get December - The statement I have enquired about hopefully will created me a column that includes December 2003. However a further complication is I don't want December 2004 because that month belongs to 2005. Is this very clear ------as mud.

 

[dhookom]

Your expression:
Year: IIf([year]=[date part],IIf([close month]="december","2004","2004&quot)
won't work since you are aliasing your column with the name of an existing column. I think all you have to do is add a month to your date field and then find the year. This is made much harder since you are saving your month as text and not as the month number. If you want to store "December", you should be storing this as either a date field #12/1/2003# or as an integer 12. This saves lots of work later, especially if you are spelling November like "Novemeber".

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[maeling]

Thanks dhookom for your observations however the above is just an example. Firstly the alias is different in the database - secondly the month is stored in a numeric format however I thought it would be harder to explain within the forum - thirdly Novemeber was just a typo. You sound like a very interesting person ! Any suggestions or advise on the question in hand ?

 

[dhookom]

Please provide:
-Actual sample data/records
-Actual field/table names
-Actual intended results



Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[maeling]

Do you have an Email ?

 

[dhookom]

Yes, I do have an email address. I don't usually provide it in forums unless absolutely necessary. I would rather you answered questions here in the forum where other members can assist or learn.

Duane
MS Access MVP
Find out how to get great answers faq219-2884.

 

[JonFer]

If your fields are dates, you can adjust them by 1 month and then check the year in the Where clause:

Where Year(DateAdd("m",1,AcctDate))=[Enter Accounting Year]

This will move December 2003 dates to Jan 2004 so they'll be included and move December 2004 dates to Jan 2005 so they'll be excluded.

 

[maeling]

jonfer,
this sounds like it would do the job howver I am unsure how and where to use the where clause in my query

 

[JonFer]

Enter it in the query design window:

Year(DateAdd("m",1,AcctDate)) -> this is the field name

[Enter Accounting Year] -> put this on the criteria line