Forum Home
Press F1
 
Thread ID: 88743 2008-04-07 23:34:00 MS Access Query Mercury (1316) Press F1
Post ID Timestamp Content User
656874 2008-04-07 23:34:00 I am setting up a database for a race draw. It involves cars that run twice - once in the left lane, once in the right.

Currently the drivers are assigned a colour - blue or red (first race blue drivers run in the left lane, second they run in the right).

Does anyone know how to change "blue" to read "left" on the first race printout and to "right" on the second (and the opposite with red)? 		Mercury (1316)
656875 2008-04-08 03:02:00 I am setting up a database for a race draw. It involves cars that run twice - once in the left lane, once in the right.

Currently the drivers are assigned a colour - blue or red (first race blue drivers run in the left lane, second they run in the right).

Does anyone know how to change "blue" to read "left" on the first race printout and to "right" on the second (and the opposite with red)?

Hi Mercury,

Do do this, you need to evaluate the colour and race number with an IIF statement. In the following example, I made a Table with 4 fields:

Car
Colour
Race 1
Race 2

The query contains an Expression called 'Lane' that looks at the colour and the race 1 field to decide on the correct colour for each car:


SELECT Table1.Car, Table1.Colour, Table1.[Race 1], Table1.[Race 2], IIf([Colour]="Blue",IIf([Race 1] Is Not Null,"Right","Left"),IIf([Race 1] Is Not Null,"Left","Right")) AS Lane
FROM Table1;

Let me know if this makes sense - it seemed to work to me, based on the logic you give above.

:) 		nofam (9009)
656876 2008-04-08 07:59:00 Many thanks for that. A couple of minutes fiddling and I have the answer (spent far longer this morning not getting it right!)

The temporary answer, for the way I've set it up, is:

Track: IIf([LID]=1,"Right","Left")

Then on the other query for the second draw I'll do the opposite.

I knew the answer was an IF in Excel but it is so long since I used Access that I have forgotten far too much - including how to use functions. 		Mercury (1316)
1