IIF function

[Guest_imported]

hi,

I am having problem with this query in access.

I am trying to design a query which would compare 2 similar fields from 2 different tables and if the fields are not similar, take value from the one field.

Since this is a if, else type stuff, my friend suggested me to use IIf function but it does not run for some reasons known to you guys !!

I am putting the SQL statement here.. i appreciate your time and efforts,

thanks,

SELECT ERICFILE.AUTHOR, ERICFILE.TITLE, ERICFILE.PUBDATE, ERICFILE.RECORDNUM, ERICFILE.[Page Counts], ERICFILE.CENO, ERICFILE.DATO, ERICFILE.INST
FROM abbreviations RIGHT JOIN ERICFILE ON abbreviations.Abbreviations = ERICFILE.INST
WHERE (((ERICFILE.DATO)=[Enter Date:]) AND ((ERICFILE.INST)=IIf([ERICFILE]![INST]<>[abbreviations]![Abbreviations],[ERICFILE]![INST],[abbreviations]![INST])));

tech_head

 

[BanditWk]

I'm not sure if this is what you're looking for, but if you are just DISPLAYING the information, try this:

SELECT ERICFILE.AUTHOR, ERICFILE.TITLE, ERICFILE.PUBDATE, ERICFILE.RECORDNUM, ERICFILE.[Page Counts], ERICFILE.CENO, ERICFILE.DATO, IIf([ERICFILE]![INST]<>[abbreviations]![Abbreviations],[ERICFILE]![INST],[abbreviations]![INST]) AS INST
FROM abbreviations RIGHT JOIN ERICFILE ON abbreviations.Abbreviations = ERICFILE.INST
WHERE (ERICFILE.DATO)=[ENTER DATE:]);

Basically, what I'm saying here, is that you need to put the IIF in the Select Statement.

HTH
Roy McCafferty
aka BanditWk

Las Vegas, NV
roy@cccamerica.org
RLMBandit@aol.com (private)

&quot;I do this because I know I can - no need to send gifts - just send me a smile to show me that I've helped.&quot; ~ seen on a cardboard sign held by Roy McCafferty on a corner in Las Vegas

 

[ScoobyDew]

I made a report that totals the number of absences for each employee. How do I get the report to print a certain type of response based on the number of absences. For example, if an employee totals 4 absences then I need the word &quot;Verbal&quot; to appear on the report next to that particular employee. If the employee totals 5 absences then the word &quot;Written&quot; needs to appear... and so forth and so on. These are disciplinary stages that an employee incurs for violating the attendance policy.
The report shows many employees and is sorted by employee. Can I create some kind of formula in a text box, possibly in the footer section for each employee that will accomplish this task?

Please help!
Thank you