Lookup issues Please help!

[hothouse]

Hello, I am seeking assistance on a confounding problem. I am still learning Access, so any assistance is appreciated. I am trying to look up a value in a query from a table based on two criteria (column and row). I want to be able to run a query to return a depreciation value (like 50% or .5) of an asset based on its age and type.

Depreciation table has property type codes 1 to 7 being the column headers (corresponds with things like computers, furniture), with the most left column being years 1985 to 2019.

The inventory table has assets, that includes a column for year and a column for asset type code (1-7).

When I try this in a query I receive an error: Lookit: DLookUp("[inventory]!

","Depreciation","[year] =  " & [inventory]![year])

Any help would be greatly appreciated. Thank you.

 

[theDBguy]

Hi. Welcome to AWF!


If you could change your lookup table structure so that you have a Type Code column (just one, not many columns), you would have a better way of looking up the value. For example, instead of:

Type1, Type2, Type3, etc.

Try:

TypeCode
Type1
Type2
Type3
etc.

 

[Deleted member 147267]

Agree that you table structure seems wrong (i.e. it isn't normalized). You were probably wearing your Excel thinking cap when you designed this db. Regardless, the problem with your expression is the syntax is wrong. You must have researched it in order to know about DLookup? It requires Domain,Field,Criteria as parameters. [inventory]!

 is a domain AND a field reference as one parameter. Unfortunately you are using reserved words in your object names as well. Maybe check out

Normalization Parts I, II, III, IV, and V 
[URL="http://rogersaccessblog.blogspot.com/2017/03/what-is-normalization-part-i.html"]http://rogersaccessblog.blogspot.com/2017/03/what-is-normalization-part-i.html [/URL]

and/or 

[URL="http://holowczak.com/database-normalization/"]http://holowczak.com/database-normalization/  [/URL]

 Naming conventions - 

[URL="http://access.mvps.org/access/general/gen0012.htm"]http://access.mvps.org/access/general/gen0012.htm [/URL]
 [URL="https://www.access-programmers.co.uk/forums/showthread.php?t=225837"]https://www.access-programmers.co.uk/forums/showthread.php?t=225837  [/URL]

[COLOR=Red][B]What not to use in names[/B][/COLOR] - [URL]http://allenbrowne.com/AppIssueBadWord.html[/URL]