access+training+courses - Calculations (expressions) in queries

Resolved · Low Priority · Version Standard

Calculations (expressions) in queries

Sinéad has attended:
Access Intermediate course
Access Advanced course
Access VBA course

by - delegate Sinéad [15 posts] (2007 May 23 Wed, 10:44) Reply

I have a query with three with three amounts: Ammount 1, 2 & 3. I want to build a calculation in a fourth column that says that if the total from these three is 0, then put the word yes in this 4th column, and if the cumulative total is greater than 0, then put the word No in the 4th column. How do I do this?

Version	Date	Location	Places available	Book	Next place rate:
Access Advanced 1 day course
Version	Date	Location	Places available	Book	Card	Invoice
2003	2008 Dec 3 Wed	Southwark	5	book now	£219	£235
2003	2008 Dec 19 Fri	Bloomsbury	5	book now	£219	£235
2003	2008 Dec 24 Wed	Bayswater	0	FULL
2003	2009 Jan 7 Wed	Southwark	7	book now	£203	£230
2003	2009 Jan 14 Wed	Bloomsbury	8	book now	£187	£224
2003	2009 Jan 23 Fri	Bloomsbury	7	book now	£203	£230
Full Schedule: See all 45 Access Advanced course dates. Bookings currently available until 25th November 2009.

RE: calculations (expressions) in queries

by - trainer Richard platinum contributer [818 posts] (2007 May 24 Thu, 16:42) Reply

Hi

You can use the IIF function to do this.
Add the below to a new field name in your query.

Expr1: IIf([result]>=0,"Yes","No")

You might need to adapt it to your purpose but essentially:

for example, embed another IIF statement in the false answer part of the statement.

Expr1: IIf([result]=0,"Yes",IIf([result]>0,"Yes","No"))

That should work

Regards

Richard

· Microsoft Access, an Efficient Database Tool
· Microsoft Access: Unleash the True Potential Today
· Quick Microsoft Access Course: Creating an Inventory Control Database in 8 Simple Steps
· The Benefits of Certified Microsoft Training in Access
· Microsoft Access 2007: New Features

Access tip:

Change The Default Font in Access 2003

You can change the default font in Access 2003 so that whenever you create a new database your preferred font is automatically set.

To change the default font:

1.From the menu bar select Tools.
2.Click Options.
3.Go to the Datasheet tab:
4.In the Default font section select your preferred font from the list of fonts e.g. Arial.
5.Select your preferred size from the size menu e.g. 12.
6.Click Apply.

Click on the Tables/queries tab:
1.In the Query Design font section select your preferred font from the list of fonts e.g. Arial.
2.Select your preferred size from the size menu e.g. 12.
3.Click Apply.
4.Click OK.

The default font for Access has now been changed.