How to perform lookup on 2D array using field names

[DrDave3144]

I am writing a program that utilizes multiple instances of lookups in 2 dimensional arrays (see below). My current approach, which doesn't work, is to use a SELECT INTO statement in SQL which leaves me with a table with a single value for each field (asset). I then wanted to select whatever value is in the field (fields are: 1,2,3,4, or 5)by passing the field name as a variable...The variable (field name) comes from an assessment value. The code that I try to use to accomplish this is below, the array is attached.

Example: I want to return the value from the field "4" for the Asset "Table" (The result should be 42).


Class Asset Life 1 2 3 4 5
1 Act 20 0 2 10 14 19
2 AV 35 0 4 18 25 33
3 Bldg 60 0 6 30 42 57
4 Comms 20 0 2 10 14 19
5 Fnc 35 0 4 18 25 33
6 Instr 25 0 3 12 18 24
7 Land 300 0 0 0 0 0
8 Lights 35 0 4 18 25 33
9 Motors 35 0 4 18 25 33
10 Piping 60 0 6 30 42 57
11 Power 35 0 4 18 25 33
12 Pumps 40 0 4 20 28 38
13 Secur 30 0 3 15 27 28
14 Valves 30 0 3 15 27 28
15 Vault 75 0 8 38 53 71
16 Vents 30 0 3 15 27 28
17 Venturi 40 0 4 20 28 38



' tmpBldConAge is the temprary table that is created and
'populated with the values from "Class = 3"
'CBefLife is the Table above
'intBuildingCode = 3
'intBuildingCondition trys to pass the value 4

strSQL = "SELECT * INTO tmpBldConAge " _
& "FROM CBefLife " _
& "WHERE (Class =" & intBuildingCode & ")"

DoCmd.SetWarnings False
DoCmd.RunSQL strSQL


'The following 2 lines don't work
'strCAText = "Tables!tmpBldConAge." & intBuildingCond
'intCondAge = strCAText

'The following 2 lines don't work either
intCondAge = "Tables!tmpBldConAge." & intBuildingCond


strSQL = "DROP TABLE tmpBldConAge;"
DoCmd.RunSQL strSQL
DoCmd.SetWarnings True


I hope someone knows how I can accomplish this ...

THANK YOU VERY MUCH for your help!

Dave

 

[PHV]

Have a look to the DLookUp function.

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[SeeThru]

This exists as an array?

You can loop thruogh the array

function getVal(column as variant, row as variant)

fox x=1 to Ubound(myarray)
if myarray(x,2)=column then
getval=myarray(x,row)
end if

This probably won't work - not sure if I have columns and rows the right way) but might help.


SeeThru
Synergy Connections Ltd - Uk Telemarketing and Telesales Services and
Synergy Mobile Solutions - UK Mobile phones, land lines and call packages