• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 361
  • Last Modified:

'If then else' in MS Access

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
optimumreports
Asked:
optimumreports
  • 2
  • 2
1 Solution
 
Guy Hengel [angelIII / a3]Billing EngineerCommented:
this should do:
  	

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

Open in new window

0
 
optimumreportsAuthor Commented:
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
 
MINDSUPERBCommented:
Tom,

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

Just a thought of using IIF than IF.

Ed
0
 
optimumreportsAuthor Commented:
Works perfect - thanks!

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

Thanks again,

Tom
0
 
MINDSUPERBCommented:
As what I know, IF is used in Excel and IIF is in Access.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

  • 2
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now