Need help with Elseif statement

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I need help with an Elseif statement.

Table Name: Modified Report Assignments

Fields:

Fund
Center
Account
Balance
FCA
Combined Governmental Reports


I need to write some code to populate the "Combined Government Reports"
fields based on the record in either the account or center field. Can
someone please help me get started writing an Elseif statement for the first
few lines of this Iif statement. I can't use Iif because I will have about
75 lines of code.

IIF ([Modified Report Assignments]![Account] Like "411*","Property
taxes",
IIF ([Modified Report Assignments]![Account] Like "412*","Local sales
taxes",
IIF ([Modified Report Assignments]![Account] Between "413000" and
"414999","Licenses and permits",
IIF (([Modified Report Assignments]![Account] ="461006","Charges for current
services",
IIF (([Modified Report Assignments]!
Like "W*", Fire," ")))))

I believe if someone wouldn't mind helping me get started, I can finish it
out.

Thanks you so much in advance.

Meg​
 
You could also create a mapping table to a common description for a range of
accounts, If I understand what you are saying you have a table like this

Account Description
411000 Property Taxes General $1,000.00
411001 Property Tax Buildings $6,000.00
411002 Property Tax Vehicles $3,000.00

.... and so on, but you would like to see this:

411XXX Property Taxes $10,000.00


What you could do is a table that contains the grouping code and common
description like this

Code Description
411XXX Property Taxes

a second table with all your accounts and the grouping code

Code Account
411XXX 411000
411XXX 411001
411XXX 411002

then create a query joining your table with the accounts with this mapping
table and then to the one with the common description, group the query by the
code (i.e. 411XXX),

Every time you have a new mapping simply add it to the table and your query
will pick it up without having to modify the if else statement
 
Thank you so much for responding! I don't think it is that simple, I wish it
were. To simplify things, I cut that part out of what I am doing in the
posting of my question. I have manually created tables for what you
mentioned but need to have a code to create them because I am a contractor
who was hired to just set up reports. I have 3 tables with the dollar
amounts I have to work with. They each have over 300,000 account, fund and
center combinations which have to be put into about 12 different reports,
sometimes the same combo will be in more than one report and titled
differently as directed by law. To manually create those each time is too
proned for errors.

The financial reports I am creating are in Crystal. I am just designing the
Crystal reports but have had to combine it with access to process the data
from a VERY OLD mainframe system to reports in Crystal. The easiest thing
would be to set up the formulas in Crystal but for reasons I won't go into
here, that idea had to be scrapped. So I have set the reports up so that
they group on the report descriptions which works nicely. Once I finish the
reports, I am gone. Because this is a very large city, accounts are added
daily, I need to create code to create the table which you described so that
this city can run financial statements quarterly. There are quite a few
exceptions in some of the accounts which I didn't go into (example: All
Centers beginning with W go into Fire except accounts 583xxx, 584xxx,
586xxx, 587xxx).

In your opinion, is the code too difficult to do?

Thank you again,

Meg
jl5000 said:
You could also create a mapping table to a common description for a range of
accounts, If I understand what you are saying you have a table like this

Account Description
411000 Property Taxes General $1,000.00
411001 Property Tax Buildings $6,000.00
411002 Property Tax Vehicles $3,000.00

... and so on, but you would like to see this:

411XXX Property Taxes $10,000.00


What you could do is a table that contains the grouping code and common
description like this

Code Description
411XXX Property Taxes

a second table with all your accounts and the grouping code

Code Account
411XXX 411000
411XXX 411001
411XXX 411002

then create a query joining your table with the accounts with this mapping
table and then to the one with the common description, group the query by the
code (i.e. 411XXX),

Every time you have a new mapping simply add it to the table and your query
will pick it up without having to modify the if else statement

Meg said:
I need help with an Elseif statement.

Table Name: Modified Report Assignments

Fields:

Fund
Center
Account
Balance
FCA
Combined Governmental Reports


I need to write some code to populate the "Combined Government Reports"
fields based on the record in either the account or center field. Can
someone please help me get started writing an Elseif statement for the first
few lines of this Iif statement. I can't use Iif because I will have about
75 lines of code.

IIF ([Modified Report Assignments]![Account] Like "411*","Property
taxes",
IIF ([Modified Report Assignments]![Account] Like "412*","Local sales
taxes",
IIF ([Modified Report Assignments]![Account] Between "413000" and
"414999","Licenses and permits",
IIF (([Modified Report Assignments]![Account] ="461006","Charges for current
services",
IIF (([Modified Report Assignments]!
Like "W*", Fire," ")))))

I believe if someone wouldn't mind helping me get started, I can finish it
out.

Thanks you so much in advance.

Meg
​
Click to expand...
​
Click to expand...
​
 
I think is about the same, your code will need to grow for new combinations
as well as a mapping table, the only thing is that a table is easier to
mantain by a user than code,

I have worked in similar projects but not with thant many
reports/cobinations, still the mapping of accounts are a pain, but a user can
do it without me having to change the code



--
jl5000
<a href="http://joshdev.com"></a>


Meg said:
Thank you so much for responding! I don't think it is that simple, I wish it
were. To simplify things, I cut that part out of what I am doing in the
posting of my question. I have manually created tables for what you
mentioned but need to have a code to create them because I am a contractor
who was hired to just set up reports. I have 3 tables with the dollar
amounts I have to work with. They each have over 300,000 account, fund and
center combinations which have to be put into about 12 different reports,
sometimes the same combo will be in more than one report and titled
differently as directed by law. To manually create those each time is too
proned for errors.

The financial reports I am creating are in Crystal. I am just designing the
Crystal reports but have had to combine it with access to process the data
from a VERY OLD mainframe system to reports in Crystal. The easiest thing
would be to set up the formulas in Crystal but for reasons I won't go into
here, that idea had to be scrapped. So I have set the reports up so that
they group on the report descriptions which works nicely. Once I finish the
reports, I am gone. Because this is a very large city, accounts are added
daily, I need to create code to create the table which you described so that
this city can run financial statements quarterly. There are quite a few
exceptions in some of the accounts which I didn't go into (example: All
Centers beginning with W go into Fire except accounts 583xxx, 584xxx,
586xxx, 587xxx).

In your opinion, is the code too difficult to do?

Thank you again,

Meg
jl5000 said:
You could also create a mapping table to a common description for a range of
accounts, If I understand what you are saying you have a table like this

Account Description
411000 Property Taxes General $1,000.00
411001 Property Tax Buildings $6,000.00
411002 Property Tax Vehicles $3,000.00

... and so on, but you would like to see this:

411XXX Property Taxes $10,000.00


What you could do is a table that contains the grouping code and common
description like this

Code Description
411XXX Property Taxes

a second table with all your accounts and the grouping code

Code Account
411XXX 411000
411XXX 411001
411XXX 411002

then create a query joining your table with the accounts with this mapping
table and then to the one with the common description, group the query by the
code (i.e. 411XXX),

Every time you have a new mapping simply add it to the table and your query
will pick it up without having to modify the if else statement

Meg said:
I need help with an Elseif statement.

Table Name: Modified Report Assignments

Fields:

Fund
Center
Account
Balance
FCA
Combined Governmental Reports


I need to write some code to populate the "Combined Government Reports"
fields based on the record in either the account or center field. Can
someone please help me get started writing an Elseif statement for the first
few lines of this Iif statement. I can't use Iif because I will have about
75 lines of code.

IIF ([Modified Report Assignments]![Account] Like "411*","Property
taxes",
IIF ([Modified Report Assignments]![Account] Like "412*","Local sales
taxes",
IIF ([Modified Report Assignments]![Account] Between "413000" and
"414999","Licenses and permits",
IIF (([Modified Report Assignments]![Account] ="461006","Charges for current
services",
IIF (([Modified Report Assignments]!
Like "W*", Fire," ")))))

I believe if someone wouldn't mind helping me get started, I can finish it
out.

Thanks you so much in advance.

Meg
​
Click to expand...
​
Click to expand...
​
Click to expand...
​
 
Back
Top