use of constants

  • Thread starter Thread starter eric theise
  • Start date Start date
E

eric theise

We're adding vba functionality to an existing database, and
I'm trying to understand the use and scope of user-defined
constants.

We take minimums and maximums in our queries, and it often
makes sense to set something to negative or positive
infinity so that the minimums and maximums are taken over
the numbers that are truly relevant. It seems as if
defining constants such as cPosInf and cNegInf would be a
nicer (self-documenting) way of doing this than having +/-
2147483647 appear inexplicably in queries.

But when I try to define public constants in my module
they're not seen by my queries.

I'm missing something, but what?

Thanks in advance, Eric
 
eric theise said:
We're adding vba functionality to an existing database, and
I'm trying to understand the use and scope of user-defined
constants.

We take minimums and maximums in our queries, and it often
makes sense to set something to negative or positive
infinity so that the minimums and maximums are taken over
the numbers that are truly relevant. It seems as if
defining constants such as cPosInf and cNegInf would be a
nicer (self-documenting) way of doing this than having +/-
2147483647 appear inexplicably in queries.

But when I try to define public constants in my module
they're not seen by my queries.

I'm missing something, but what?

Thanks in advance, Eric
Click to expand...

You can't use the named constants in your queries. What you can do is
create public functions in a standard module:

'--- constants declared at module level
Public Const cPosInf = 2147483647
Public Const cNegInf = -2147483648

'--- functions declared to return "infinity" constants
Public fncPosInf() As Long
fncPosInf = cPosInf
End Function

Public fncNegInf() As Long
fncNegInf = cNegInf
End Function

.... and then use the functions, rather than the constants, in your
queries.
 
But when I try to define public constants in my module
they're not seen by my queries.

I'm missing something, but what?
Click to expand...

VBA is one language, SQL is another. Inserting VBA variables or
constants in a Query ist als ob ich in Deutsch mittens ins Antwort
veranderte.

You can write a dumb little VBA function to call from a Query to
retrieve the constant: for instance

Public Function GetLim(strEnd As String) As Long
Select Case strEnd
Case "Min"
GetLim = -2147483648
Case "Max"
GetLim = 2147483647
Case Else
MsgBox "Call GetLim with either Min or Max as argument"
End Select
End Function

and then use GetLim("Min") to return the minimum possible Long.
 
Back
Top