Where Clause of a SQL Statement

[Kavy]

Sorry, I posted this in another thread, but it has been simplified and is an entirely different question.

Below, I would like to switch the where clause to say "WHERE [Num] <= [Cycles]", but when I do this, Access prompts me to enter "Cycles" as a parameter, instead of pulling the calculated value below:

<code>

SELECT DateAdd([ShortForm],[Num],[First]) AS [Pay Dates], Fix([Days]/[AverageDaysPer]) AS Cycles, [datee]-[Start] AS Days, [Forms]![Budgets]![txtEDate] AS Datee, BVendors.Vendor, BAccounts.FirstOccurrence AS [First]
FROM Numbers, Freq INNER JOIN (BVendors INNER JOIN BAccounts ON BVendors.ID = BAccounts.BVendor) ON Freq.ID = BAccounts.FreqDesc
WHERE (([Num]<=8));
</code>

The code is using "Num" to cycle through a DateAdd function like a Do-loop in VBA

Any ideas?

Thank you

Kavy

 

[xenou]

If this is Cycles:

Fix([Days]/[AverageDaysPer]) AS Cycles

Then in your where clause:

WHERE [NUM] <= Fix([Days]/[AverageDaysPer])

I thought a UDF was a good idea for your needs - you could embed programming logic in a function with the flexibility of VBA.

 

[Kavy]

Thank you for your reply!

When I put that into the WHERE statement, the query requests for me to input the "Days" parameter, despite the fact it is already defined (for some reason it doesn't need it if I only put a constant into the WHERE statement).

I can't seem to figure this one out!

Also,

As for UDF, never used it, I will have to google it.

Thanks for the tip!

Kavy

 

[Kavy]

Got it, I didn't realize my stupidity, I have to type out each calculated value in the WHERE clause, as per the following:

<code>

SELECT DateAdd([ShortForm],[Num],[First]) AS [Pay Dates], Fix([Days]/[AverageDaysPer]) AS Cycles, [datee]-[Start] AS Days, [Forms]![Budgets]![txtEDate] AS Datee, BVendors.Vendor, BAccounts.FirstOccurrence AS [First], Freq.AverageDaysPer, Fix(((Date()-[Start])/[AverageDaysPer])) AS Expr1
FROM Numbers, Freq INNER JOIN (BVendors INNER JOIN BAccounts ON BVendors.ID = BAccounts.BVendor) ON Freq.ID = BAccounts.FreqDesc
WHERE (((Numbers.NUM)<=Fix((([Forms]![Budgets]![txtEDate]-[Start])/[AverageDaysPer]))));

</code>

Thank you, your reply made me realize the mistake!