在ms访问查询中使用嵌套的iif（） [英] using nested iif() in ms access queries

问题描述

大家好！

我在设计查询时需要一些帮助，我需要计算从不到一个月的年龄开始的患者年龄。我有一个出生日期，诊断日期字段。我希望减去两个日期以获得年龄。我试图使用嵌套的iif（），但我得到逻辑/语义错误。谁能告诉我我错过了什么？

这是我用的查询，

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,

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" ) ); 

推荐答案

引用：

没有语法查询中的错误，它是一个逻辑错误，我的意思是查询给出结果但是当你检查不是所有的结果都是真的。例如，在上面的查询中，我使用了Year（[dat_birthday]），这将返回year作为整数的值。检查此功能，

IIf（年份（[dat_diagnosisdate]） - 年份（[dat_birthday]）< = 1，True，False）

此功能在查询中将两年减去整数值，如果差值小于或等于1，则返回true，如果差值大于1，则返回false。

自我想要一个条件，我可以玩不到一年的年龄，如1个月到12个月的年龄，然后在iif（条件，真，假）我必须嵌套另一个iif（）如果年龄小于或等于一岁。那是iif（年（条件），iif（月（条件），真，假），假）这个，现在我可以指定我是否想让我们说年龄少于八个月然后在我的查询中我将有iif（）如下;

IIf（年（[dat_diagnosisdate]） - 年（[dat_birthday]）< = 1 ，IIf（（月（ [dat_diagnosisdate]）） - （月（[dat_birthday]））< 8，true，false），false）作为年龄。

这仍然给我逻辑错误，因为它不考虑整个日期差异，即如果出生日期是07/24/2012并且诊断日期是06/03/2013，实际年龄应该是11个月但是使用此功能它不能像那样计算，而是它返回-1并且年差等于1，然后它传递了上面的iif（）条件。

但是在尝试搜索更多可用于计算日期的函数后，我来到找到DateDiff（）。我可以简单地使用它来查找两个日期的差异，并返回年，月，周，小时，秒等的值。

所以正确的解决方案如下;

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;

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, ;)

这篇关于在ms访问查询中使用嵌套的iif（）的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！