Use expression/calculated fields as criteria?

E

eselk2003

I've read a lot of posts about setting criteria ON calculated fields.
I'd like to use a calculated field AS criteria. Is this possible?
I'm using Access 2000, but will probably be upgrading to 2003 in the
near future.

Here is an example query I have in SQL format, it returns all sales
for the prior month:

SELECT SALES.[ID]
FROM SALES
WHERE (((SALES.DATE)>=DateSerial(Year(Now()),Month(Now())-1,1) And
(SALES.DATE)<=DateSerial(Year(Now()),Month(Now()),1-1)))
ORDER BY SALES.DATE;

In more complex queries I use the
"DateSerial(Year(Now()),Month(Now())-1,1)" expression in several
places. In order to avoid entering the same formula multiple times I
was hoping I could create a field with that formula in it and give it
a name like "QueryStartDate" (I know how to do that part)... then in
the criteria part I could just say ">=[QueryStartDate]".

I tried it, but I get a prompt for QueryStartDate.

I'm guessing you just can't do this... but just wanted to make sure I
wasn't missing something, since that sure would make my queries easier
to follow. I kind of think maybe you can do this though, because I
think you can use normal fields as part of the criteria of another
field (i.e.-"This field must be >= some other field"), so maybe my
syntax is just wrong.

I know I could write a VBA function to return that value, and then use
it... but in my experience, VBA functions take longer to execute
(slower queries) than built-in functions.
 
K

KARL DEWEY

It might work for some queries and not for others. The reason is the
'chicken & egg' problem - which came first.
There is a sequence in processing the SQL statement. You can not use it
before it is created.
Your best bet is to create an unbound form with unbound text boxes and refer
to then in the query.
[Forms]![YourFormName]![TextBox1]
If using it for dates then hedge your bet by doing it this way --
CVDate([Forms]![YourFormName]![TextBox1])
With wildcard like so --
Like [Forms]![YourFormName]![TextBox1] & "*"
OR
Like "*" & [Forms]![YourFormName]![TextBox1] & "*"
 
G

ghetto_banjo

You should be able to do that without VBA. When you say that you
created a field "with that formula in it", do you mean that you set
the default value for that field in a table? Or it is the control
source on a form you are using?

If its a field on a form, you need to preface with it with Forms!
formName.


If it is prompting for it, it doesnt know where that field is (or
there is a typo somewhere).
 
J

John Spencer

No, you can do that.

What you can do is get the information from an open form control. The
control's value could be controlled by the expression.

DO you know how to reference form controls in a query.

You could use a vba function. As long as the function is in the where clause
and does not reference any fields in the query it should get called ONE time
so it would still be fast.

WHERE Sales.[Date] >= GetPriorMonthStart() And Sales.[Date] <= GetPriorMonthEnd()

THen your function would be as simple as:
Public Function GetPriorMonthStart() as Date
GetPriorMonthStart = DateSerial(Year(Date()),Month(Date())-1,1)
End Function

Try it and see if your query performance is really affected. I think you will
find no real difference.

John Spencer
Access MVP 2002-2005, 2007-2008
The Hilltop Institute
University of Maryland Baltimore County
 
M

Michel Walsh

If the question is about using an alias in the WHERE clause when the alias
is defined in the SELECT clause, the answer is NO. A work around would be to
use a virtual table.


SELECT a, a+b AS c
FROM table
WHERE a+b >0

is ok,

SELECT a, a+b AS c
FROM table
WHERE c0


is not ok,


SELECT *
FROM (
SELECT a, a+b AS c
FROM table
)
WHERE c>0



is ok.




Vanderghast, Access MVP
 
E

eselk2003

If the question is about using an alias in the WHERE clause when the alias
is defined in the SELECT clause, the answer is NO. A work around would beto
use a virtual table.
SELECT *
FROM (
            SELECT a, a+b AS c
            FROM table
            )
WHERE c>0

is ok.

Thank you, and thanks to all who replied. This looks promissing, and
also I'll have to check the VBA function to see if it really is slow
like I was thinking. Before I think I was using the VBA function in
the select statement, because it was being called for each row, but if
only called once for a WHERE clause, then that should still be fast.

...good to know a few options, thanks again.
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top