Solved

'If then else' in MS Access

Posted on 2010-09-14
5
320 Views
Last Modified: 2013-11-27
Hi,

I have a query in Access (2007) and I want to build an 'If then else' statement. The code below is what I am currently using (without the proposed new statement) but here is the logic that I want it to do:

1) Before the code attached is used I want the statement to check to see if '[AttendanceCalc]![SessionsPossible]' = 0.

2) if '[AttendanceCalc]![SessionsPossible]' does = 0 then return 0 in the query results (currently it return '#error').

3) else do the calculation attached.

Can anyone help with this please?

Thanks,

Tom
%Att: Round(([AttendanceCalc]![SessionsAttended]/[AttendanceCalc]![SessionsPossible])*100,0)

Open in new window

0
Comment
Question by:optimumreports
  • 2
  • 2
5 Comments
 
LVL 142

Expert Comment

by:Guy Hengel [angelIII / a3]
ID: 33669622
this should do:
  	

%Att: IF([AttendanceCalc]![SessionsPossible] = 0; 0; Round(([AttendanceCalc]![SessionsAttended]/[AttendanceCalc]![SessionsPossible])*100,0))

Open in new window

0
 
LVL 1

Author Comment

by:optimumreports
ID: 33669676
Hi,

Thanks for the pronpt relpy. I get the following error when I tried the code:

"The expression you entered contains invalid syntax.

You omitted an operand or operator, you entered an invalid character or comma, or you entered text without surrounding in quotation marks".

I will try and fix this but - do you have know where the problem is straight away that would be great.

Thanks,

Tom
0
 
LVL 19

Accepted Solution

by:
MINDSUPERB earned 500 total points
ID: 33669678
Tom,

%Att: IIF([AttendanceCalc]![SessionsPossible] = 0; 0; Round(([AttendanceCalc]![SessionsAttended]/[AttendanceCalc]![SessionsPossible])*100,0))

Just a thought of using IIF than IF.

Ed
0
 
LVL 1

Author Closing Comment

by:optimumreports
ID: 33669730
Works perfect - thanks!

For future reference - what is the difference between 'IF' and 'IFF'?

Thanks again,

Tom
0
 
LVL 19

Expert Comment

by:MINDSUPERB
ID: 33669765
As what I know, IF is used in Excel and IIF is in Access.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
How can you open the FORM2 2 31
Search for text in a .txt file 14 44
Access report groups with sums 5 26
unable to create table-based data macro in MS ACCESS 2013 11 25
I see at least one EE question a week that pertains to using temporary tables in MS Access.  But surprisingly, I was unable to find a single article devoted solely to this topic. I don’t intend to describe all of the uses of temporary tables in t…
I'm trying, I really am. But I've seen so many wrong approaches involving date(time) boundaries I despair about my inability to explain it. I've seen quite a few recently that define a non-leap year as 364 days, or 366 days and the list goes on. …
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

770 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question