vlookup from excel equivalent function in access

I am trying to lookup a value in a table that has lengths in the first
columns and withdths across the field names and a value at the intersections
of those. this is a table for time it takes to drill a peice of sheetmetal
with a certain diameter hole in a certain thicknes metal. it has me boggled.
I can do it in excel with vlookup() function.

I THINK you wand DLookUp()
DLookUp("FieldYouWantToFind","Table/QueryWhereTheDataIs","FieldInTable=FieldOnForm")

That end part is different whether you are dealing with text or number
field.

"bartmet" wrote in message
...
I am trying to lookup a value in a table that has lengths in the first
columns and withdths across the field names and a value at the
intersections
of those. this is a table for time it takes to drill a peice of sheetmetal
with a certain diameter hole in a certain thicknes metal. it has me
boggled.
I can do it in excel with vlookup() function.

I have tried to use the dlookup and that seems to work if the lookup field is
static but I want ot be able to vary the field(or select a called for
field)called for ithin the record selected

"Gina Whipp" wrote:

I THINK you wand DLookUp()
DLookUp("FieldYouWantToFind","Table/QueryWhereTheDataIs","FieldInTable=FieldOnForm")

That end part is different whether you are dealing with text or number
field.

"bartmet" wrote in message
...
I am trying to lookup a value in a table that has lengths in the first
columns and withdths across the field names and a value at the
intersections
of those. this is a table for time it takes to drill a peice of sheetmetal
with a certain diameter hole in a certain thicknes metal. it has me
boggled.
I can do it in excel with vlookup() function.

I expect your issue is taking a spreadsheet attitude into a relational
database. If your "FieldYouWantToFind" is dynamic then consider normalizing
your table to where your where condition would include values for length and
width or whatever.

It would help if we knew your table structure with field names.

--
Duane Hookom
MS Access MVP


"bartmet" wrote in message
...
I have tried to use the dlookup and that seems to work if the lookup field
is
static but I want ot be able to vary the field(or select a called for
field)called for ithin the record selected

"Gina Whipp" wrote:

I THINK you wand DLookUp()
DLookUp("FieldYouWantToFind","Table/QueryWhereTheDataIs","FieldInTable=FieldOnForm")

That end part is different whether you are dealing with text or number
field.

"bartmet" wrote in message
...
I am trying to lookup a value in a table that has lengths in the first
columns and withdths across the field names and a value at the
intersections
of those. this is a table for time it takes to drill a peice of
sheetmetal
with a certain diameter hole in a certain thicknes metal. it has me
boggled.
I can do it in excel with vlookup() function.

this should show up ok if you copy and paste the following into excel. I need
to look up the time to drill a hole based on diameter of the hole and
thicknessof the steel if the hole is 0.5 inches in diameter and 5/8 inch
thick the time is 1.25 minutes from this sample sheet.

"column diameter(in)/row headers
depth(eigth inch increments/values(minutes)" 1 2 3 4 5 6 7 8
0.125 0.25 0.25 0.25 0.25 0.5 0.5 0.5 0.5
0.25 0.25 0.25 0.25 0.5 0.5 0.5 0.75 0.75
0.375 0.75 0.75 0.75 1 1 1 1 1.25
0.5 1 1 1 1.25 1.25 1.25 1.25 1.25
0.625 1.25 1.25 1.25 1.625 1.5 1.5 1.5 1.625
0.75 1.525 1.525 1.525 1.975 1.775 1.775 1.75 1.9
0.875 1.8 1.8 1.8 2.325 2.05 2.05 2 2.175
1 2.075 2.075 2.075 2.675 2.325 2.325 2.25 2.45
1.125 2.35 2.35 2.35 3.025 2.6 2.6 2.5 2.725
1.25 2.625 2.625 2.625 3.375 2.875 2.875 2.75 3
1.375 2.9 2.9 2.9 3.725 3.15 3.15 3 3.275


"Duane Hookom" wrote:

I expect your issue is taking a spreadsheet attitude into a relational
database. If your "FieldYouWantToFind" is dynamic then consider normalizing
your table to where your where condition would include values for length and
width or whatever.

It would help if we knew your table structure with field names.

--
Duane Hookom
MS Access MVP


"bartmet" wrote in message
...
I have tried to use the dlookup and that seems to work if the lookup field
is
static but I want ot be able to vary the field(or select a called for
field)called for ithin the record selected

"Gina Whipp" wrote:

I THINK you wand DLookUp()
DLookUp("FieldYouWantToFind","Table/QueryWhereTheDataIs","FieldInTable=FieldOnForm")

That end part is different whether you are dealing with text or number
field.

"bartmet" wrote in message
...
I am trying to lookup a value in a table that has lengths in the first
columns and withdths across the field names and a value at the
intersections
of those. this is a table for time it takes to drill a peice of
sheetmetal
with a certain diameter hole in a certain thicknes metal. it has me
boggled.
I can do it in excel with vlookup() function.

You have committed spreadsheet with your table. I assume your 1 2 3...are
fields? These should be data values stored in a field. It's hard to tell but
it looks like you should have about 100 records from the information you
provided.

--
Duane Hookom
MS Access MVP
--

"bartmet" wrote in message
...
this should show up ok if you copy and paste the following into excel. I
need
to look up the time to drill a hole based on diameter of the hole and
thicknessof the steel if the hole is 0.5 inches in diameter and 5/8 inch
thick the time is 1.25 minutes from this sample sheet.

"column diameter(in)/row headers
depth(eigth inch increments/values(minutes)" 1 2 3 4 5 6 7 8
0.125 0.25 0.25 0.25 0.25 0.5 0.5 0.5 0.5
0.25 0.25 0.25 0.25 0.5 0.5 0.5 0.75 0.75
0.375 0.75 0.75 0.75 1 1 1 1 1.25
0.5 1 1 1 1.25 1.25 1.25 1.25 1.25
0.625 1.25 1.25 1.25 1.625 1.5 1.5 1.5 1.625
0.75 1.525 1.525 1.525 1.975 1.775 1.775 1.75 1.9
0.875 1.8 1.8 1.8 2.325 2.05 2.05 2 2.175
1 2.075 2.075 2.075 2.675 2.325 2.325 2.25 2.45
1.125 2.35 2.35 2.35 3.025 2.6 2.6 2.5 2.725
1.25 2.625 2.625 2.625 3.375 2.875 2.875 2.75 3
1.375 2.9 2.9 2.9 3.725 3.15 3.15 3 3.275


"Duane Hookom" wrote:

I expect your issue is taking a spreadsheet attitude into a relational
database. If your "FieldYouWantToFind" is dynamic then consider
normalizing
your table to where your where condition would include values for length
and
width or whatever.

It would help if we knew your table structure with field names.

--
Duane Hookom
MS Access MVP


"bartmet" wrote in message
...
I have tried to use the dlookup and that seems to work if the lookup
field
is
static but I want ot be able to vary the field(or select a called for
field)called for ithin the record selected

"Gina Whipp" wrote:

I THINK you wand DLookUp()
DLookUp("FieldYouWantToFind","Table/QueryWhereTheDataIs","FieldInTable=FieldOnForm")

That end part is different whether you are dealing with text or number
field.

"bartmet" wrote in message
...
I am trying to lookup a value in a table that has lengths in the
first
columns and withdths across the field names and a value at the
intersections
of those. this is a table for time it takes to drill a peice of
sheetmetal
with a certain diameter hole in a certain thicknes metal. it has me
boggled.
I can do it in excel with vlookup() function.

no there are only 8 records and the 1,2,3 are field names like I said if you
copy the array I put in there and paste it into excell it should come out as
a table.

"Duane Hookom" wrote:

You have committed spreadsheet with your table. I assume your 1 2 3...are
fields? These should be data values stored in a field. It's hard to tell but
it looks like you should have about 100 records from the information you
provided.

--
Duane Hookom
MS Access MVP
--

"bartmet" wrote in message
...
this should show up ok if you copy and paste the following into excel. I
need
to look up the time to drill a hole based on diameter of the hole and
thicknessof the steel if the hole is 0.5 inches in diameter and 5/8 inch
thick the time is 1.25 minutes from this sample sheet.

"column diameter(in)/row headers
depth(eigth inch increments/values(minutes)" 1 2 3 4 5 6 7 8
0.125 0.25 0.25 0.25 0.25 0.5 0.5 0.5 0.5
0.25 0.25 0.25 0.25 0.5 0.5 0.5 0.75 0.75
0.375 0.75 0.75 0.75 1 1 1 1 1.25
0.5 1 1 1 1.25 1.25 1.25 1.25 1.25
0.625 1.25 1.25 1.25 1.625 1.5 1.5 1.5 1.625
0.75 1.525 1.525 1.525 1.975 1.775 1.775 1.75 1.9
0.875 1.8 1.8 1.8 2.325 2.05 2.05 2 2.175
1 2.075 2.075 2.075 2.675 2.325 2.325 2.25 2.45
1.125 2.35 2.35 2.35 3.025 2.6 2.6 2.5 2.725
1.25 2.625 2.625 2.625 3.375 2.875 2.875 2.75 3
1.375 2.9 2.9 2.9 3.725 3.15 3.15 3 3.275


"Duane Hookom" wrote:

I expect your issue is taking a spreadsheet attitude into a relational
database. If your "FieldYouWantToFind" is dynamic then consider
normalizing
your table to where your where condition would include values for length
and
width or whatever.

It would help if we knew your table structure with field names.

--
Duane Hookom
MS Access MVP


"bartmet" wrote in message
...
I have tried to use the dlookup and that seems to work if the lookup
field
is
static but I want ot be able to vary the field(or select a called for
field)called for ithin the record selected

"Gina Whipp" wrote:

I THINK you wand DLookUp()
DLookUp("FieldYouWantToFind","Table/QueryWhereTheDataIs","FieldInTable=FieldOnForm")

That end part is different whether you are dealing with text or number
field.

"bartmet" wrote in message
...
I am trying to lookup a value in a table that has lengths in the
first
columns and withdths across the field names and a value at the
intersections
of those. this is a table for time it takes to drill a peice of
sheetmetal
with a certain diameter hole in a certain thicknes metal. it has me
boggled.
I can do it in excel with vlookup() function.

Like I have said at least twice, you should be normalizing this spreadsheet
into at table where 1,2,3... are values in a field rather than fields in a
table.

--
Duane Hookom
MS Access MVP


"bartmet" wrote in message
...
no there are only 8 records and the 1,2,3 are field names like I said if
you
copy the array I put in there and paste it into excell it should come out
as
a table.

"Duane Hookom" wrote:

You have committed spreadsheet with your table. I assume your 1 2 3...are
fields? These should be data values stored in a field. It's hard to tell
but
it looks like you should have about 100 records from the information you
provided.

--
Duane Hookom
MS Access MVP
--

"bartmet" wrote in message
...
this should show up ok if you copy and paste the following into excel.
I
need
to look up the time to drill a hole based on diameter of the hole and
thicknessof the steel if the hole is 0.5 inches in diameter and 5/8
inch
thick the time is 1.25 minutes from this sample sheet.

"column diameter(in)/row headers
depth(eigth inch increments/values(minutes)" 1 2 3 4 5 6 7 8
0.125 0.25 0.25 0.25 0.25 0.5 0.5 0.5 0.5
0.25 0.25 0.25 0.25 0.5 0.5 0.5 0.75 0.75
0.375 0.75 0.75 0.75 1 1 1 1 1.25
0.5 1 1 1 1.25 1.25 1.25 1.25 1.25
0.625 1.25 1.25 1.25 1.625 1.5 1.5 1.5 1.625
0.75 1.525 1.525 1.525 1.975 1.775 1.775 1.75 1.9
0.875 1.8 1.8 1.8 2.325 2.05 2.05 2 2.175
1 2.075 2.075 2.075 2.675 2.325 2.325 2.25 2.45
1.125 2.35 2.35 2.35 3.025 2.6 2.6 2.5 2.725
1.25 2.625 2.625 2.625 3.375 2.875 2.875 2.75 3
1.375 2.9 2.9 2.9 3.725 3.15 3.15 3 3.275


"Duane Hookom" wrote:

I expect your issue is taking a spreadsheet attitude into a relational
database. If your "FieldYouWantToFind" is dynamic then consider
normalizing
your table to where your where condition would include values for
length
and
width or whatever.

It would help if we knew your table structure with field names.

--
Duane Hookom
MS Access MVP


"bartmet" wrote in message
...
I have tried to use the dlookup and that seems to work if the lookup
field
is
static but I want ot be able to vary the field(or select a called
for
field)called for ithin the record selected

"Gina Whipp" wrote:

I THINK you wand DLookUp()
DLookUp("FieldYouWantToFind","Table/QueryWhereTheDataIs","FieldInTable=FieldOnForm")

That end part is different whether you are dealing with text or
number
field.

"bartmet" wrote in message
...
I am trying to lookup a value in a table that has lengths in the
first
columns and withdths across the field names and a value at the
intersections
of those. this is a table for time it takes to drill a peice of
sheetmetal
with a certain diameter hole in a certain thicknes metal. it has
me
boggled.
I can do it in excel with vlookup() function.

thanks for your patience with me I guess the term normalizing threw me. what
do you mean by that i looked it up in Access and got nothing. I have a dozen
of the type of tables to do.

"Duane Hookom" wrote:

Like I have said at least twice, you should be normalizing this spreadsheet
into at table where 1,2,3... are values in a field rather than fields in a
table.

--
Duane Hookom
MS Access MVP


"bartmet" wrote in message
...
no there are only 8 records and the 1,2,3 are field names like I said if
you
copy the array I put in there and paste it into excell it should come out
as
a table.

"Duane Hookom" wrote:

You have committed spreadsheet with your table. I assume your 1 2 3...are
fields? These should be data values stored in a field. It's hard to tell
but
it looks like you should have about 100 records from the information you
provided.

--
Duane Hookom
MS Access MVP
--

"bartmet" wrote in message
...
this should show up ok if you copy and paste the following into excel.
I
need
to look up the time to drill a hole based on diameter of the hole and
thicknessof the steel if the hole is 0.5 inches in diameter and 5/8
inch
thick the time is 1.25 minutes from this sample sheet.

"column diameter(in)/row headers
depth(eigth inch increments/values(minutes)" 1 2 3 4 5 6 7 8
0.125 0.25 0.25 0.25 0.25 0.5 0.5 0.5 0.5
0.25 0.25 0.25 0.25 0.5 0.5 0.5 0.75 0.75
0.375 0.75 0.75 0.75 1 1 1 1 1.25
0.5 1 1 1 1.25 1.25 1.25 1.25 1.25
0.625 1.25 1.25 1.25 1.625 1.5 1.5 1.5 1.625
0.75 1.525 1.525 1.525 1.975 1.775 1.775 1.75 1.9
0.875 1.8 1.8 1.8 2.325 2.05 2.05 2 2.175
1 2.075 2.075 2.075 2.675 2.325 2.325 2.25 2.45
1.125 2.35 2.35 2.35 3.025 2.6 2.6 2.5 2.725
1.25 2.625 2.625 2.625 3.375 2.875 2.875 2.75 3
1.375 2.9 2.9 2.9 3.725 3.15 3.15 3 3.275


"Duane Hookom" wrote:

I expect your issue is taking a spreadsheet attitude into a relational
database. If your "FieldYouWantToFind" is dynamic then consider
normalizing
your table to where your where condition would include values for
length
and
width or whatever.

It would help if we knew your table structure with field names.

--
Duane Hookom
MS Access MVP


"bartmet" wrote in message
...
I have tried to use the dlookup and that seems to work if the lookup
field
is
static but I want ot be able to vary the field(or select a called
for
field)called for ithin the record selected

"Gina Whipp" wrote:

I THINK you wand DLookUp()
DLookUp("FieldYouWantToFind","Table/QueryWhereTheDataIs","FieldInTable=FieldOnForm")

That end part is different whether you are dealing with text or
number
field.

"bartmet" wrote in message
...
I am trying to lookup a value in a table that has lengths in the
first
columns and withdths across the field names and a value at the
intersections
of those. this is a table for time it takes to drill a peice of
sheetmetal
with a certain diameter hole in a certain thicknes metal. it has
me
boggled.
I can do it in excel with vlookup() function.

Jeff Conrad has some excellent links to various web resources regarding
normalizing
http://home.bendbroadband.com/conrad...abaseDesign101

--
Duane Hookom
MS Access MVP


"bartmet" wrote in message
...
thanks for your patience with me I guess the term normalizing threw me.
what
do you mean by that i looked it up in Access and got nothing. I have a
dozen
of the type of tables to do.

"Duane Hookom" wrote:

Like I have said at least twice, you should be normalizing this
spreadsheet
into at table where 1,2,3... are values in a field rather than fields in
a
table.

--
Duane Hookom
MS Access MVP


"bartmet" wrote in message
...
no there are only 8 records and the 1,2,3 are field names like I said
if
you
copy the array I put in there and paste it into excell it should come
out
as
a table.

"Duane Hookom" wrote:

You have committed spreadsheet with your table. I assume your 1 2
3...are
fields? These should be data values stored in a field. It's hard to
tell
but
it looks like you should have about 100 records from the information
you
provided.

--
Duane Hookom
MS Access MVP
--

"bartmet" wrote in message
...
this should show up ok if you copy and paste the following into
excel.
I
need
to look up the time to drill a hole based on diameter of the hole
and
thicknessof the steel if the hole is 0.5 inches in diameter and 5/8
inch
thick the time is 1.25 minutes from this sample sheet.

"column diameter(in)/row headers
depth(eigth inch increments/values(minutes)" 1 2 3 4 5 6 7 8
0.125 0.25 0.25 0.25 0.25 0.5 0.5 0.5 0.5
0.25 0.25 0.25 0.25 0.5 0.5 0.5 0.75 0.75
0.375 0.75 0.75 0.75 1 1 1 1 1.25
0.5 1 1 1 1.25 1.25 1.25 1.25 1.25
0.625 1.25 1.25 1.25 1.625 1.5 1.5 1.5 1.625
0.75 1.525 1.525 1.525 1.975 1.775 1.775 1.75 1.9
0.875 1.8 1.8 1.8 2.325 2.05 2.05 2 2.175
1 2.075 2.075 2.075 2.675 2.325 2.325 2.25 2.45
1.125 2.35 2.35 2.35 3.025 2.6 2.6 2.5 2.725
1.25 2.625 2.625 2.625 3.375 2.875 2.875 2.75 3
1.375 2.9 2.9 2.9 3.725 3.15 3.15 3 3.275


"Duane Hookom" wrote:

I expect your issue is taking a spreadsheet attitude into a
relational
database. If your "FieldYouWantToFind" is dynamic then consider
normalizing
your table to where your where condition would include values for
length
and
width or whatever.

It would help if we knew your table structure with field names.

--
Duane Hookom
MS Access MVP


"bartmet" wrote in message
...
I have tried to use the dlookup and that seems to work if the
lookup
field
is
static but I want ot be able to vary the field(or select a called
for
field)called for ithin the record selected

"Gina Whipp" wrote:

I THINK you wand DLookUp()
DLookUp("FieldYouWantToFind","Table/QueryWhereTheDataIs","FieldInTable=FieldOnForm")

That end part is different whether you are dealing with text or
number
field.

"bartmet" wrote in message
...
I am trying to lookup a value in a table that has lengths in
the
first
columns and withdths across the field names and a value at the
intersections
of those. this is a table for time it takes to drill a peice
of
sheetmetal
with a certain diameter hole in a certain thicknes metal. it
has
me
boggled.
I can do it in excel with vlookup() function.