Using Expressions in an SQL Statement in VBA

[33396670]

Hi,
I am trying to populate a Recordset object with data coming from an SQL statement that has expressions on it. To create the expressions I use IIF statements as the result of the expressions are going to be part of my output and either True or False, based on some conditions.
The problem is that I get an error saying that "I am missing parameters" when I try to run my application. Apparently this is not the right way to do this, but I have no idea of how to fix it.
I am posting here the sql statement I have at the moment.

SELECT [Third Party Interface/Need].ID,[Third Party Entity].[Third Party Entity],[Need Type].[Need Type], IIf([AddedRecord]=True And ([TimeStampForNew]>=#8/30/08# And [TimeStampForNew]<=#09/26/08#),1,0) AS [Expr1],IIf([Open/Closed_ID]=2 And ([Actual Date]>=#8/30/08# And [Actual Date]<=#09/26/08#),1,0) AS [Expr2],IIf(IsDate([Current Due Date]),IIf(DateDiff('d',Date(),[Current Due Date])>0 And DateDiff('d',Date(),[Current Due Date])<=30,1,0),0) AS [Expr3],IIf([AddedRecord]=True And ([TimeStampForNew]>=#8/30/08# And [TimeStampForNew]<=#09/26/08#) Or ([Open/Closed_ID]=2 And ([Actual Date]>=#8/30/08# And [Actual Date]<=#09/26/08#)) Or (IsDate([Current Due Date])=True And (DateDiff('d',Date(),[Current Due Date])>0 And DateDiff('d',Date(),[Current Due Date])<=30)),1,0) AS Expr4 FROM [Third Party Entity] INNER JOIN ([Need Type] INNER JOIN [Third Party Interface/Need] ON [Need Type].ID = [Third Party Interface/Need].[Need Type_ID]) ON [Third Party Entity].ID = [Third Party Interface/Need].[Third Party Entity_ID] Where [Expr4]= True Order by [Expr1] DESC,[Expr2] DESC,[Expr3] DESC


Any help would be appreciated,

Thanks

 

[lespaul]

What is it you are trying to do with this query? What's the raw data behind it and what are the results you need?

Leslie

Have you met Hardy Heron?

 

[PHV]

You can't use aliases in the WHERE nor the ORDER BY clause.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886