Where Clause of a SQL Statement

K

Kavy

Well-known Member
Joined
Jun 25, 2007
Messages
607
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
 

Excel Facts

Copy formula down without changing references
If you have =SUM(F2:F49) in F50; type Alt+' in F51 to copy =SUM(F2:F49) to F51, leaving the formula in edit mode. Change SUM to COUNT.
Sort by date Sort by votes
If this is Cycles:
Code: 
Fix([Days]/[AverageDaysPer]) AS Cycles

Then in your where clause:
Code: 
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.
 
Upvote 0
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
 
Upvote 0
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!
 
Upvote 0
You must log in or register to reply here.

Similar threads

K
  • Question Question
Where Clause in SQL Insert Statement
2
Replies
14
Views
1K
strive4peace
strive4peace
K
  • Question Question
Max Parameter Value
Replies
2
Views
605
Kavy
K
K
  • Question Question
Query vs Dlookup
Replies
2
Views
1K
Kavy
K
K
  • Question Question
Input to a Query from a Form
Replies
1
Views
763
Kavy
K
K
  • Question Question
Summing my Query
Replies
2
Views
639
Kavy
K

Forum statistics

Threads
1,224,618
Messages
6,179,916
Members
452,949
Latest member
beartooth91

Share this page

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top