Results 1 to 8 of 8
  1. #1
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Reusing a number in multiple places (Access 2002)

    I have a number that is referenced in a variety of places: Expressions, unbound textboxes, and formulas. Is there some way to enter it in one place and then reference it every where else? This number could change every year.

    Thank you for your time and help. Fay

  2. #2
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Reusing a number in multiple places (Access 2002)

    The way most people solve this is to create a hidden form which is opened when the database is started, and one or more controls on the form are used to store "global" parameters that are referenced in queries, in populating other controls on forms or reports, and as constants in VBA. If you are using the value just in VBA you can create a Global variable (or an array if you have several), and initialize it on startup. There are probably some other ways, such as storing a value in a table, but I think the hidden form is the most common.
    Wendell

  3. #3
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Reusing a number in multiple places (Access 2002)

    I use a Company table, which contains a single record that contains just such information (company name, tax rate, etc.). Since there is only a single record, it is easy to use DLookup to get what I want. For example, I can always get the tax rate by using DLookup("TaxRate","Company").
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  4. #4
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reusing a number in multiple places (Access 2002)

    Thanks guys for responding. I used a table and was able to pull the number into an unbound text box on a form and that worked well. Thanks, but as usual answers lead to more questions. So here goes.

    Another reused fact is the fiscal year (July to June). Which I have set in various queries as Between #7/1/2001# And #6/30/2002#. In a meeting yesterday I came away with having to track three different years: the fiscal year, a April to March, and June to May. I don't want to have to go in next year in umpteen places and changes those dates. Will the DLookup still work in those situations?

    How do I write the DLookup in a query. I have tried =DLookUp("JuneYear","tblCompanyFacts") and without the = in the criteria line. I get the message that: "Your tried to execute a query that does not include the specified expression..." I have the total criteria set to expression, otherwise I get a data type mismatch message.

    Next question what is your opinion table versus form to use as the base of the DLookup which is easier to handle?

    Thank you. Fay

  5. #5
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Reusing a number in multiple places (Access 2002)

    I use both DLookup and a hidden form to get info I need. The only problem with the form is that you for whatever reason, it may not be open. I use a Company form that I open hidden as part of my startup routine.

    As for your problem, DLookUp("JuneYear","tblCompanyFacts") . I can't offhand tell you what the problem is, because I don't know all the details. Like what is JuneYear? also, I'm assuming there is only a single record in tblCompanyFActs?

    You could use Dlookup as a field in your query, or as a selection criteria, so again I can't give you specific help unless I know the situation.
    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  6. #6
    5 Star Lounger
    Join Date
    Jul 2001
    Location
    NJ, USA
    Posts
    617
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Reusing a number in multiple places (Access 2002)

    Hi Mark thanks for your reply.

    JuneYear is a field in the tblCompanyFacts. The table has only one record. The data stored in that field reads "Between #6/1/2001# And 5/31/2001#". I need that string to be placed in the query field criteria row and act to restrict the records used to within those dates. Just as it would if you type the sting into the criteria row.

    Would you use the DLookup in the criteria row written like this [DLookup("JuneYear","tblCompanyFacts")]?

    Thanks again Mark. Fay

  7. #7
    Gold Lounger
    Join Date
    Jun 2001
    Location
    Crystal Beach, FL, Florida, USA
    Posts
    3,436
    Thanks
    1
    Thanked 34 Times in 34 Posts

    Re: Reusing a number in multiple places (Access 2002)

    you wrote:
    >>The data stored in that field reads "Between #6/1/2001# And 5/31/2001#"<<

    The won't work! Access tries to match this text string against the date field. You need 2 fields in your company record, a JuneYrBeginDate and a JuneYrEndDate. Your selection criteria would be:
    <pre>Between DLookup("JuneYrBeginDate","tblCompany") AND _
    DLookup("JuneYrEndDate","tblCompany")
    </pre>

    Mark Liquorman
    See my website for Tips & Downloads and for my Liquorman Utilities.

  8. #8
    Super Moderator
    Join Date
    Aug 2001
    Location
    Evergreen, CO, USA
    Posts
    6,623
    Thanks
    3
    Thanked 60 Times in 60 Posts

    Re: Reusing a number in multiple places (Access 2002)

    One small point regarding using DLookup - it is generally pretty slow, so you don't want to do lots of them. In your case as part of a criteria it works OK, but if you end up doing it in code, or as an expression in the results of a query that returns lots of records you will find it really sluggish. Hopefully you've got things working now.
    Wendell

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •