IIF statment syntax

D

dcozzi

I am trying to use an IIF statment to organize data from rows which have
specific values into 1 colum by using a query.

What is the proper syntax for 5 "ifs" and the colum i want it to return

Example

F1 f2 F3 F4 F5 F6
1 1
2 1
3 1
4 1
5 1

what would the exact syntax look like for a query to return all the 1's in a
single column of output

if FI=1 then F2
if F1=2 then F3
if F1=3 then F4
if F1=4 then F5
if F1=5 then F6

thanks
 
D

dcozzi

Im not really sure how to use teh choose function.

How does it work.

while my example below is rahter simple, my actual table has data in every
column. Will this still work?
 
J

John Vinson

Im not really sure how to use teh choose function.

The first argument of Choose must be an integer. There can be any
(reasonable) number of additional arguments; if the first argument is
1 the function returns the first, if it's 2 it returns the second, if
it's 334 it returns the 334th. E.g.

N = 3
?Choose(N, "A", "B", "C", "D")

will return "C" since that's the third possibility.
How does it work.

while my example below is rahter simple, my actual table has data in every
column. Will this still work?

Yes, just pass it all the columns.

I fully agree that your table design IS SIMPLY WRONG and needs to be
reconsidered.

John W. Vinson[MVP]
 
D

dcozzi

John,

Unfortunetly, the reason i am trying to do what was described, is that the
data i am importing is from a text file where the columns are not lined up
correctly. I am trying create useable data. I do not thing the choose will
work in this case. I am trying to get whatever value is in a specific row
based on a defined value in a standard column. Example, i may have book
values of a bunch of fixed income assets in 1 of 3 columns, and based on an
asset class identifer, return the correct column which has the book value.

Thanks for your help anyway.
 
D

Douglas J Steele

Just because the data you imported isn't normalized doesn't mean you can't
normalize it in your database.

Even if you can't be bothered creating properly normalized tables, you can
create a query that normalizes the data:

SELECT F1, "F2" As FieldName, F2
FROM MyTable
UNION
SELECT F1, "F3" As FieldName, F3
FROM MyTable
UNION
SELECT F1, "F4" As FieldName, F4
FROM MyTable
UNION
SELECT F1, "F5" As FieldName, F5
FROM MyTable

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)
 
D

dcozzi

Normalizing the data via query is exactly what i am trying to do. Currently i
use a string of IIF statments.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top