By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,404 Members | 2,145 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,404 IT Pros & Developers. It's quick & easy.

Setting criteria on an expression in a query

P: 78

I have a query with an expression that compares [SoldDate] to today's date and displays a value in months using the DateDiff() function. I have a second expression to evaluate the first expression and assign a letter based on the value. It would either assign: A,B,C, or D. The query is running correctly at this point.

The problem I am having is I need to set a criteria on the second expression. It is simply = "C". When I try to set the criteria, I get a parameter prompt for the first expression.

Any ideas why this is occuring?

In reality, I don't need to use the second expression. However it would be nice.
Dec 22 '11 #1

✓ answered by TheSmileyCoder

I think I had a similar issue once, where I found that creating a query calculating the SoldDateDiff, and then using that query in another query solved the issue.

I dont know if its "best" way of doing it, but as I recall it worked for me.

Share this Question
Share on Google+
7 Replies


TheSmileyCoder
Expert Mod 100+
P: 2,321
Have you tried it without the = sign? Just plainly writing "c" with the quotes?
Dec 22 '11 #2

P: 78
Thanks, The Smiley Coder. It still causes the parameter issue.
Dec 22 '11 #3

TheSmileyCoder
Expert Mod 100+
P: 2,321
I think I had a similar issue once, where I found that creating a query calculating the SoldDateDiff, and then using that query in another query solved the issue.

I dont know if its "best" way of doing it, but as I recall it worked for me.
Dec 22 '11 #4

P: 78
Thats the approach I have came to also. Thank You!!!

Have a happy holiday TheSmileyCoder
Dec 22 '11 #5

NeoPa
Expert Mod 15k+
P: 31,494
I told you that in the other thread when I posted the code for you :-D I swear that half of what I write is either ignored or misunderstood.

The reason the value is prompted is because of the order that different parts of the SQL run in. Before it gets to the SELECT clause, it must determine if a record qualifies even to be looked at (The WHERE clause), and if so, how it will be grouped (The GROUP BY and ORDER BY clauses). Your [SoldDateDiff] field is determined in the SELECT clause, after the value of the [SoldDateDiffGrp] field is checked in the GROUP BY clause. At that point the SELECT clause has not processed so the references to [SoldDateDiff] are not understood, therefore causing the prompt.

Subqueries (See Subqueries in SQL) are fully determined by the time the outer query tries to access any part of them, so using a subquery (or a named and saved query) as your record source ensures all these values are available in all the clauses.

Does that make the situation clearer?
Dec 22 '11 #6

P: 78
NeoPa,

This explaination you have provided makes the issue much easier to understand. Your advice is always appriciated. At times I feel that the explainations are "over my head" but the more I read them the clearer they become.

As always, thanks for your help.

Have a great holiday season!
Dec 23 '11 #7

NeoPa
Expert Mod 15k+
P: 31,494
I'm happy to hear that, and wish you too a happy holiday season.

Remember though, if there's something any of us says that you don't understand, the best idea is to ask for clarification. Generally (if not always), we'll be happy to clarify.
Dec 24 '11 #8

Post your reply

Sign in to post your reply or Sign up for a free account.