How to enter a public variable into a Query

[Guest]

Hi
I have two public variables that get changed by various forms.
I am trying to use the Public variables PubStartDate and PubEndDate as
critera of a query like this --- Between PubStartDate And PubEndDate ---
to give me a date range of a field named QuoDate in a table called tblQuoteNo
It does not seem to work
How do you enter a public variable into the criteria of a query Access
always changes the variable to a text string.
can anybody please help.
Steve

 

[John Vinson]

How do you enter a public variable into the criteria of a query Access
always changes the variable to a text string.

SQL is one language; VBA is a different one. SQL does not have any way
of seeing the variables in a VBA project.

What you can do is create a little wrapper function: e.g.

Public Function GetStartDate() As Variant
GetStartDate = PubStartDate
End Sub

and use GetStartDate() as your query criterion.

Better... don't do it this way! Instead, have the user enter the start
and end dates into unbound controls (txtStartDate and txtEndDate) on a
Form (frmCrit) and use

BETWEEN Forms![frmCrit]![txtStartDate] AND
Forms![frmCrit]![txtEndDate]


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[Guest]

Thanks John
The Public variablles came from an entry form, but I had to keep the form
open while the query was being updated and so it cluttered up the screen and
blocked information being viewed on the main form. So I thought making the
viariables public would do this but of course you are right and SQL wont see
them. I did not know this and so I thank you for this information. I will go
back to having the entry form open and then pass the entry boxes to the query.

Thanks again
Steve

How do you enter a public variable into the criteria of a query Access
always changes the variable to a text string.

SQL is one language; VBA is a different one. SQL does not have any way
of seeing the variables in a VBA project.

What you can do is create a little wrapper function: e.g.

Public Function GetStartDate() As Variant
GetStartDate = PubStartDate
End Sub

and use GetStartDate() as your query criterion.

Better... don't do it this way! Instead, have the user enter the start
and end dates into unbound controls (txtStartDate and txtEndDate) on a
Form (frmCrit) and use

BETWEEN Forms![frmCrit]![txtStartDate] AND
Forms![frmCrit]![txtEndDate]


John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps

 

[John Vinson]

The Public variablles came from an entry form, but I had to keep the form
open while the query was being updated and so it cluttered up the screen and
blocked information being viewed on the main form.

Just set the Visible property of the form to False. It will still be
there, the controls will still have their values, but they won't
clutter the screen.

John W. Vinson[MVP]
Join the online Access Chats
Tuesday 11am EDT - Thursday 3:30pm EDT
http://community.compuserve.com/msdevapps