help with Elseif or IIF

G

Guest

I have a database which I am writing Crystal Reports off. There are 3 or 4
tables with about 300,000 rows in each. Each table has the following key
fields:
Fund
Center
Account
Balance

I have written a query to create a unique field for each record
FCA which is Fund+Center+Account

I also need to create a table which assigns a row for each record in the
reports so I can group off them, ie Property taxes, Local sales taxes,
Licenses and permits and so forth. There are about 100 different assignments
in each table. The if then statements begin simple but can get complex for
of the assignments. I was doing this with a make query table with the new
field called "Combined Governmental Reports". I had it working with the IIF
command but after about 6 or 7 passes it wouldn't take any more statements.

It looked like this:

IIF ([Modified accrual balances with FCA]![Center} like "Z*"," ",
IIF ([Modified accrual balances with FCA]![Account] Like "411*","Property
taxes",
IIF ([Modified accrual balances with FCA]![Account] Like "412*","Local sales
taxes",
IIF ([Modified accrual balances with FCA]![Account] Between "413000" and
"414999","Licenses and permits"," "))))

I am guessing that I need to write this in visual Basic to get all the
statements in as well as having someone maintain it easily. About a year
ago, I was able to write quite a bit of Visual Basic code in reports and
forms (with MUCH help from this board) however I need help getting started
writing it in this query.

Can anyone spare a few moments to get me started or show me where I am
heading in the wrong direction? I'd sure appreciate it!

Thanks,

Meg
 
M

MGFoster

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It sounds more like a DB design problem. You should have a table of
Accounts that shows the Account Number and the Account Type. E.g.:

AccountNo AccountType
--------- -----------
411 Property Taxes
412 Local Sales Taxes

And probably a SubAccount table to show subaccounts, since you have
account number ranges in your IIf() functions:

AccountNo SubAccountNo SubAccountType
--------- ------------ -------------------------
411 001 Commercial Property Taxes
411 002 Residential Property Taxes
412 001 Building Materials Taxes
412 002 Food Taxes

etc.

Then you could join to these tables in your query to get the values of
the AccountType depending on the AccountNo/SubAccountNo.

=====

If you can't rearrange your DB's design, try using the Switch() function
instead of multiple IIf() functions. E.g.:

Switch ([Modified accrual balances with FCA].
Like "Z*', " ",
[Modified accrual balances with FCA].[Account] Like "411*",
Property taxes",
[Modified accrual balances with FCA].[Account] Like "412*",
"Local sales taxes",
.... etc. ... )

Use periods between table names and column names - not the exclamation
point!

You could also alias your table names and make the formula much more
readable:

Switch (M.
Like "Z*', " ",
M.[Account] Like "411*", Property taxes",
M.[Account] Like "412*", "Local sales taxes",
... etc. ...
)

Read the VBA Help for the Switch() function for more info.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQm6qJoechKqOuFEgEQIajACfa29hfdLcYvGGij43gmRAb8ig0/AAoKnq
goXJUNhEfrgpDbkDy6/qBLmR
=NEl+
-----END PGP SIGNATURE-----

I have a database which I am writing Crystal Reports off. There are 3 or 4
tables with about 300,000 rows in each. Each table has the following key
fields:
Fund
Center
Account
Balance

I have written a query to create a unique field for each record
FCA which is Fund+Center+Account

I also need to create a table which assigns a row for each record in the
reports so I can group off them, ie Property taxes, Local sales taxes,
Licenses and permits and so forth. There are about 100 different assignments
in each table. The if then statements begin simple but can get complex for
of the assignments. I was doing this with a make query table with the new
field called "Combined Governmental Reports". I had it working with the IIF
command but after about 6 or 7 passes it wouldn't take any more statements.

It looked like this:

IIF ([Modified accrual balances with FCA]![Center} like "Z*"," ",
IIF ([Modified accrual balances with FCA]![Account] Like "411*","Property
taxes",
IIF ([Modified accrual balances with FCA]![Account] Like "412*","Local sales
taxes",
IIF ([Modified accrual balances with FCA]![Account] Between "413000" and
"414999","Licenses and permits"," "))))

I am guessing that I need to write this in visual Basic to get all the
statements in as well as having someone maintain it easily. About a year
ago, I was able to write quite a bit of Visual Basic code in reports and
forms (with MUCH help from this board) however I need help getting started
writing it in this query.

Can anyone spare a few moments to get me started or show me where I am
heading in the wrong direction? I'd sure appreciate it!
Click to expand...
 
G

Guest

thanks for responding. I do have a table with the Account Number, Account
type. Problem is that it is over 300000 records and has to be recreated
everytime the reports are run to incorporate new accounts set up. And to top
it off, there are three tables that size it has to be created for because
there are three different sets of accounting books with different accounts in
each dependent on the type of books they are. So I'm trying to
programatically create the table you mentioned so that others can run the
reports on a quarterly basis. I'm just a contractor here to write the
reports. They just didn't realize when they set up the contract that they'd
have to use access as well as Crystal.

I will look more into switch to see if it will work. Thanks.

Meg

MGFoster said:
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

It sounds more like a DB design problem. You should have a table of
Accounts that shows the Account Number and the Account Type. E.g.:

AccountNo AccountType
--------- -----------
411 Property Taxes
412 Local Sales Taxes

And probably a SubAccount table to show subaccounts, since you have
account number ranges in your IIf() functions:

AccountNo SubAccountNo SubAccountType
--------- ------------ -------------------------
411 001 Commercial Property Taxes
411 002 Residential Property Taxes
412 001 Building Materials Taxes
412 002 Food Taxes

etc.

Then you could join to these tables in your query to get the values of
the AccountType depending on the AccountNo/SubAccountNo.

=====

If you can't rearrange your DB's design, try using the Switch() function
instead of multiple IIf() functions. E.g.:

Switch ([Modified accrual balances with FCA].
Like "Z*', " ",
[Modified accrual balances with FCA].[Account] Like "411*",
Property taxes",
[Modified accrual balances with FCA].[Account] Like "412*",
"Local sales taxes",
.... etc. ... )

Use periods between table names and column names - not the exclamation
point!

You could also alias your table names and make the formula much more
readable:

Switch (M.
Like "Z*', " ",
M.[Account] Like "411*", Property taxes",
M.[Account] Like "412*", "Local sales taxes",
... etc. ...
)

Read the VBA Help for the Switch() function for more info.
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQm6qJoechKqOuFEgEQIajACfa29hfdLcYvGGij43gmRAb8ig0/AAoKnq
goXJUNhEfrgpDbkDy6/qBLmR
=NEl+
-----END PGP SIGNATURE-----

I have a database which I am writing Crystal Reports off. There are 3 or 4
tables with about 300,000 rows in each. Each table has the following key
fields:
Fund
Center
Account
Balance

I have written a query to create a unique field for each record
FCA which is Fund+Center+Account

I also need to create a table which assigns a row for each record in the
reports so I can group off them, ie Property taxes, Local sales taxes,
Licenses and permits and so forth. There are about 100 different assignments
in each table. The if then statements begin simple but can get complex for
of the assignments. I was doing this with a make query table with the new
field called "Combined Governmental Reports". I had it working with the IIF
command but after about 6 or 7 passes it wouldn't take any more statements.

It looked like this:

IIF ([Modified accrual balances with FCA]![Center} like "Z*"," ",
IIF ([Modified accrual balances with FCA]![Account] Like "411*","Property
taxes",
IIF ([Modified accrual balances with FCA]![Account] Like "412*","Local sales
taxes",
IIF ([Modified accrual balances with FCA]![Account] Between "413000" and
"414999","Licenses and permits"," "))))

I am guessing that I need to write this in visual Basic to get all the
statements in as well as having someone maintain it easily. About a year
ago, I was able to write quite a bit of Visual Basic code in reports and
forms (with MUCH help from this board) however I need help getting started
writing it in this query.

Can anyone spare a few moments to get me started or show me where I am
heading in the wrong direction? I'd sure appreciate it!
Click to expand...
Click to expand...
​
 

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

Similar Threads

Need help with Elseif statement 3
Please help with Elseif in Query 2
Between date with greater than or eaqual to 4
Trouble with queries 2
Can I use OR in the THEN or ELSE portion of an IIF statement? 2
Help needed with IIF in my query 14
Multiple Yes/No fields + IIF 2
Help with IIf in a query 2

Top