Results 1 to 12 of 12
  1. #1
    gtg430i is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    10

    Global Variable

    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

  2. #2
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,965
    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.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  3. #3
    gtg430i is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    10
    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.

  4. #4
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,965
    Fine, I already told you the path you'd have to take.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  5. #5
    gtg430i is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    10
    Hello - Could you please help me build it from scratch? I have no clue how?

    Thanks

  6. #6
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,875
    [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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  7. #7
    pbaldy's Avatar
    pbaldy is online now Who is John Galt?
    Windows XP Access 2007
    Join Date
    Feb 2010
    Location
    Nevada, USA
    Posts
    20,965
    I'll stay out of the kitchen.
    Paul (wino moderator)
    MS Access MVP 2007-2019
    www.BaldyWeb.com

  8. #8
    gtg430i is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    10
    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

  9. #9
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,875
    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.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

  10. #10
    gtg430i is offline Novice
    Windows 7 64bit Access 2007
    Join Date
    Feb 2014
    Posts
    10
    That worked exactly as needed

    Thank you very much!

  11. #11
    Missinglinq's Avatar
    Missinglinq is offline VIP
    Windows 7 64bit Access 2007
    Join Date
    May 2012
    Location
    Richmond (Virginia, not North Yorkshire!)
    Posts
    2,924
    Quote Originally Posted by pbaldy View Post

    ...I'll stay out of the kitchen...
    Can't blame you, Paul!

    Quote Originally Posted by gtg430i View Post

    ...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?
    The problem with making anything foolproof...is that fools are so darn ingenious!

    All posts/responses based on Access 2003/2007

  12. #12
    June7's Avatar
    June7 is online now VIP
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    The Great Land
    Posts
    48,875
    Using a form would mean entering the date only once. Then open as many queries as you want while the form is open.
    How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Can't See Global Variable
    By CementCarver in forum Programming
    Replies: 12
    Last Post: 09-19-2013, 12:28 PM
  2. How to use global/public variable
    By mrbabji in forum Programming
    Replies: 7
    Last Post: 05-18-2013, 10:08 PM
  3. Global variable
    By ramdandi in forum Queries
    Replies: 3
    Last Post: 12-18-2011, 01:01 AM
  4. Replies: 4
    Last Post: 07-14-2011, 12:55 PM
  5. How to declare Global Variable
    By ganeshvenkatram in forum Access
    Replies: 1
    Last Post: 06-16-2011, 05:18 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Tech Forums: Microsoft Office Forums