Full Version: IIF Problem In Criteria
UtterAccess Forums > Microsoft® Access > Access Forms
morpheus
Hello,

I have the following:

CODE
IIf([Forms]![frmMembersCourses]![frmMemberCourse Subform].[Form]!cboCourse.Column(0)=7,
nbsp;   "First Aider",
IIF(Forms!frmMembersCourses![frmMemberCourse Subform].Form!cboCourse.Column(0)=7 And 10,
    "Advanced First Aider",
IIF(Forms!frmMembersCourses![frmMemberCourse Subform].Form!cboCourse.Column(0)=7 And 10 And 12,
    "Patient Transport Assistant"
IIf(Forms!frmMembersCourses![frmMemberCourse Subform].Form!cboCourse.Column(0)=7 And 10 And 11 And 13,
    "Emergency Transport Attendant",
    "None")


Its not working I am getting a #Name? error however when I put an = at the front I get an Invalid Syntex Error.

Does anyone know whats causing this? or if I can do this in VBA?

Its currently the Control Source for a text Box.

Many Thanks

James


EDIT: SQL to prevent horizontal scrolling. Doug
Edited by: DougY on Tue Aug 4 15:38:40 EDT 2009.
fkegley
First of all your syntax is not right. Instead of = 7 And 10 And other values, try this:
On (7, 10, other values go here)
Also, I am not sure the Iif function can "dig" down as far as you want it to inside the combo box columns collection.
Also your logic may not be correct but that will have nothing to do with the syntax being bad.
morpheus
Hello,
Thanks for the quick reply.
What do you mean when you say in (7, 10, other values go here)
Do I actually need the IN?
If so I have never seen or heard of the IN command what is it and what does it do?
Many Thanks
James
trek46
Just taking a quick look you are missing a comma on the second to the last iif.
CODE
)=7 And 10 And 12,"Patient Transport Assistant" IIf(Forms!frmMembersCourses

should be
CODE
)=7 And 10 And 12,"Patient Transport Assistant", IIf(Forms!frmMembersCourses
fkegley
I just read your Iif again, I now think my first response was incorrect. I think you are storing multiple values in the field, is that right? Possibly with a , separating the values? Or maybe as one string of consecutive digits?
On that case, you probably want to check for a string value, such as "7" or "7, 10" or maybe "71012". Or possibly some other way. I do know that what you are doing will not work as you want it to. You are using the AND operator which means something completely else to Access VBA than you want it to.
I am not sure this is a good way to store this data. A better way might be to store each value in its own row in a table, together with a way to tie the value to a particular person. Then to get the correct title, you can get the DMax value of the value for a particular person, then convert THAT to a piece of text.
morpheus
Hello,
Thank you for your responses and I have put the comma back in but still teh same error.
I am just trying to get the course ID so that it will display a role not holding data in a table anywhere.
I will have a look further and see if I have missed any other comma's etc...
Many Thanks
James
morpheus
Hello,
My IIF statment has all the comma's there now and its still not having any of it.
Frank - How would I do what you mean? is it just literally 7,10,13 etc?
Many Thanks
James
NewHorizons
I suggest not using the nested if's to get your syntax working correctly. After that you can start nesting, it may help you get this working.
The way that I am reading your original note is below:
One needs 7 to be a "First aider", a 7 and a 10 to be "An Advanced First Aider", etc.
How is this data appear in Form!cboCourse.Column(0)?
Does it appear as a string or a single value?
Can someone have a 10 rating only which implies that they also have the 7?
These would help to determine the fix.
Good luck,
NH
morpheus
Hello,
The data in Form!cboCourse.Column(0) is the course ID so its just a number.
The ID is based on a course you go on soas long as they have 1 of tjhe courses or two of them they will get the specific role.
So if they ONLY have 7 they will be a first aider If they have ONLY 10 then they will be NONE. If they have 7 AND 10 they are an Advanced First Aider.
I hope this makes sense.
They can not be an advanced first aider without being a first aider.
Many Thanks
JAmes
NewHorizons
Since Form!cboCourse.Column(0) results in a single integer, it can no possibly be 7 and 10, etc. It can ony be one value, 7 OR 10, etc.

You will need to restructure things to get it working the way you describe.

NH
Edited by: NewHorizons on Wed Aug 5 8:50:14 EDT 2009.
morpheus
When you say restructure things? or would it have to be based on the members courses table where a member ID and a course ID meet? so then a member ID and they can have multiple course ID's with it.
Could this be a better way of doing this? Would this then be another form of code or something else?
Like a DLOOKUP or something?
Many Thanks
James
NewHorizons
Yes, I think this would be a better way to do it.
You can create/use:
A Members table consisting of an ID, name, other meaningful fields
A Course table consisting of an ID, description, other meaningful fields
A CoursesTaken table consisting of the MemberID and the CourseID, and other meaningful fields (date, grade, etc.)
Since each higher level course is dependant on the lower level course you can find the max course level to assign the label. The way you have things structured today may be like this, they way that you were trying to detemine the lablel was not correct becuase the valve of CourseID (Form!cboCourse.Column(0)) could only hold one value per row of the cbo.
OR
You can instead consider adding separate fields (one for each course, assuming you don't have a whole bunch) to the member table. You can make these fields ID holders if you have a courses table with ID and description, etc. or use the date that the course was taken, or some other meaningful entry.
Somethings to consider.
I think the underlying issue from what you started with was syntax of the nested if as well as trying to compare multiple valves from a single value holding cbo row.
HTH,
NH
morpheus
Hello,
think I will have a look at doing the DLOOKUP and see what happens and I will post back my results.
Many Thanks
James
morpheus
Hello,
I have tried the following:
=IIf(DLookUp("MemberID"=[MembersID],[tblMemberCourse],"CourseID"=7),"First Aider","None")
MemberID IS a field in the Table and courseID IS a field in the table too.
MEMBERSID IS a text box with teh control course looking at MEMMBERID on the form.
Does anyone know why I am getting a #Name? error?
Many Thanks
James
datAdrenaline
Very short on time ...
IIf(DLookUp("MemberID"=[MembersID],[tblMemberCourse],"CourseID=7"),"First Aider","None")
datAdrenaline
Any chance you can post your table structure? ... Multi-valued fields is not a good situation to me in (7, 7 And 10, etc.) ... when you have a situation like that you typically need a "child" table and relationship between the "parent" table and the "child" table ...
S> did you ever get your days <-> years thing worked out in your previous thread? ...
morpheus
Hello,
Not fully as of yet I am waiting some more stuff to come from that! sad.gif
However what is wrong with the CourseID?
The memberCourse is a member ID and a courseID field...
Does that help any as I think thats the relationship your saying I need?
Many Thanks
James
morpheus
Hello,
I have made the change to the DLOOKUP however I am still getting the #Name? error.
Is there any other reason for this?
Many Thanks
James
This is a "lo-fi" version of UA. To view the full version with more information, formatting and images, please click here.