CODEWITHSUNDEEP
ms-accessvbacalculated-columns
user776676
user776676

Reputation: 4385

Microsoft Access: How to do IF in Calculated Field Expression?

I want my calculated field to have a conditional value like this: if fieldA is 0 then set fieldC to 0 else field C = fieldB / fieldA

How do I do this? Thanks.

And while you are at it, how do I do this in an Access VBA routine so that I can keep on revising my conditions.

Upvotes: 2

Views: 6056

Answers (2)

bracho monacho
bracho monacho

Reputation: 181

You can add a procedure like this to your module code... 

Public Function CalculateFieldC(ByVal fieldA as Long, ByVal fieldB as Long) as Single
  If fieldA = 0 Then
    CalculateFieldC = 0
  Else: CalculateFieldC = fieldB / fieldA
  End If
End Function

Then call it from an SQL statement; for example:

SELECT fieldA, fieldB, CaculateFieldC(fieldA, fieldB) AS fieldC
FROM aTableWithFieldAandFieldB;

Upvotes: 2

NGLN
NGLN

Reputation: 43659

In a table definition: not possible.

As a query:

SELECT
  IIF(FieldA = 0; 0; FieldB / FieldA) AS FieldC
FROM
  ATableWithFieldAAndFieldB

As SQL text in VBA: replace the ; with ,

Upvotes: 2

Related Questions