where to put IIF function

[Feprincess]

Does an IIF function go in the criteria row?
I want to say if place1 has a 'U' in it put it into column 'Stuent1U' but if it has a 'C' in it, I want it to go into Column 'Student1C'.
Thanks to anyone who may help me.

 

[Len Boorman]

Nope IIF does not go into criteria

Think you need two update queries to deal with each situation but since it is a sort of calculated value why update and store the result anyway

L

 

[Bilbo_Baggins_Esq]

Is this an Update query, or a Select Query?

If it is a simple SELECT query these go in the Field Rows for two columns:
In one column
Student1U:iif(instr([your_table_name]![place1],1,"U")>0,[your_table_name]![place1],null)

In another column
Student1C:iif(instr([your_table_name]![place1],1,"C")>0,[your_table_name]![place1],null)

 

[Bilbo_Baggins_Esq]

If this is for an UPDATE query, you can use the iif with the instr function in the criteria, but if you want to do it in one query, you'll need to make sure you place the different criteria for the two different fields on TWO seperate rows in the criteria section.

However, as Len suggested, you shouuld not normally need to store and retail calculated values. It is essentally duplicated data.
Using this for a SELECT query, to populate a form or report may be one usage, but if your going to store this in a table, you're going to make your database unnecessarily larger than it needs to be.

 

[Feprincess]

I'm not sure what you mean by update but it is a query that I have already run and need more specific information based on the query that I've already run.
It's for student summer school hours which fall into two catagories, Capped and Uncapped.
I already have the totals but now need to seperate them in Capped or Uncapped columns.
Does that sound ok?

 

[Rabbie]

Look up "Query" in Access help for more information about the different types of Query

 

[Bilbo_Baggins_Esq]

Well that sounds like a Select Query to me, but I'm still not sure.
When you look at the query in Design View, are any of the rows labled (on he far left side of the column edit area) "Update to:" or "Append to:".
If not, then this is a simple Select Query, and you can use the syntax I posted earlier.


There numerous types of queries; Select, Append, Update, Delete, Add Table, Crosstab, and Group By.

 

[Feprincess]

Hi and thank you, but no there are no rows with that command.
My boss gave me this project and told me that the only way to learn it is to figure it out myself, isn't that nice?
I put the select query calculation in the field heading correct and that will be my nw column?
What do you mean by, your_table_name? Input the actual name of my table that I've created?

This is the calculation that I used to add up the minutes & hours.
Student10: ([M0510_10]+[M1530_10])+(([HOURA_10]+[HOURB_10])*60)
Do I put the IIF in front of this calculation?

 

[Brianwarnock]

This query that calculates the hours per student , call it query1, presumably does or can have output of
Place1,Student,totalhours. but with correct field names

Take this into query2 and have 2 fields like

Totalu:IIf(Instr(1,[Place1],"u")>0,[totalhours],0)
then also for c
This will list the times for u and c turn it into a totals query to get the totals for u and c

Brian

 

[Bilbo_Baggins_Esq]

Hi and thank you, but no there are no rows with that command.

The "Update To:" and "Append To:" are NOT commands.
They would be ROW headings on the far left edge of the column section of the query in DESIGN VIEW.

My boss gave me this project and told me that the only way to learn it is to figure it out myself, isn't that nice?

Ahh, the old "Sink or Swim" learning method. Slow and nerve wrecking, but often effective.

I put the select query calculation in the field heading correct and that will be my nw column?

unless I misread your initial post, you're looking for TWO new columns, "Stuent1U" and "Student1C".
Assuming that is the case, the calculations I posted earlier are for TWO seperate columns in the Design view and should be pasted in the "Field:" row for TWO seperate columns.

What do you mean by, your_table_name? Input the actual name of my table that I've created?

Yes, you'll replace the phrase your_table_name by typing or pasting the name of your actual table in that space.

This is the calculation that I used to add up the minutes & hours.
Student10: ([M0510_10]+[M1530_10])+(([HOURA_10]+[HOURB_10])*60)
Do I put the IIF in front of this calculation?

WHERE DID THIS COME FROM???
You made no mention that this IIF had anything to do with adding time!!!

 

[Feprincess]

The total hours/minutes for the C & U students is supposed to be on the same query page, so says my boss. He said "don't make two queries".
I'm ready to shoot myself in the head at this point because he told me that I'm taking too long.

 

[Bilbo_Baggins_Esq]

Paste this in "FIELD:" row of TWO seperate columns in your query design view

Totalu:iif(instr([your_table_name]![place1],1,"U")>0,([M0510_10]+[M1530_10])+(([HOURA_10]+[HOURB_10])*60),0)

In another column
TotalC:iif(instr([your_table_name]![place1],1,"C")>0,([M0510_10]+[M1530_10])+(([HOURA_10]+[HOURB_10])*60),0)

Be sure to replace "your_table_name" with the name of your actual table.

 

[Brianwarnock]

The total hours/minutes for the C & U students is supposed to be on the same query page, so says my boss. He said "don't make two queries".
I'm ready to shoot myself in the head at this point because he told me that I'm taking too long.

You have not said what your output should look like, if it is student, hoursu, hoursc then it would appear on one page just add student to query2.

Bilbo I think your InStr is incorrect, I think the syntax is Instr(startpos,string1,string2) and if the names of the fields are unique you donot need the table name as Access will find them.

Brian

 

[Feprincess]

No that was the original calculation to add minutes + hours to get the totals.
Now he wants me to seperate them each student (1-10) into U or C columns.
Thanks for your help, but I'm not sure if I can explain myself clearly enough.
I appreciate it.

 

[Brianwarnock]

Whoa don't give up.

You currently have a query which I think gives

Student Totaltime_for_student

and you want

Student Totaltime_for_student_uncapped Totaltime_for_student_capped

My query 2 will give that if query1 includes Place1 simply by adding Student into query2, then you just run query2.

Or try Bilbo's approach with the corrections I mentioned.

Note that it is not unusual to run multiple queries to get what you want, any report is based on the final query, the report automatically runs everything.

Brian

 

[Bilbo_Baggins_Esq]

My BAD, you are indeed correct. Typing too fast.

Paste this in "FIELD:" row of TWO seperate columns in your query design view

TotalU:iif(instr(1,[place1],"U")>0,([M0510_10]+[M1530_10])+(([HOURA_10]+[HOURB_10])*60),0)

In another column
TotalC:iif(instr(1,[place1],"C")>0,([M0510_10]+[M1530_10])+(([HOURA_10]+[HOURB_10])*60),0)

This should give you exactly what your boss seeks.

 

[Feprincess]

Thank you so much!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!
I took and introductory course on Microsoft office last semester.
In two weeks I start a course on Access only, so I won't have to shoot myself afterall.
I appreciate eveyone's help!
Thank the heavens for the Internet...