Please help with Elseif in Query

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

Guest

I REALLY need help with an Elseif statement in a query. I am receiving 3 or
4 tables which I convert to access and then have to create tables which will
assign about 300,000 rows with classifications which will be used for groups
in Crystal Reports. For each set of reports, I have to set up about 6
access tables to set up the groups. I am using a make table query to do
this, however the expression box of the IIF is too small for the number of
IIFs I need, so I think I need to be able to do this with code. If someone
would be kind enough to help get me started, I think I will be able to write
the remaining elseif statements on my own. Might need some additional help
calling them up.

Below is a small piece of the query which was working until I ran out of
room in the expression builder box. I think if someone would be kind enough
to take the time to convert these two lines to code I can fill in the rest.
I have written some code in the past in the form or report mode (with lots of
help from this board), but never free standing code like this.

Here are the two lines that I would like one of the GREAT MINDS on this
board to be so kind as to convert.

Table Name: Modified Report Assignments

Fields:

Fund (text)
Center (text)
Account (text)
FCA (text)
Combined Governmental Reports (text)


I need to write some code to populate the "Combined Government Reports"
fields based on the record in either the account or center field.

IIF ([Modified Report Assignments]![Account] Like "411*","Property
taxes",
IIF ([Modified Report Assignments]![Account] Between "413000" and
"414999","Licenses and permits"," "))
THANK YOU so much in advance.

Meg
 
Hi Meg,

There are a couple of ways to do this based on your style (Please note that
the Ifs used only have one I):

If...Then...ElseIf...EndIf Method
------------------------------------
If [Modified Report Assignments]![Account] like "411*" Then
[Modified Report Assignments]![Combined Governmental Reports] =
"Property taxes"
ElseIF [Modified Report Assignments]![Account] Between "413000" and "414999"
Then
[Modified Report Assignments]![Combined Governmental Reports] =
"Licenses and permits"
EndIf

You could make your coding a little easier using the With syntax. For
example, the above code could also be written like this:

With [Modified Report Assignments]
If ![Account] like "411*" Then
![Combined Governmental Reports] = "Property taxes"
ElseIF ![Account] Between "413000" and "414999" Then
![Combined Governmental Reports] = "Licenses and permits"
EndIf
End With

Make sure your code after the "Then" is on a separate line than the
If...Then line.

Alternatively, you could also use the Select Case method of writing this code:

With [Modified Report Assignments]
Select Case ![Account]
Case Like "411*": ![Combined Governmental Reports] = "Property
taxes"
Case Between "413000" and "414999": ![Combined Governmental
Reports] = "Licenses and permits"
End Select
End With

I hope this helps.

Michael

Meg said:
I REALLY need help with an Elseif statement in a query. I am receiving 3 or
4 tables which I convert to access and then have to create tables which will
assign about 300,000 rows with classifications which will be used for groups
in Crystal Reports. For each set of reports, I have to set up about 6
access tables to set up the groups. I am using a make table query to do
this, however the expression box of the IIF is too small for the number of
IIFs I need, so I think I need to be able to do this with code. If someone
would be kind enough to help get me started, I think I will be able to write
the remaining elseif statements on my own. Might need some additional help
calling them up.

Below is a small piece of the query which was working until I ran out of
room in the expression builder box. I think if someone would be kind enough
to take the time to convert these two lines to code I can fill in the rest.
I have written some code in the past in the form or report mode (with lots of
help from this board), but never free standing code like this.

Here are the two lines that I would like one of the GREAT MINDS on this
board to be so kind as to convert.

Table Name: Modified Report Assignments

Fields:

Fund (text)
Center (text)
Account (text)
FCA (text)
Combined Governmental Reports (text)


I need to write some code to populate the "Combined Government Reports"
fields based on the record in either the account or center field.

IIF ([Modified Report Assignments]![Account] Like "411*","Property
taxes",
IIF ([Modified Report Assignments]![Account] Between "413000" and
"414999","Licenses and permits"," "))
THANK YOU so much in advance.

Meg
Click to expand...
 
Michael:

Thanks for answering. I took a long weekend, but as soon as I get int the
office I will work with that. It is pretty much what I thought except, I was
thinking I would have to write some declaration statements or something.
That is what I am struggling with. Do I not need any? I'm assuming I set up
my expression to call a macro which will call a module where I put the code.
Correct?
Thanks again so much! You guys and gals are the best!

Meg
 
Back
Top