What's wrong with this SQL??

[checkOut]

After inserting the Iif Function, the code doesn't work. So I think the error is in there, Maybe an IIF function doesn't work in SQL? Any help appreciated.

Gerard
-------------------------------------------
SELECT tbl_ControleOnderaannemers.RegelID, tbl_ControleOnderaannemers.MPRef, MARKTPARTIJ.bedrijfsnaam AS Onderaannemer, tbl_ControleOnderaannemers.OndAannContrRef, tbl_ControleOnderaannemers.dteVerplicht AS Uiterlijk, tbl_ControleOnderaannemers.dteIngekomen AS Ingekomen, tbl_OndAannContract.txtContract AS Document
FROM (tbl_ControleOnderaannemers INNER JOIN MARKTPARTIJ ON tbl_ControleOnderaannemers.MPRef = MARKTPARTIJ.MP_Id) INNER JOIN tbl_OndAannContract ON tbl_ControleOnderaannemers.OndAannContrRef = tbl_OndAannContract.OndAannID
WHERE (((tbl_ControleOnderaannemers.MPRef) Like [Forms]![frmControleOnderaannemers]![cboOnderaannemer]) AND ((tbl_ControleOnderaannemers.dteVerplicht)=IIf(nz([Forms]![frmControleOnderAannemers]![blnToolate],0)=0,([tbl_ControleOnderaannemers].[dteVerplicht])>#1/1/1900#,([tbl_ControleOnderaannemers].[dteVerplicht])>Date())) AND ((tbl_ControleOnderaannemers.dteIngekomen) Is Null));

 

[jksmi]

Hi,

looking at your iif statement in isolation...

IIf(nz([Forms]![frmControleOnderAannemers]![blnToolate],0)=0,([tbl_ControleOnderaannemers].[dteVerplicht])>#1/1/1900#,([tbl_ControleOnderaannemers].[dteVerplicht])>Date()))

it is going to return true or false rather than a date, since you are evaluating dteVerplicht as a date I would expect that you want the iif statement to return a date. What's the logic behind the iif statement? maybe you need to embed further iif statements...


HTH, Jamie
FAQ219-2884

 

[lespaul]

The standard format of an IIF statement is:

IIF(CONDITION, TrueReturns, FalseReturns)

So, for an easier example:

IIF(LName = 'Smith', 'Is Smith', 'Is Not Smith')


In your statement below, you are saying:

IIf(nz([Forms]![frmControleOnderAannemers]![blnToolate],0)=0,([tbl_ControleOnderaannemers].[dteVerplicht])>#1/1/1900#,([tbl_ControleOnderaannemers].[dteVerplicht])>Date()))

1.  if blnTooLate is null then make it zero
2.  if blnTooLate = 0 then return (dteVerplicht > 1/1/1900) which only a value of true
3.  if blnTooLate <> 0 return true or false if dteVerplicht > today

What do you want to do if blnTooLate = 0? you can do another iif statement like this:

IIF(LName <> 'Smith', iif(LName = 'Jones', 'This is Jones', 'This is not Jones or Smith'), 'This is Smith')

so to break that down

I'm checking the LNAME = 'DOE'

check LName is it NOT EQUAL to Smith returns True (because it's DOE)
so if the condition returns TRUE, we check to see if the name = JONES which returns FALSE
So we would get: 'This is not Jones or Smith'

If we checked 'SMITH'
check LName is it NOT EQUAL to Smith RETURN FALSE so we would get 'This is SMITH'.

Hope that helps explain how the iif statement and nested iif statements work.

If you need more help figuring out the logic for what you need to do, feel free to post back!



Leslie