use constant in sql code

  • Thread starter Thread starter ruisoares via AccessMonster.com
  • Start date Start date
R

ruisoares via AccessMonster.com

Hi,

If I set a "Public Const" in my code, can I use the definition in a sql code?

Thanks
 
No. Create a function whose sole purpose is to return the variable's value.

'In a standard module
Public intMyVariable as Integer

Public Function GetMyVariable() As Integer
GetMyVariable = intMyVariable
End Function

Then, in your query:
SELECT ABC, DEF, GetMyVariable() As XYZ FROM tblMyTable

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Office DevCon 2007. Sydney, Australia 3rd-4th Nov. Will you be there?
http://www.block.net.au/devcon/index.htm
 
You method is sound, however, it still relies on a global varialble. IMHO
global variables don't exist. I prefer a similar technique, but using a
static variable in a Public function. Its value is immune from being reset
when an error occurs.

This version uses an Optional argument. If you pass an argument, the value
you pass will be assigned to the static varilabe; otherwise, the static
variable will retain its value. In either case, the function returns the
value of the static variable. To use it, you pass it a value as you would
when you assign a value to a global variable. When you call it from the
query, you pass no value and the current value of the static variable is
returned.

Public Function GetMyVariable(varNewVal As Variant) As Integer
Static intMyVariable As Integer

If Not IsMissing(varNewVal) Then
intMyVariable = Cint(varNewValue)
End If
GetMyVariable = intMyVariable

End Function
--
Dave Hargis, Microsoft Access MVP


Graham R Seach said:
No. Create a function whose sole purpose is to return the variable's value.

'In a standard module
Public intMyVariable as Integer

Public Function GetMyVariable() As Integer
GetMyVariable = intMyVariable
End Function

Then, in your query:
SELECT ABC, DEF, GetMyVariable() As XYZ FROM tblMyTable

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Office DevCon 2007. Sydney, Australia 3rd-4th Nov. Will you be there?
http://www.block.net.au/devcon/index.htm
Click to expand...
 
Yeah, thought about showing that one too, but opted to keep it simple with
just one example. But you're right.

Regards,
Graham R Seach
Microsoft Access MVP
Sydney, Australia

Office DevCon 2007. Sydney, Australia 3rd-4th Nov. Will you be there?
http://www.block.net.au/devcon/index.htm



Klatuu said:
You method is sound, however, it still relies on a global varialble. IMHO
global variables don't exist. I prefer a similar technique, but using a
static variable in a Public function. Its value is immune from being
reset
when an error occurs.

This version uses an Optional argument. If you pass an argument, the
value
you pass will be assigned to the static varilabe; otherwise, the static
variable will retain its value. In either case, the function returns the
value of the static variable. To use it, you pass it a value as you would
when you assign a value to a global variable. When you call it from the
query, you pass no value and the current value of the static variable is
returned.

Public Function GetMyVariable(varNewVal As Variant) As Integer
Static intMyVariable As Integer

If Not IsMissing(varNewVal) Then
intMyVariable = Cint(varNewValue)
End If
GetMyVariable = intMyVariable

End Function
Click to expand...
 
Back
Top