constant date criteria

  • Thread starter Thread starter pow67
  • Start date Start date
P

pow67

In my Access97 db

[VERIFIED] = date field

In many queries, I use the following criteria for [VERIFIED] to select
certain data:
Date()-365
Click to expand...

I would like to replace "365" with a constant value such as "ExpDate" so
that I only need to change the value of "ExpDate" in order to change the
value in all my queries.

I created the following module:

Public Const ExpDate = 365

but when I changed my query to read:
Date()-ExpDate
Click to expand...

it produced an error message that read:

"data mismatch"

Do I have to declare "ExpDate" somewhere in the query?

Thanks in advance

CW.
 
Unfortunately, you can't reference a global constant or variable in a
VBA module directly from SQL, or indeed from the definition of a
calculated control on a Form or Report. You have to define a Public
Function which returns the value of the constant (which can then be
local to the module that contains the Function. In your example, your
Module code would look something like:

Public Const ExpDate = 365

Function Return_ExpDate () As Date

Return_ExpDate = ExpDate

End Function


Your Query column would read: Date () - Return_ExpDate ()



In my Access97 db

[VERIFIED] = date field

In many queries, I use the following criteria for [VERIFIED] to select
certain data:
Date()-365
Click to expand...

I would like to replace "365" with a constant value such as "ExpDate" so
that I only need to change the value of "ExpDate" in order to change the
value in all my queries.

I created the following module:

Public Const ExpDate = 365

but when I changed my query to read:
Date()-ExpDate
Click to expand...

it produced an error message that read:

"data mismatch"

Do I have to declare "ExpDate" somewhere in the query?

Thanks in advance

CW.
Click to expand...

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Thanks for the direction. I am still receiving an error of data mismatch
but I will continue to work.

I did not even know you could create a "public function" and call it in
a query.

Thanks again.
CW
 
I think you may need to express your criterion as:
(Date() - Return_ExpDate())
Click to expand...

Note the parens. If you omit them, everything after the comparison
operator gets interpreted as literal text, hence the error.

Thanks for the direction. I am still receiving an error of data mismatch
but I will continue to work.

I did not even know you could create a "public function" and call it in
a query.

Thanks again.
CW
Click to expand...

Please respond to the Newsgroup, so that others may benefit from the exchange.
Peter R. Fletcher
 
Back
Top