How to update field with a few "if" possibilites

[ps40life]

I need to update a grade field using several parameters:

if age = 5 and grade = PK then grade = K
if grade = k then grade = 1
if grade = between 1 and 11 then grade = grade + 1
otherwise don't change

Is this doable in an update query or is there another way?

Thanks,
Susan : )

 

[scriverb]

I am a little cautious about this as I see you have mixed the grades in [grade] with characters and numbers. I am not sure if we will have a type mismatch here. Let's give it a try. I am going to setup a table and test it here also.

Put this in the Update To row of your update query:
NZ(Switch([Age]=5 and [grade]=&quot;PK&quot;, &quot;K&quot;, [grade]=&quot;K&quot;, 1, CInt([grade])>=1 and CInt([grade])<=11, [grade] + 1)),[grade]) Bob Scriver

 

[ps40life]

I changed the SQL to this.

UPDATE tblDataStudent SET tblDataStudent.CurrentGrade = Switch([Age]=5 And [currentgrade]=&quot;PK&quot;,&quot;K&quot;,[currentgrade]=&quot;K&quot;,&quot;01&quot;,[currentgrade]=&quot;01&quot;,&quot;02&quot;,[currentgrade]=&quot;02&quot;,&quot;03&quot;,True,[currentgrade])
WHERE (((tblDataStudent.Active)=Yes));

I still need to add the rest of the grades up to 11. I think you are right about the issue of mixing letters and numbers, but I had no choice. So it is a text field. It seems to work fine if I use it this way. One thing though-the Age 5 and Grade PK part does not work. It just leaves it the same. Should I just do that one in another query or is there another way to write that part?
Thanks

 

[scriverb]

UPDATE tblDataStudent SET tblDataStudent.CurrentGrade = Switch([Age]=5 And [currentgrade]=&quot;PK&quot;,&quot;K&quot;,[currentgrade]=&quot;K&quot;,&quot;01&quot;,[currentgrade]=&quot;01&quot;,&quot;02&quot;,[currentgrade]=&quot;02&quot;,&quot;03&quot;,True,[currentgrade])
WHERE (((tblDataStudent.Active)=Yes));

The Age = 5 and CurrentGrade = PK should work. I see nothing wrong with it. Is Age a numeric field? If not then change the 5 to a &quot;5&quot; or &quot;05&quot; depending on the size of the field. This would account for this expression not working. Check this over very carefully as it should work the way it is written.

I am confused by the red code above. TRUE? What are you checking for TRUE? You must have a valid expression here. Luckily nothing has fallen through to that point so that the query tried to execute the expression or you would have had an error. Were you planning on checking a field called Retain or HoldBackAYear or something like that? You must put in a valid equation. And it may need to be checked first so that if it is TRUE the Switch won't be checking the other values which also may be applicable.
Bob Scriver

 

[ps40life]

The age field is a number field. I put the true in because I saw it in help. To tell you the truth, I don't know why it is there, but it gives a syntax error without it. I sent the db file to you.

 

[scriverb]

All is well with the database now. I created the LEFT JOIN as directed and picked up the TestID and the appropriate Accomodations for the test.

Let me know if this works for you. Bob Scriver

 

[scriverb]

Ignore the previous post as it was for the other thread. This query as described above works just fine. No need to change anything. I tested all of the scenarios and they all updated as advertised. The true is there in case ACCESS doesn't find an expression that is true it will test the expression TRUE and find it TRUE so it then returns the value of [currentgrade]. This in effect changes nothing.

The post above that I sent you does the same thing. The NZ function tests for a return of Null. You see if the Switch statement doesn't find a match in any of the expressions it returns a Null value. The NZ around the Switch statement then tests for the Switch returning a Null and if found then returns the value of the second parameter which is [CurrentGrade]

NZ(Switch([Age]=5 and [grade]=&quot;PK&quot;, &quot;K&quot;, [grade]=&quot;K&quot;, 1, CInt([grade])>=1 and CInt([grade])<=11, [grade] + 1)),[grade])

Two ways of arriving at the same result. Bob Scriver