Variables in queries

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Is there a way to reference public variables in queries? Right now, I create
an invisible unbound control on the form, assign its value to the value of
the public variable, then reference the control in the query:
[Forms]![FormName]![ControlName]. It would be so much easier to avoid all the
code and just use [PublicVariableName] in my query.

Any better methodolgies?
 
You can't refer to a variable directly in a query. You could write a
function that returns the value of the variable and call the function from
the query ...

Public varWhatever As Variant

Public Function GetWhatever As Variant
GetWhatever = varWhatever
End Function

SELECT SomeField, GetWhatever() AS Whatever FROM SomeTable

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.
 
Thanks. Would you consider use of a function more efficient than the
invisible unbound control method I am currently using?

Brendan Reynolds said:
You can't refer to a variable directly in a query. You could write a
function that returns the value of the variable and call the function from
the query ...

Public varWhatever As Variant

Public Function GetWhatever As Variant
GetWhatever = varWhatever
End Function

SELECT SomeField, GetWhatever() AS Whatever FROM SomeTable

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Brian said:
Is there a way to reference public variables in queries? Right now, I
create
an invisible unbound control on the form, assign its value to the value of
the public variable, then reference the control in the query:
[Forms]![FormName]![ControlName]. It would be so much easier to avoid all
the
code and just use [PublicVariableName] in my query.

Any better methodolgies?
Click to expand...
Click to expand...
 
It depends. If you expect the form to be usually open, it probably won't
make any discernable difference. If you expect the form to be usually
closed, so that you have to open it before the query can run, the opening of
the form might cause a delay.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Brian said:
Thanks. Would you consider use of a function more efficient than the
invisible unbound control method I am currently using?

Brendan Reynolds said:
You can't refer to a variable directly in a query. You could write a
function that returns the value of the variable and call the function
from
the query ...

Public varWhatever As Variant

Public Function GetWhatever As Variant
GetWhatever = varWhatever
End Function

SELECT SomeField, GetWhatever() AS Whatever FROM SomeTable

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Brian said:
Is there a way to reference public variables in queries? Right now, I
create
an invisible unbound control on the form, assign its value to the value
of
the public variable, then reference the control in the query:
[Forms]![FormName]![ControlName]. It would be so much easier to avoid
all
the
code and just use [PublicVariableName] in my query.

Any better methodolgies?
Click to expand...
Click to expand...
Click to expand...
 
The query is run exclusively from user interaction on the form
(ButtonWhatever_Click Sub in form's VBA code), so I guess it makes sense to
keep it as is.

Thanks.

Brendan Reynolds said:
It depends. If you expect the form to be usually open, it probably won't
make any discernable difference. If you expect the form to be usually
closed, so that you have to open it before the query can run, the opening of
the form might cause a delay.

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Brian said:
Thanks. Would you consider use of a function more efficient than the
invisible unbound control method I am currently using?

Brendan Reynolds said:
You can't refer to a variable directly in a query. You could write a
function that returns the value of the variable and call the function
from
the query ...

Public varWhatever As Variant

Public Function GetWhatever As Variant
GetWhatever = varWhatever
End Function

SELECT SomeField, GetWhatever() AS Whatever FROM SomeTable

--
Brendan Reynolds (MVP)
http://brenreyn.blogspot.com

The spammers and script-kiddies have succeeded in making it impossible
for
me to use a real e-mail address in public newsgroups. E-mail replies to
this post will be deleted without being read. Any e-mail claiming to be
from brenreyn at indigo dot ie that is not digitally signed by me with a
GlobalSign digital certificate is a forgery and should be deleted without
being read. Follow-up questions should in general be posted to the
newsgroup, but if you have a good reason to send me e-mail, you'll find
a useable e-mail address at the URL above.


Is there a way to reference public variables in queries? Right now, I
create
an invisible unbound control on the form, assign its value to the value
of
the public variable, then reference the control in the query:
[Forms]![FormName]![ControlName]. It would be so much easier to avoid
all
the
code and just use [PublicVariableName] in my query.

Any better methodolgies?
Click to expand...
Click to expand...
Click to expand...
 
Back
Top