If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
IIf Statement in Query Criteria
I have the following code in a query:
SELECT qryPointsWithDropOffDates.EMPLOYEEID, qryPointsWithDropOffDates.SSN, qryPointsWithDropOffDates.NAME, qryDisciplineNoticesReprint.LASTINCIDENTDATE, qryPointsWithDropOffDates.INCIDENTDATE, qryPointsWithDropOffDates.TYPESHORT, qryPointsWithDropOffDates.POINTVALUE FROM qryDisciplineNoticesReprint INNER JOIN qryPointsWithDropOffDates ON qryDisciplineNoticesReprint.SSN=qryPointsWithDropO ffDates.SSN WHERE (((qryPointsWithDropOffDates.INCIDENTDATE) Between [qryDisciplineNoticesReprint.CALCDATE] And [qryDisciplineNoticesReprint.LASTINCIDENTDATE]) AND ((qryPointsWithDropOffDates.POINTVALUE]0)) ORDER BY qryPointsWithDropOffDates.EMPLOYEEID, qryPointsWithDropOffDates.INCIDENTDATE; This works fine in most circumstances. However, I did fine a loop hole I need to fix. If the field [qryDisciplineNoticesReprint.DISCIPLINE]="ATTENDANCE INFORMATION FORM", I need to change the between statement to read "Between [qryDisciplineNoticesReprint.CALCDATE] And [qryDisciplineNoticesReprint.DISCIPLINEDATE]). The code I am trying looks like this: SELECT qryPointsWithDropOffDates.EMPLOYEEID, qryPointsWithDropOffDates.SSN, qryPointsWithDropOffDates.NAME, qryDisciplineNoticesReprint.LASTINCIDENTDATE, qryPointsWithDropOffDates.INCIDENTDATE, qryPointsWithDropOffDates.TYPESHORT, qryPointsWithDropOffDates.POINTVALUE FROM qryDisciplineNoticesReprint INNER JOIN qryPointsWithDropOffDates ON qryDisciplineNoticesReprint.SSN=qryPointsWithDropO ffDates.SSN WHERE (((qryPointsWithDropOffDates.INCIDENTDATE)=IIf([qryDisciplineNoticesReprint.DISCIPLINE]="ATTENDANCE INFORMATION FORM", (qryPointsWithDropOffDates.INCIDENTDATE] Between [qryDisciplineNoticesReprint.CALCDATE] And [qryDisciplineNoticesReprint.DISCIPLINEDATE]),(qryPointsWithDropOffDates.INCIDENTDATE) Between [qryDisciplineNoticesReprint.CALCDATE] And [qryDisciplineNoticesReprint.LASTINCIDENTDATE])) AND ((qryPointsWithDropOffDates.POINTVALUE]0)) ORDER BY qryPointsWithDropOffDates.EMPLOYEEID, qryPointsWithDropOffDates.INCIDENTDATE; With the added IIf statement in the criteria, the query returns no records. It should return 9 records. Can someone tell me what I am doing wrong int he code above? Thank you in advance. |
#2
|
|||
|
|||
IIf Statement in Query Criteria
You can't put the Betwen inside the IIf(). Try a WHERE CLAUSE of
WHERE INCIDENTDATE Between CALCDATE And IIF(DISCIPLINE]="ATTENDANCE INFORMATION FORM", DISCIPLINEDATE,LASTINCIDENTDATE) AND POINTVALUE0 -- Duane Hookom Microsoft Access MVP "AccessIM" wrote: I have the following code in a query: SELECT qryPointsWithDropOffDates.EMPLOYEEID, qryPointsWithDropOffDates.SSN, qryPointsWithDropOffDates.NAME, qryDisciplineNoticesReprint.LASTINCIDENTDATE, qryPointsWithDropOffDates.INCIDENTDATE, qryPointsWithDropOffDates.TYPESHORT, qryPointsWithDropOffDates.POINTVALUE FROM qryDisciplineNoticesReprint INNER JOIN qryPointsWithDropOffDates ON qryDisciplineNoticesReprint.SSN=qryPointsWithDropO ffDates.SSN WHERE (((qryPointsWithDropOffDates.INCIDENTDATE) Between [qryDisciplineNoticesReprint.CALCDATE] And [qryDisciplineNoticesReprint.LASTINCIDENTDATE]) AND ((qryPointsWithDropOffDates.POINTVALUE]0)) ORDER BY qryPointsWithDropOffDates.EMPLOYEEID, qryPointsWithDropOffDates.INCIDENTDATE; This works fine in most circumstances. However, I did fine a loop hole I need to fix. If the field [qryDisciplineNoticesReprint.DISCIPLINE]="ATTENDANCE INFORMATION FORM", I need to change the between statement to read "Between [qryDisciplineNoticesReprint.CALCDATE] And [qryDisciplineNoticesReprint.DISCIPLINEDATE]). The code I am trying looks like this: SELECT qryPointsWithDropOffDates.EMPLOYEEID, qryPointsWithDropOffDates.SSN, qryPointsWithDropOffDates.NAME, qryDisciplineNoticesReprint.LASTINCIDENTDATE, qryPointsWithDropOffDates.INCIDENTDATE, qryPointsWithDropOffDates.TYPESHORT, qryPointsWithDropOffDates.POINTVALUE FROM qryDisciplineNoticesReprint INNER JOIN qryPointsWithDropOffDates ON qryDisciplineNoticesReprint.SSN=qryPointsWithDropO ffDates.SSN WHERE (((qryPointsWithDropOffDates.INCIDENTDATE)=IIf([qryDisciplineNoticesReprint.DISCIPLINE]="ATTENDANCE INFORMATION FORM", (qryPointsWithDropOffDates.INCIDENTDATE] Between [qryDisciplineNoticesReprint.CALCDATE] And [qryDisciplineNoticesReprint.DISCIPLINEDATE]),(qryPointsWithDropOffDates.INCIDENTDATE) Between [qryDisciplineNoticesReprint.CALCDATE] And [qryDisciplineNoticesReprint.LASTINCIDENTDATE])) AND ((qryPointsWithDropOffDates.POINTVALUE]0)) ORDER BY qryPointsWithDropOffDates.EMPLOYEEID, qryPointsWithDropOffDates.INCIDENTDATE; With the added IIf statement in the criteria, the query returns no records. It should return 9 records. Can someone tell me what I am doing wrong int he code above? Thank you in advance. |
Thread Tools | |
Display Modes | |
|
|