Help with "if" Statement in Query

  • Thread starter Thread starter Erin
  • Start date Start date
E

Erin

I have a query, qryTally, that tallies how many tardies that an employee has.
What I need to do now is create another query using qryTally that would give
me the following information:

if Tardies = 4, "Verbal Warning"
if Tardies = 8, "1st Written Warning"
if Tardies = 11, "2nd Written Warning"
if Tardies = 15, "Termination"

if Tardies = any other number of null, ""

How the heck do I do that???

Thanks!!!
 
On Wed, 5 Aug 2009 06:28:01 -0700, Erin

You can use nested IIf statements:
iif(t = 4, "verbal", iif(t = 8, "1st written, iif(...etc...)))

-Tom.
Microsoft Access MVP
 
Best way is to use a table --
tblTardyAction --
Low High Action
0 3 -
4 7 Verbal Warning
8 10 1st Written Warning
11 14 2nd Written Warning
15 99 Termination

Use this query --
SELECT YourTable.Name, YourTable.TimesTardy, IIf([TimesTardy] Between [Low]
And [High],[Action],"Error") AS FRate
FROM YourTable, [tblTardyAction ]
WHERE (((YourTable.TimesTardy) Between [Low] And [High]));
 
It would help if you posted the SQL of the existing query.

(Possibly unneeded instructions follow)
Open the query
Select View:Sql from the Menu
Select all the text
Copy it
Paste it into the message

As a guess, you can use an expression in the query that would look like the
following. This would all be on one line in a query.
IIF(Tardies=4,"Verbal Warning"
,IIF(Tardies=8,"1st Written Warning"
,IIF(Tardies=11,"2nd Written Warning"
,IIF(Tardies=15,"Termination",Null))))

A better solution would involve a small table (tblWarnings) with two fields
TCount
TMessage

Now you can add that table and your query together to get the result you want.
SELECT qTardyCount.*, tblWarnings.TMessage
FROM qTardyCount LEFT JOIN tblWarnings
ON qTardyCount.Tardies = tblWarnings.TMessage

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
 
Back
Top