Access - if function

  Gaz W 01:10 08 Dec 2003
Locked

I have a query in Access made from a single table, which is fairly straightforard. This query displays every field in the table. I want to create a "virtual field" which uses the IF function. It needs to display, in this virtual field, the contents of another field IF the contents is greater than 39 (i.e. 40+), BUT if it is less than 40 (39 and below) it must display a zero, not the value of the field.

This is Access XP. I've tried Help but it's very confusing and never gives you exactly the answer you're looking for!

Any help will be much appreciated.

Gaz

  LeadingMNMs 07:38 08 Dec 2003

The basics of the If function in Access 2000 is something like this:

=IIf([Field1]>=40,[Field1],"0")

The IF statement in access is IIf. PLease replace Field1 with the correct field name, keeping the [ ]. The inequality maybe the wrong way round, not sure.

Hope this helps.

  AndyJ 08:43 08 Dec 2003

LeadingMNMs....spot on !

  Gaz W 10:18 08 Dec 2003

Thanks very much. As AndyJ says it was spot on, except I had toi change it slightly because I found I needed to involve 3 cells, so now it's something more like this:

=IIf([Field2]>=40,[Field1],"0")

The main thing is it works.

Thanks very much for your help,

Gaz

This thread is now locked and can not be replied to.

Elsewhere on IDG sites

HP Envy x2 review: Hands-on

How Sketch and InVision have revolutionised our design workflow

The best tech gifts for Christmas 2017

Les meilleurs jeux de société (2017)