IIf in Query

[TheHarleygirl2005]

I have a database where my "CourseID" field lists specific training courses. "Other" is one of the choices. If you select "Other" you are required to enter a course name in the "CourseDescription" field. I have a lot of "Other" listings in my query. What I would like to do is if the CourseID is "Other", leave it blank in my query otherwise list the name of the course. Would I do this with an IIF statement? and if so, would it go in the criteria section of the query? I have never used the IIF function before and am unsure how to make this work.

[pbaldy]

It would go as a new field, not in the criteria. Something like:

IIf(CourseID = "Other", "", CourseName)

Access will alias the new field as Expr1, which you can change to whatever you want.

[TheHarleygirl2005]

I am getting an error (see attached)

[June7]

Don't see any aggregate function calculations. Why using Totals query?

Don't see [CourseName] field in the tables. Is it actually in TrainingT?

Is CourseID a number type field? Do you have lookup alias on this field in table? Advise not to do that, review http://access.mvps.org/access/lookupfields.htm

[pbaldy]

Try changing Group By to Expression. It looks like CourseName needs to be CourseDescription? Does CourseID contain the text "Other", or is it perhaps a numeric ID field?

[pbaldy]

I'll get out of the way.

[TheHarleygirl2005]

Okay, I figured it out. CourseID is pulling from a table of Course Names. I changed the CourseID to a number rather than "Other" and it worked. Thank you so much for your help!

[TheHarleygirl2005]

The totals query is calculating number of hours. I have a start time and end time and need to calculate totals hours for that course.

I do have one question about your answer. I have about twenty to thirty courses each year, so I have a table that lists course names and then use lookup in the table for the CourseID field. You recommend not doing that, how would I accomplish this? The Course Name Table only has two fields Course Name and an auto generated number for the key. The only other way I can think of doing this is to add both tables to a query and then use a list box or combo box on the form to pull the information.

[June7]

I don't build lookups in tables. I use combo or list boxes on form.

You are saving a course ID and name into TrainingT? I would not have an "Other" course record. I would save only the ID or the name. If saving the ID, would have method for creation of new record into Courses table. This is what the NotInList event of list and combo boxes is for.

Your image does not show linking on PK field of EmployeeT. PK should be saved as FK. Either change the PK to MIS or save MemberID as FK. MemberID and MIS should not both be in TrainingT.

Also, advise no spaces or special characters/punctuation (underscore is exception) in naming convention. Better would be CarNum, Car_Num, CarNo.