new to if statements

[Kelly]

I am trying to create an if statement that would assign a classroom. I have
a data field that has a grade and I want each grade level to be assigned to a
room.

classroom: iif ([next grade] = 1 or 2, "1st and 2nd", = 3 or 4, "3rd and
4th" = 5 or 6 or 7 or 8, " "5th through 8th")

Clearly this doesn't work because I don't know what I'm doing, but I'm
hoping it will give someone an idea of what I want it to do and maybe you can
tell me what it should say, or if it's even possible.

Thanks for any advice.

 

[Beetle]

IIf([NextGrade] = 1 Or [NextGrade] = 2, "1st and 2nd", IIf([NextGrade] = 3
Or [NextGrade] = 4, "3rd and 4th", "5th throught 8th"))

This assumes 1 - 8 are the only possible values.

 

[KARL DEWEY]

Try this --
classroom: iif ([next grade] = 1 or [next grade] = 2, "1st and 2nd",
iif([next grade] = 3 or [next grade] = 4, "3rd and 4th", "5th through 8th"))

 

[Duane Hookom]

I wouldn't use an IIf() statement. Data belongs in tables, not expressions.
Create a table of grades and rooms.
Grade Room
1 1st and 2nd
2 1st and 2nd
3 3rd and 4th
4 3rd and 4th
5 5th through 8th
6 5th through 8th
7 5th through 8th
8 5th through 8th
Add this table to your query and display the Room field.

 

[Kelly]

This worked great! Thank you

Try this --
classroom: iif ([next grade] = 1 or [next grade] = 2, "1st and 2nd",
iif([next grade] = 3 or [next grade] = 4, "3rd and 4th", "5th through 8th"))

I am trying to create an if statement that would assign a classroom. I have
a data field that has a grade and I want each grade level to be assigned to a
room.

classroom: iif ([next grade] = 1 or 2, "1st and 2nd", = 3 or 4, "3rd and
4th" = 5 or 6 or 7 or 8, " "5th through 8th")

Clearly this doesn't work because I don't know what I'm doing, but I'm
hoping it will give someone an idea of what I want it to do and maybe you can
tell me what it should say, or if it's even possible.

Thanks for any advice.

 

[Jerry Whittle]

You would need a many level nested IIf statement. They can get rather ugly
and hard to maintain. You'd probably be better off with a Case statement in a
code module.

Past the following in a Module. Make sure to save it then go up to Debug,
Compile. Hopefully it won't give you any errors.

Function fClassrooms(strClassrooms As Variant) As String
Dim TheClassroom As String
Select Case strClassrooms
Case 1 to 2
TheClassroom = "1st and 2nd"
Case 3 To 4
TheClassroom = "3rd and 4th"
Case 5 To 8
TheClassroom = "5th through 8th"
Case Else ' Other values.
TheClassroom = "No Classroom"
End Select
fClassrooms = TheClassroom
End Function

Next in a query, form, or report you call upon this function. In a query it
would look like:

Classroom: fClassrooms([next grade])

 

[vanderghast]

Indeed, as example, if the "administration" decide to change the 'grouping',
they can do it by changing data from a FORM, rather than having to transform
themselves into developer and... changing YOUR code. Sure, they will
probably ask YOU to do it, in one year and two months from now, but you
won't remember imediately WHERE ELSE you have to change the 'data in the
code', well, not, if all your data is at the same place, in ONE table,
rather than repeated here and there, in YOUR code...


So, definitively, using a table approach is to be prefered to the 'data in
the code' approach. Even if it seems to be 'stupid' to use a new table just
for that, the database itself don't really care. And you will be less
stressed, in one year from now



Vanderghast, Access MVP

I wouldn't use an IIf() statement. Data belongs in tables, not expressions.
Create a table of grades and rooms.
Grade Room
1 1st and 2nd
2 1st and 2nd
3 3rd and 4th
4 3rd and 4th
5 5th through 8th
6 5th through 8th
7 5th through 8th
8 5th through 8th
Add this table to your query and display the Room field.

--
Duane Hookom
Microsoft Access MVP

I am trying to create an if statement that would assign a classroom. I
have
a data field that has a grade and I want each grade level to be assigned
to a
room.

classroom: iif ([next grade] = 1 or 2, "1st and 2nd", = 3 or 4, "3rd and
4th" = 5 or 6 or 7 or 8, " "5th through 8th")

Clearly this doesn't work because I don't know what I'm doing, but I'm
hoping it will give someone an idea of what I want it to do and maybe you
can
tell me what it should say, or if it's even possible.

Thanks for any advice.