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