Setting criteria on an expression in a query

[wisni1rr]

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.

[TheSmileyCoder]

Have you tried it without the = sign? Just plainly writing "c" with the quotes?

[wisni1rr]

Thanks, The Smiley Coder. It still causes the parameter issue.

[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.

[wisni1rr]

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

Have a happy holiday TheSmileyCoder

[NeoPa]

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?

[wisni1rr]

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!

[NeoPa]

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.