Reusing a number in multiple places (Access 2002)

Topic

New Reply

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

Reply | Quote

Replies

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.

Reply | Quote

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”).

Reply | Quote

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

Reply | Quote

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.

Reply | Quote

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

Reply | Quote

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:

Between DLookup("JuneYrBeginDate","tblCompany") AND _
     DLookup("JuneYrEndDate","tblCompany")

Reply | Quote

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.

Reply | Quote