473,320 Members | 1,879 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

Iff Statement Help

Hello everyone, new to this site, I just wanted to thank you for reading my question. I have a database that I built that has all the records being used by the Date Completed which is the Date of the task being completed. The thing I want to do is have the first 6 months and last 6 months by the date completed. I wrote a query that grabs the months from my field date completed, like Format([DateCompleted],'mmmm') and from their I wanted to change this to show the first 6 months or last 6 months. Like Jan - June and July - December. The thing I tried is using an Iff statement and I can't get it to work, any help would be great appreciated. Here is the iif state I wrote.

Date: IIf([Month]="January" And " February" And "March" And "April" And "May" And "June","First 6 Months","Last 6 Months")

BTW, I tried using "Or" instead of "And" and it didn't work.

Thank you
Feb 9 '10 #1

✓ answered by Delerna

month cannot be different values at the same time so you must use OR
A month cannot be both "January" AND "February" at the same time
so if you said that in an iif it would always evaluate to false.
But a month can be "January" or "February" or "something else"

You also need to specify month for each OR
iif Month=this OR Month=this

AND and OR are boolean mathematical operations
0 or 0 = 0
1 or 0 = 1
0 and 0 = 0
1 and 0 = 0
1 and 1 = 1

Its difficult to explain because you are using words but the way you had it is like saying
iif month= 1 + 2 + 3
which equates to iif Month=6
and not iif month=1 or month=2 or month=3

This should work
Expand|Select|Wrap|Line Numbers
  1. Date: IIf([Month]="January" or [Month]= "February" or [Month]="March" or [Month]="April" or [Month]="May" or [Month]= "June","First 6 Months","Last 6 Months")
  2.  

Alternativey you could have left the months as numbers instead of names
That way you could have said this
Expand|Select|Wrap|Line Numbers
  1. Date: iif(Month < 7 ,"First 6 Months","Last 6 Months")
  2.  

6 2073
Delerna
1,134 Expert 1GB
month cannot be different values at the same time so you must use OR
A month cannot be both "January" AND "February" at the same time
so if you said that in an iif it would always evaluate to false.
But a month can be "January" or "February" or "something else"

You also need to specify month for each OR
iif Month=this OR Month=this

AND and OR are boolean mathematical operations
0 or 0 = 0
1 or 0 = 1
0 and 0 = 0
1 and 0 = 0
1 and 1 = 1

Its difficult to explain because you are using words but the way you had it is like saying
iif month= 1 + 2 + 3
which equates to iif Month=6
and not iif month=1 or month=2 or month=3

This should work
Expand|Select|Wrap|Line Numbers
  1. Date: IIf([Month]="January" or [Month]= "February" or [Month]="March" or [Month]="April" or [Month]="May" or [Month]= "June","First 6 Months","Last 6 Months")
  2.  

Alternativey you could have left the months as numbers instead of names
That way you could have said this
Expand|Select|Wrap|Line Numbers
  1. Date: iif(Month < 7 ,"First 6 Months","Last 6 Months")
  2.  
Feb 9 '10 #2
nico5038
3,080 Expert 2GB
When your data can hold multiple years, the year needs to be appended to the First/Last string, else calculated values will be wrong....

Nic;o)
Feb 9 '10 #3
Delerna
1,134 Expert 1GB
good point nico........
Feb 10 '10 #4
Thank you Delerna, the first expression you wrote worked perfectly, the second didn't because when it got to 10, 11 and 12, it acted like they were part of the first 6 months, the other worked to perfection. Also Nico, I actually have a query after this one which grabs the year of each "Date Completed" to either the "First or Second" 6 months, so I will be able to run my reports through the years. Thank you guys, you guys were a tremendous help!
Feb 10 '10 #5
gershwyn
122 100+
Make sure that you are comparing numbers, and not strings. The string "12" is less than the string "7" since it's comparing the first character of each. How are you generating the month value? The format function returns a string, but you can grab the integer value of the month by using Month([DateCompleted]).
Feb 10 '10 #6
Actually, I was grabbing the month value by using Format([DateCompleted],'m') I didn't know that using Month([DateCompleted]) brought back an integer vs a string. Thanks a lot!
Feb 10 '10 #7

Sign in to post your reply or Sign up for a free account.

Similar topics

3
by: Robert Mark Bram | last post by:
Hi All! I have the following two methods in an asp/jscript page - my problem is that without the update statement there is no error, but with the update statement I get the following error: ...
4
by: James E Koehler | last post by:
I can't get the WHILE statement to work in MySQL. The version of MySQL that I am using is: Ver 12.16 Distrib 4.0.6-gamma, for Win95/Win98 (i32) running on Windows MX. Here is the relevant...
2
by: Little PussyCat | last post by:
Hello, I hope you can help me. We have a SQL Server 2000 database at work, (which works with a VB6 frontend) which grew to a considerable size, so one of my past colleagues sent me this...
5
by: WindAndWaves | last post by:
Hi Team The function below searches all the tables in a database. However, if subsearch = true then it searches all the objects listed in a recordset (which are all table names). I thought to...
11
by: Scott C. Reynolds | last post by:
In VB6 you could do a SELECT CASE that would evaluate each case for truth and execute those statements, such as: SELECT CASE True case x > y: dosomestuff() case x = 5: dosomestuff() case y >...
10
by: John Smith | last post by:
Can you do a Select Statement within a Select Statement? I want to build a query similar to queries built in Access which link to other queries but using only SQL Statements. Is it possible? If...
6
by: FayeC | last post by:
I really need help figuring this out. i have a db with mostly text fields but 2. The user_id field is an autonumber (key) and the user_newsletter is a number (1 and 0) field meaning 1 yes the ...
4
by: Jack | last post by:
Hi, I have a asp page where part of the code is as follows. This builds up the sql statement partially. sql01 = "UPDATE EquipmentTbl SET " sql01 = sql01 & "SerialNumber = '" &...
7
by: Steven Bethard | last post by:
I've updated PEP 359 with a bunch of the recent suggestions. The patch is available at: http://bugs.python.org/1472459 and I've pasted the full text below. I've tried to be more explicit about...
18
by: dspfun | last post by:
Hi! The words "expression" and "statement" are often used in C99 and C- textbooks, however, I am not sure of the clear defintion of these words with respect to C. Can somebody provide a sharp...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
0
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.