IIF statement

[jagsdj]

Hello all...

My first post and I'm coming from a SQL Server 2000 background. So, please be kind...

In my select statement I'm using the IIF statement to reference some codes, but I seem to keep getting an error saying that the Expression is too complex in the query expression. Is there anyway of referencing a lookup table and extracting those codes based on a criteria??

For instance my sql statement looks like this:
select [or].one, [or].two, iif([or].three = 'a', 'hey', iif([or].three = 'b', 'bee')) As expr8 (i have about 30 of these comparisons...Is there any other way then to put this IIF directly into a sql statement?? Thanks so much

 

[mdthornton]

There is obviously some limit in play here. I've had something similar when joining loads of tables but not so far in an expression. Anyway, could you not just make another table:

key value
a hey
b bee
etc

and join to this in your query? Best Regards,
Mike

 

[jagsdj]

Yes, I could make another table -- how would I go about joining the tables?? Through some link of some sort?

 

[mdthornton]

Have a play with the query design. Add the new look up table (right click and do "show table&quot. Then join column "three" to the key column. If you need to do an outer join double click on the link to specify the direction (ie you want all rows from "or&quot. Best Regards,
Mike

 

[LittleSmudge]

I don't think that will work because .. eerm?

I'm going to assume that this Select statement is in VBA code in a module somewhere. If it isn't then what I'm writing might not be relevant

The Select statement in in the form of a text string so the iif will be seen as literal characters instead of being evaluated.

So you need to get the iif out of the string like this

" SELECT [or].one, [or].two, " & iif([or].three = 'a', 'hey', iif([or].three = 'b', 'bee')) & " As expr8"

I don't think this is what you want either because it will be evaluated as

" SELECT [or].one, [or].two, hey As expr8" iif .three = 'a'
or
" SELECT [or].one, [or].two, bee As expr8" iif .three = 'b'

but hopelfully this will put you on the right track.

ALTERNATIVELY try this approach
Dim strSQL as string
strSQL = " SELECT [or].one, [or].two, "
IF [or].three = 'a' THEN
strSQL = strSQL & TheNextBitOption1
ELSEIF [or].three = 'b' THEN
strSQL = strSQL & TheNextBitOption2
ENDIF
strSQL = strSQL & " As expr8 "
.. ..
etc.


G LS

 

[jagsdj]

thanks for the responses...there appreciated!!

Could you be kind enough to point me in the right direction of where to learn more about VBA modules!?

thanks

 

[jagsdj]

Lets try this again...I don't think I was totally clear...my brain is almost dead, so please bear with me!

I have a table(table1) containing 10 fields(sector, case#, date, time, IBRCode, Address, suffix, direction, city, state). I also have another table called table2 containing 2 fields (UCRCode and IBRCode). Now, what I would like is to obtain the UCRCode for the matching IBRCode in table1...my select looks something like this...

select table1.sector, table1.case#, table1.date, etc.., {what would go here so as that I could get the UCRCode out of table2?}...

thanks and I apologize for the confusion
as always, all help is greatly appreciated!

jags
jagsdj@hotmail.com

 

[LittleSmudge]

Oh, right IIF is NOT the way to go then

"SELECT table1.sector, table1.case#, table1.date, etc.., table2.UCRCode " _
"FROM table1 LEFT JOIN table2 " _
"ON table1.IBRCode = table2.IBRCode;"



( Depending on what you want in the resulting record set you might want INNER JOIN instead of LEFT JOIN. )



G LS