using nested iif() in ms access queries

Question

0.00/5 (No votes)

See more:

Hi guys!
I need some help here in designing a query, i need to calculate patient's age starting with less than one month age. i have a date of birth field, date of diagnosis field. i expect to subtract the two dates to obtain age. I tried to use nested iif(), but i a get logic/semantic errors. Can anyone please tell me what i am missing?
Here is the query i used,

SQL

SELECT diagnosis.txt_typeofvisit, 
       patientpersonaldata.dat_birthday, 
       patientpersonaldata.txt_sex, 
       diagnosis.dat_diagnosisdate, 
       Iif(Year([dat_diagnosisdate]) - Year([dat_birthday]) >= 1, "greaterthan",
       Iif(( 
       Month([dat_diagnosisdate]) ) - ( Month([dat_birthday]) ) <= 7, "less",
       "greaterthan" 
       )) AS AgeRang1 
FROM   diagnosis 
       INNER JOIN patientpersonaldata 
               ON diagnosis.ipnr = patientpersonaldata.ipnr 
WHERE  ( ( ( diagnosis.txt_typeofvisit ) = "revisit" ) 
         AND ( ( patientpersonaldata.txt_sex ) = "f" ) );

Posted 20-Nov-13 1:33am

WhiteTulip

Updated 20-Nov-13 22:01pm

♥…ЯҠ…♥

v2

Add a Solution

Comments

CHill60 20-Nov-13 7:35am

It would help if we knew what the error was

ZurdoDev 20-Nov-13 7:55am

Can you just fix the syntax error?

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

WhiteTulip · Accepted Answer · 2013-11-20T19:00:00

Quote:
there is no syntax error in the query, its a logic error, i mean the query gives result but when you check not all results are true. for example in above query i used Year([dat_birthday])this will return the value of year as integer. check this function,
IIf(Year([dat_diagnosisdate])-Year([dat_birthday])<=1,"True","False")
this function in a query will subtract the two years as integer values and if the difference is less or equals to one, then it will return true and if the difference is greater than one, it will return false.
Since i wanted a condition that i can play with ages less than a year, like a 1 month age to 12 months age, then in the iif(condition,"true","false") i had to nest another iif() if the the age is less or equals to one. that is iif(year(condition),iif(month(condition),"true","false"),"false") with this, now i could specify if i want lets say age less than eight months then in my query I'll have iif() as follows;
IIf(Year([dat_diagnosisdate])-Year([dat_birthday])<=1,IIf((MONTH([dat_diagnosisdate]))-(Month([dat_birthday]))<8,"true","false"),"false") as Age.
this was still giving me logic error, since it does not consider the whole date difference, that is if date of birth is 07/24/2012 and date diagnosed is 06/03/2013, the actual age should be 11months but with this function it could not calculate like that, instead it returns as -1 and year difference is equal to one, then it passed the conditions in iif() above.
but after trying to search more functions that can be used to calculate dates, i came to find the DateDiff(). I could simply use this to find difference in two dates and return values in either year,months,weeks,hours,seconds etc.
So the correct solution was as follows;

SQL
SELECT diagnosis.txt_TypeOfVisit, PatientPersonalData.dat_birthday, PatientPersonalData.txt_sex, diagnosis.dat_diagnosisdate,
IIf((datediff("m",[dat_birthday],[dat_diagnosisdate]))< 2,"lessthan","greaterthan") As AgeRang1
FROM diagnosis INNER JOIN PatientPersonalData ON diagnosis.ipnr = PatientPersonalData.Ipnr
WHERE (((diagnosis.txt_TypeOfVisit)="Revisit") AND ((PatientPersonalData.txt_sex)="F"));
Any ways thanks a lot guys for your support. Its nice to know that you are not working alone, ;)