Use specific value from a table in multiple calculations/Excel Name Manager Equivalent in Access

[Cyclone_Rob]

Hi,

I'm fairly inexperienced in Access. I'm in the process of converting a series of very complicated Excel spreadsheet to an Access database. In that spreadsheet there are a list of ~20 constants that are used to determine how much of a given material will be used. For example, one constant is the weight of ink per square inch. The spreadsheet uses other inputs (area of paper being printed, % of the paper being covered) to calculate a weight of ink for one printing. For simplicity, using the Name Manager functionality, the constant is named "Ink" and can be used throughout the workbook for calculations by typing "Ink" into the formula. And because it is a referenced cell, if I need to change the value of the constant, I can with one click and the value will be updated in all calculations.

I want the same functionality in Access. This constant will be used in about a dozen queries so if the value ever changes and I use the "hard" number, I will have to comb back through all of the queries to update it (or more likely, someone else less computer literate will need to update it so I need a user friendly way to manage these constants).

I tried putting the ~20 constants in a table (named Constants) with column 1 being a 1-word description of each constant named Description, and column 2 is the numerical value and the column is titled K-value. Using this, I tried to pull values in using dlookup("K-value","Constants","Description="&"Ink") in the query expression builder but it did not work. Changed the syntax as well, so it was DLookUp([Constants]![K Value],[Constants],[Constants]![Description]="Ink") but then when I run the query/open it in spreadsheet view I get prompted for each of the values in Dlookup.

I like the idea of keeping the constants in a table as it will be user friendly to update the values. Does Access have anything I can use to simulate the name manager functionality in Excel?

Any ideas?

Thanks in advance.

[Cyclone_Rob]

Nevermind. I rotated my table 90 degress (instead of 2 columns and ~20 rows, I now have ~20 columns and just one row with each column labelled the specfic type of constant). I can now use the expression builder to pull each column (and since there's only one row) it pulls in the correct piece of information.

[Bulzie]

x = dlookup("K-value","Constants","Description= 'Ink'")

last part is (single quote)ink(single quote)(double quote)

[CJ_London]

On the basis the constants is effectively configuration data (i.e. you are not adding new constants), either way is doable. In your case the single row is probably the easier to manage. FYI, the syntax for the dlookup is wrong. Assuming it is in a query, it would be

DLookUp("[K Value]","[Constants]","[Description]='Ink'")

[Bulzie]

My test worked without the brackets on table or query.

[CJ_London]

it probably will, I was using the second example where there is a space in K Value, so brackets required. And description is a reserved word, so better to use the brackets than not.

Edit - the '-' in the field name is also not advised - coding may try to subtract 'Value' (also a reserved word) from 'K' and fail, using square brackets goes some way to preventing this from happening, but I have known cases where it has not.