Global Variable

[gtg430i]

Hello - I am sure this was answered many times before, but I can't figure out how I can use it in my situation.

Basically - I have an access database with many different queries. Each query has a column called Run Date. Each time time I run a query I go to Design View and put the date I need in the Run Date column (for example :03/05/2014).

The thing is I need to change this in every query. Is there a simple way with which I can create a global variable in a module and then just set the global to be a certain date which gets reflected in all query with the variable name?



I do not need to use any sort of forms.

Your help is appreciated.

Thanks

[pbaldy]

I'd use a form, but failing that you can create a public function that simply returns the value of the variable. You can't refer to the variable directly from outside VBA, hence the function.

[gtg430i]

I do not want to use a form. I want to write the variable name in the column in the design view and assign a value to this variable in the module.

[pbaldy]

Fine, I already told you the path you'd have to take.

[gtg430i]

Hello - Could you please help me build it from scratch? I have no clue how?

Thanks

[June7]

[Run Date] is a field created with expression so that every record will have this date value?

Calling a public function where user is prompted to input a date is one way.

However, a parameter input prompt in the query accomplishes the same.

SELECT *, [enter date value] AS [Run Date] FROM table;

The difference is function can have code to validate the user input.

[pbaldy]

I'll stay out of the kitchen.

[gtg430i]

Hello,

Run Date is an existing column in my table. Everytime I upload new data to the table, I run a query that update the "Run Date" column to today's date.

What I need is say I need to return all data subject to Run Date >= 03/10/2013. I need a global variable to follow the following: Run Date >= MyVariable

Please note I do not want to use a form. I want to put the variable name is the criteria column in the Design View.

Thanks

[June7]

Then:

SELECT * FROM table WHERE [Run Date] >= [Enter date value];

This will generate an input popup prompt for user to enter date.

Calling a function in place of [Enter date value] is possible but essentially the same from the user point of view.

If user inputs an invalid value, the query will still try to run. A function could manage that somewhat to try and prevent invalid entry.

[gtg430i]

That worked exactly as needed

Thank you very much!

[Missinglinq]

...I'll stay out of the kitchen...

Can't blame you, Paul!

...I do not want to use a form...

..Could you please help me build it from scratch...

In other words, "I don't want to take your advice, and do it, as you advised...but I don't want to do the work, either! I want you to actually do the work!"

Is that about it?

[June7]

Using a form would mean entering the date only once. Then open as many queries as you want while the form is open.