Constant value in queries

[James Pelletier]

I have five queries in my database which need to calculate the number of
days between today's date and the target finish date. I use this format:

DaysLeft: DateValue("12/31/2009")-Date()

I want to declare a constant, for example:

Define FINISHDATE 40178

and then:

DaysLeft: FINISHDATE-Date()

so I only have to change the value in one place if the target date changes.
Does anyone have any ideas on how to achieve this?

Jim

 

[Allen Browne]

You can do this in Access 2007 by using a TempVar

You cannot do it in previous versions.

You could create a VBA function in a standard module to return the value,
e.g.:

Public Function GetFinishDate As Date
GetFinishDate = CDate(40178)
End Function

 

[Allen Browne]

That should work in break mode, if the window has focus, and nothing else is
wrong.

It may not work if a timer is running.

You can always open the Immediate Window (Ctrl+G), and ask about a variable,
e.g.:
? var1

Or you can use the Watch or Locals window (View menu, in the code window.)

 

[John W. Vinson]

I have five queries in my database which need to calculate the number of
days between today's date and the target finish date. I use this format:

DaysLeft: DateValue("12/31/2009")-Date()

I want to declare a constant, for example:

Define FINISHDATE 40178

and then:

DaysLeft: FINISHDATE-Date()

so I only have to change the value in one place if the target date changes.
Does anyone have any ideas on how to achieve this?

Jim

I'd certainly recommend storing *data* such as a finish date in a field in a
Table - perhaps a one-row, one field table, but in a table nonetheless. It'll
be more easily used, more easily edited when needed, can be kept secure... VBA
constants and public variables are just not stable enough.