Date with IIF statement 2

[monkeysee]

I have a query that lists the type and dates of inspections for food facilities. Fields used:

FoodID
Priority (this will only have entries: 1, 2, 3)
InspectionDate

I wish to add a field : "Re-Inspection Date"
which will be based on the Priority & InspectionDate fields;

Examples:
If priority = 1, then the next inspection will be 4 months after the InspectionDate.

If priority = 2, then the next inspection will be 1 year after the InspectionDate.

If priority = 3, then the next inspection will be 2 years after the Inspection Date.

Can anyone help with building the "iff" statement or suggest alternative?

 

[r937]

select IIf(priority=1
         , dateadd("m",4,InspectionDate)
      ,IIf(priority=2
         , dateadd("y",1,InspectionDate)
         , dateadd("y",2,InspectionDate)
       )) as ReInspectionDate
  from yourtable

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[monkeysee]

thanks r937

I used your expression and got some errors - I modified as so:

SELECT Food.BusinessName, Food.Priority, Inspections.InspectionDate, IIf([Priority]=1,DateAdd("mm",4,[InspectionDate]),
IIf([Priority]=2,DateAdd("yyyy",1,[InspectionDate]),
IIf([Priority]=3,DateAdd("yyyy",2,[InspectionDate])))) AS ReInspectionDate
FROM Food INNER JOIN Inspections ON Food.FacilityID = Inspections.FacilityID;

the field returns: #Error

any ideas? (also I should have mentioned, I using Access 2003)

 

[PHV]

And something like this ?
SELECT Food.BusinessName, Food.Priority, Inspections.InspectionDate,
IIf([Priority]=1, DateAdd('m',4,[InspectionDate]),
IIf([Priority]=2, DateAdd('yyyy',1,[InspectionDate]),
IIf([Priority]=3, DateAdd('yyyy',2,[InspectionDate]), [InspectionDate]))) AS ReInspectionDate
FROM Food INNER JOIN Inspections ON Food.FacilityID = Inspections.FacilityID;


Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

yeah, i was wrong to use "y" for year, "y" is day of year

but "m" is month, i just looked it up

plus, your last IIF -- which is not necessary -- is missing the third parameter

the syntax is IIF(condition,valueiftrue,valueiffalse)

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[monkeysee]

No, still getting the #error

 

[r937]

could you show the latest query with the changes i suggested?

also, what is the datatype of InspectionDate?

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[monkeysee]

r937
Really appreciate the help!
Here is what I have:

SELECT Food.BusinessName, Inspections.InspectionDate, IIf([Priority]=1,DateAdd("m",4,[InspectionDate]),
IIf([Priority]=2,DateAdd("yyyy",1,[InspectionDate])
,DateAdd("yyyy",2,[InspectionDate]))) AS ReInspectionDate
FROM Food INNER JOIN Inspections ON Food.FacilityID = Inspections.FacilityID;


also, i don't really understand why the 3rd parameter isn't needed as priority 2 and 3 differ in length of time of inspection

 

[PHV]

Seems you even not tried my suggestion ...

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[monkeysee]

PHV
Yes, I did,
the field returned: "#error"

also, when using the 3rd parameter as you wrote, I got another error stating duplicate value.

Still open for suggestions!

 

[PHV]

I got another error stating duplicate value
???

 

[monkeysee]

PHV
yes, (refer to the line which addresses Priority 3)

SELECT Food.BusinessName, Food.Priority, Inspections.InspectionDate,
IIf([Priority]=1, DateAdd('m',4,[InspectionDate]),
IIf([Priority]=2, DateAdd('yyyy',1,[InspectionDate]),

[highlight]IIf([Priority]=3, DateAdd('yyyy',2,[InspectionDate]), [InspectionDate])))[/highlight]

AS ReInspectionDate
FROM Food INNER JOIN Inspections ON Food.FacilityID = Inspections.FacilityID;

I eliminated the second [InspectionDate]
then I was back to the #Error message.

 

[PHV]

And you get a 'Duplicate value error' simply executing this SELECT instruction ?

 

[monkeysee]

r937
oops forgot:
datatype of InspectionDate is date (short date) mm/dd/yyyy

 

[monkeysee]

PHS

yes

 

[PHV]

And this ?
SELECT F.BusinessName, F.Priority, I.InspectionDate,
IIf(F.Priority=1, DateAdd('m',4,I.InspectionDate),
IIf(F.Priority=2, DateAdd('yyyy',1,I.InspectionDate),
IIf(F.Priority=3, DateAdd('yyyy',2,I.InspectionDate), I.InspectionDate))) AS ReInspectionDate
FROM Food F INNER JOIN Inspections I ON F.FacilityID=I.FacilityID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[r937]

what is the datatype of Priority? if it's not numeric, you might need to put quotes around '1' and '2'

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts March 6 2005)

 

[monkeysee]

r937

That did it! THANKS!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

 

[monkeysee]

r937
Can you help with this scenario?

Same Date & Iff statement, however, I wish to add one more condition.

I need the calculation to perform only if the field: [Inspection.Type] = "routine"

If it doesn't = routine, then I want the user to be able to fill in date of choosing.

Thanks a bunch for any help you can give.!

 

[PHV]

PARAMETERS [Enter new inspection date] DateTime;
SELECT F.BusinessName, F.Priority, I.InspectionDate,
IIf(I.Type='routine',
IIf(F.Priority='1', DateAdd('m',4,I.InspectionDate),
IIf(F.Priority='2', DateAdd('yyyy',1,I.InspectionDate),
IIf(F.Priority='3', DateAdd('yyyy',2,I.InspectionDate),
I.InspectionDate))), [Enter new inspection date]) AS ReInspectionDate
FROM Food F INNER JOIN Inspections I ON F.FacilityID=I.FacilityID;

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244