Wrox Programmer Forums
Access Discussion of Microsoft Access database design and programming. See also the forums for Access ASP and Access VBA.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Access section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
Old October 9th, 2003, 10:03 AM
Authorized User
Join Date: Oct 2003
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts
Default Tricky SQL

In a table, Field2 can contain 1 of 2 values. Depending on what value is in Field2, the value in Field3 needs to be placed in a different window in my program. So, using a SQL statement, I would like to add two "virtual" fields to the recordset that is returned from the database. The two new fields would contain the value from Field3 depending on what is in Field2.

For example, if the database looks like this:

Field1 Field2 Field3
test A 65
test2 B 73
test3 A 45
test4 A 87
test5 B 34

I want the recordset returned to look like the following, with FieldA and FieldB representing the "virtual" fields that don't actually exist in database but their contents depend on what is in Field2 and Field3:

Field1 Field2 Field3 FieldA FieldB
test A 65 65
test2 B 73 73
test3 A 45 45
test4 A 87 87
test5 B 34 34

How would I structure a SQL statement to produce this recordset? Thanks!
Old October 9th, 2003, 02:19 PM
sal sal is offline
Friend of Wrox
Join Date: Oct 2003
Posts: 702
Thanks: 0
Thanked 0 Times in 0 Posts

Use the IIf («expr», «truepart», «falsepart») function.

SELECT Table.Field1,
IIf([Field2]="A",[Field3],"") AS FieldA,
IIf([Field2]="B",[Field3],"") AS FieldB
FROM [Table];

Assuming that your table name is Table. Use the IIF statement. This statement evaluates an expression on the first argument. After evaluating it you use the second argument to tell it what to do if it comes back as true, if it comes back as false you tell it what you want it to do on the third argument.

Hope it helps.

Old October 10th, 2003, 10:28 AM
Authorized User
Join Date: Oct 2003
Posts: 21
Thanks: 0
Thanked 0 Times in 0 Posts

That worked! Thanks!

Similar Threads
Thread Thread Starter Forum Replies Last Post
Tricky logic (for me) cedwards Dreamweaver (all versions) 5 April 5th, 2006 04:47 PM
Tricky Query BSkelding MySQL 5 August 31st, 2005 08:59 AM
Tricky asp/sql problem cedwards Dreamweaver (all versions) 8 June 13th, 2005 04:02 PM
Alright, here's a tricky one... Anubis Access VBA 14 November 19th, 2003 04:22 PM
Very tricky challenge 12th_Man SQL Language 3 June 20th, 2003 06:45 PM

Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.