how do I avoid "expression too complex" error with nested IIf?

Thanks a lot, the method of creating a table based on the query grid works.
Thank you very much for your support. I really appreciate it.


"Dale Fye" wrote:

I don't know what all of those fields are, but my guess is that you don't
need all of them to define a unique record within your table. My quess is
that you could get by with SPNAME, TDATE, PAYEE, and REF; but I'd have to
look at your data to be sure.

First, I would try writing a query that joins your original table to the
normalized query. Add the table and the query grid to your query grid, join
them on all of the fields that you listed in your note below, except the
TRANS_CATEGORY field, add the TRANS_CATEGORY field from "yourTable" to the
grid, change the query type to Update, and in the "update to" row, enter
[queryname].[Trans_Category]. Try to run the query. If it works, great. If
it doesn't, it will probably tell you the query is not updateable.

In that case, you will need to use the DLOOKUP( ) function. If you do
this, the normalization query will get run over and over again. What I
would do is create another query that uses the normalization query and
creates a temporary table. Once you have done this, you might want to try
the previous step again (updating from a temp table may work where it didn't
work when you tried to update from a query).

If that doesn't work, then you will have to use the DLOOKUP function. It
would be easier to show you what that would look like if you can provide the
data types of each of the columns you listed below (or at least the key ones
if done some more analysis based on my note above).

Dale
--
Email address is not valid.
Please reply to newsgroup only.


"Yamou" wrote:

Thanks Dale,

I am almost done.

It works with and I get the the TCATEGORY Field Updated with the right data.
Now I am not sure how to join the tow tables; I have not done of these in
quite a while.

At this point I have

Normalization query results with the following fields: PNAME,
SPNAME,TDATE,PAYEE,REF,EXP,BBAL,CBAL,TCATEGORY

The table to which I want to join the normalization query has the exact same
fields and more, with the TCATEGORY field to update

I tried the Update query you suggested, but because I have more than one key
field (8), I didn't know how to write the last argument like "ID ="& ID in
your example.

Once I figure out how to transfer the data from the Normalization query to
my REPIMPORT table, I will be done.

Thanks for your patience




"Dale Fye" wrote:

The ID field I put in my query should be replaced by the name of the primary
key field in your table. If your primary key contains more than one field,
then you should list all of the fields that are in your primary key. When
you save this query the query should not be saved as a "pass through" query.
The image to the left of the query should actually look like two
interlocking rings, sort of like the Olympic rings, but with only two.

"Yamou" wrote in message
...
I have tried that, but having no experience with "normalizing" queries i
get
a parameter popup asking for a value for ID, what should the ID field
match?
I suppose some sort of unique record (key) field.

Also, when I use this expression, the query appears as a passthrough
query;
i suppose that should be the case.


"Dale Fye" wrote:

I think what you need to do to start with is create a "normalizing"
query.
It would look something like:

SELECT ID, "Salaries & Benefits" as Trans_Category
FROM yourTable WHERE NOT ISNULL([SAL_BEN])

UNION ALL

SELECT ID, "Tuition" as Trans_Category
FROM yourTable WHERE NOT ISNULL([TUITION])

UNION ALL

SELECT ID, "Supplies & Other" as Trans_Category
FROM yourTable WHERE NOT ISNULL([SUPPL_OTHER])

... (for all of your 18 fields).

Once you have done that, you should be able to join that to your other
table
and update the [Trans_Category] field. If that doesn't work, you could
do a
DLOOKUP( ) pointed at this new "normalization query" for each record

UPDATE yourTable
SET [TRANS_CATEGORY] = DLOOKUP("Trans_Category", "Normalization_Query",
"ID
= " & ID)

HTH
Dale

--
Email address is not valid.
Please reply to newsgroup only.


"Yamou" wrote:

I am using an update query with a single table comprised of text, date
and
number fields (the fields to query are all number fields and the
destination
field [TRANS_CATEGORY]). Now these are transactions that get posted
first in
a field called [TRANS_AMOUNT] and then to their respective category -
one of
the 18 fields in question (education, health, office expense, transp,
etc.)

Purpose/idea: to update the [TRANS_CATEGORY] field with the right label
depending on whether a record was posted in that category (i.e. the
single
non null record in a row where an amount is posted).

I use the following IIF construction:

UPDATE REPIMPORT SET REPIMPORT.TRANS_CATEGORY = IIf([SAL_BEN] Like
"*","Salaries & Benefits",IIf([TUITION] Like
"*","Tuition",IIf([SUPPL_OTHER]
Like "*","Supplies & Other",IIf([HEALTH] Like
"*","Health",IIf([NUTRITION]
Like "*","Nutrition",IIf([HEALTH] Like "*","Health",IIf([CLOTHING] Like
"*","Clothing",IIf([RECREATION] Like "*","Recreation",IIf([CHRIS_FORM]
Like
"*","Christian Formation",IIf([HELP_TO_FAM] Like "*","Help to
Families",IIf([SPEC_ GIFTS] Like "*","Special Gifts",IIf([OTHER_BEN]
Like
"*","Other Benefits",IIf([BEN_EXPL] Like "*","Benefits
Explanation",IIf([SALARIES] Like "*","Salaries",IIf([EMPLOY_BEN] Like
"*","Employee Benefits",IIf([PHOTOS] Like "*","Photos",IIf([OFFICE_EXP]
Like
"*","Office Expense"IIf([TRANSP] Like
"*","Transportation",IIf([OTHER_EXP]
Like "*","Other Expenses","" )))))))))))))))))));

If I keep all the above fields in the expression, I get the message:
"the
expression you entered is too complex". If, however, I truncate the
expression down to certain length, say up to [OTHER_BEN], then It
returns the
right label (Salaries and Benefits to Other Benefits) in
[TRANS_CATEGORY],
and nothing in all others (if I specify "" in the last If statement).

I want to go beyond this limitation from the first category (salaries &
Benefits) all the way to the last category (Other expenses). Maybe the
if
Function like in excel has a n number of nesting possible or should I
take
another approach to be able include all my parameters in the query?

Please advise and thanks a lot!


"Dale Fye" wrote:

Need a few more details if we are going to give you any meaningful
help.

What are the tables and fields in your query, what does your IIF( )
statement look like now? What are you actually trying to accomplish?

Dale
--
Email address is not valid.
Please reply to newsgroup only.


"Yamou" wrote:

I have about 20 parameters and can't seem to fit them all in one
nested iif
expression. Is there a way to create an expression that would cycle
through
those twenty (field) parameters?