I have an expression that I am trying to reference as criteria in the same query. Is this even possible? I think I know how to do that with a form and unbound textbox but if there is an easier way inside the query, I would rather do that. The SQL is:
SELECT [Enter Qtr] AS Qtr, [Enter Year] AS Yr, IIf([Qtr]=2,"0101" & [Yr],100301) AS StartInv, IIf([Qtr]=2,"0401" & [Yr],103101) AS EndInv, tblRevenue.CustomerID, tblRevenue.InvoiceNum, tblRevenue.Revenue
FROM tblRevenue
WHERE (((tblRevenue.InvoiceNum) Between [StartInv] And [EndInv]));
Everything works the way I want it except the "Where" clause. I want the Where clause to select criteria based upon what was entered. Can this be done?
SELECT [Enter Qtr] AS Qtr, [Enter Year] AS Yr, IIf([Qtr]=2,"0101" & [Yr],100301) AS StartInv, IIf([Qtr]=2,"0401" & [Yr],103101) AS EndInv, tblRevenue.CustomerID, tblRevenue.InvoiceNum, tblRevenue.Revenue
FROM tblRevenue
WHERE (((tblRevenue.InvoiceNum) Between [StartInv] And [EndInv]));
Everything works the way I want it except the "Where" clause. I want the Where clause to select criteria based upon what was entered. Can this be done?
