search programming problem(really complicated) 2

[nycdata]

Hi,

I have a very complicated programming problem here..can't solve it...

I'll explain how it goes...

i have a table table1 with field names

id one two three four five

and it lays out like this

id one two three four five
1 23 34 32 54 56
2 34 76 46 85 96

and another table table2 with a field called id and one called list

that list has values
one
two
three
four
five

now for id 2 if i have list item "two" selected

then i have to search table1 for id 2 and the value in field two of id 2 of table1

is that possible??

ANY HELP GREATLY APPRECIATED !!!

 

[PHV]

Have you tried to create a saved union query, say qryUnion1:
SELECT id, "one" As list, one As aValue FROM table1
UNION SELECT id, "two", two FROM table1
UNION SELECT id, "three", three FROM table1
UNION SELECT id, "four", four FROM table1
UNION SELECT id, "five", five FROM table1;
And now you search qryUnion1 for the field aValue where id=2 and list="two"
i.e. you may join table2 and qryUnion1 on the id and list fields.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[Zion7]

Well, how do you need to gather the result? Do you need to perform a filter, on a recordsource, or simply obtain the result, of a single field?

Dim vResult as Variant 'choose variant, if search on different fields

vResult = DLookUp("two","TableName", "id = " & lstItem)

I gathered that you're doing a search from a listbox?
-two is the fieldName, you want the result from.
-tABLEnAME is the name of the table, field two is from
-lstItem is the name of listbox, you enter search criteria.
-this method returns, a single field result "two".

other method
Dim SQL as String
Dim rec as ADODB.Recordset
Set rec = New ADODB.Recordset

SQL = "SELECT * FROM tblName " & _
"WHERE ID =" & lstName

rec.Open SQL, CurrentProject.Connection, acOpenDynaset, acLockOptimistic

-This will open a recordset of all the fields in record Id Matching list selection.
to reference each field use
rec!FieldName....rec!two or rec!three

If I've missed your point, please elaborate.

Hope this helps, good luck!

 

[nycdata]

Thanks a lot guys! That should help!