Access 2003 using IIf statement on a form

35 pts.
Tags:
Access 2003 forms
IIF function
Microsoft Access
Microsoft Access 2003
Microsoft Access forms
I am building a database to help my daughter with keeping a record of pupil progress. I have a form in which I have a field for 'current grade' and a field for 'target grade'. I also have a field 'progress' in which I am using an 'IIf statement' to compare the two fields (to show either 'under target' or 'on target'). In both of the 'grade' fields are entered any of the grades from 3c,3b,3a,4c,4b,4a...through to 8c where 3c is the lowest grade and 8c the highest. It works fine with the number values but the a,b,c within each number work the wrong way round (i.e. it says that 5c is higher than 5b). Am I missing some vital point? The statement I am using is: - =IIf([CGrade]>=[TGrade],"On or above target","Under target") Hope someone can help.

Answer Wiki

Thanks. We'll let you know when a new response is added.

Once you introduced the a,b,c, the grade is now alpha numeric. So C comes after B. You may need to split the letters from the numbers. So one field has 3,4,5,.. and the other has the c, b, a. Then you can sort by field1 ascending and field2 descending.

—————–

One way to make it work could be to use the MID (or LEFT) function and nested iif statements. Something like this:

=IIf(Left([CGrade],1) = Left([TGrade],1),IIf([TGrade]>=[CGrade],”On or above target”,”Under target”),IIf([CGrade]>=[TGrade],”On or above target”,”Under target”))

Notice the inverted comparison in the second iff, due to the alphanumeric ordering mentioned by Randym.

——————

Or take that one step farther to:

IIf(Left([CGrade],1)>Left([TGrade],1),”Above target”,IIf(Left([CGrade],1)<Left([TGrade],1),”Below target”,IIf(Right([CGrade],1)<Right([Tgrade],1),”Above target”,IIf(Right([CGrade],1)>Right([Tgrade],1),”Below target”,”At target”))))

If the number is higher, it bumps out of the first IIf with “Above target”. Otherwise, it checks to see if the number is lower, in which case it bumps out with “Below target”. If neither of those are true, then the numbers must be equal, and you test the letter in a similar manner.

——————–

Thanks guys, you put me on the right track. what I have ended up with is:
=IIF(LEFT([CGRADE],1)<LEFT([TGRADE],1),”Under target”,IIF(LEFT([CGRADE],1)>LEFT([TGRADE],1),”Above target”,IIF(LEFT([CGRADE],2)=LEFT([TGRADE],2),”On target”,IIF(LEFT([TGRADE],2)>LEFT([CGRADE],2),”Above target”,”Under target”))))
And this works just fine.

Malc.

————————————–

A neater and more readable solution (and better practice) would be to use a lookup table that eg matches 3c to 19, 3b to 20 etc

Discuss This Question: 2  Replies

 
There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy
  • carlosdl
    The complete code is not visible. If you are going to copy and paste, make sure you copy the complete line.
    69,920 pointsBadges:
    report
  • msi77
    I think you need use combo box with two columns: 1 3c 2 3b 3 3a ... and so on. Then, compare by first column.
    1,670 pointsBadges:
    report

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

REGISTER or login:

Forgot Password?
By submitting you agree to receive email from TechTarget and its partners. If you reside outside of the United States, you consent to having your personal data transferred to and processed in the United States. Privacy

Thanks! We'll email you when relevant content is added and updated.

Following