Look up value

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

TableOne lists 1000 Material Codes (primary key). TableTwo contains a field
called MatlCode.

What I need is a way, in a query, to look in TableOne and if the MatlCode in
a record in TableTwo is in the list of 1000 records in TableOne then enter
that code, if it's not in the list in TableOne then enter "Default".

I'm just drawing a blank. Thanks -
 
Check out Nz and DLookup functions to do what you want:

LookedUpValue: Nz(DLookup("MatlCode", "TableOne", "[LinkingFieldName]=" &
[TableTwo].[LinkingFieldName]), "Default")
 
Thanks Ken. I am still unsuccessful. I have used the example you gave me and
examples from the Access Bible and from the Help System and I'm still
struggling. I keep getting "Error" in my field and Data Type Mismatch msg
boxes.

The following is my SQL. I don't have TableOne showing in the query, but
it's my understanding that I can lookup values in a table that is not open.

TableOne field is MaterialCode. TableTwo field is MATCODE, For MC: in my
query I need to show, for every record in TableTwo the name of the MATCODE if
it exists in TableOne. If there are no matches to MATCODE in TableOne I need
the word "default" to show in the MC field in my query. Below is my SQL - am
I close at all?

Thanks so much

Ken Snell said:
Check out Nz and DLookup functions to do what you want:

LookedUpValue: Nz(DLookup("MatlCode", "TableOne", "[LinkingFieldName]=" &
[TableTwo].[LinkingFieldName]), "Default")

--

Ken Snell
<MS ACCESS MVP>



Alex said:
TableOne lists 1000 Material Codes (primary key). TableTwo contains a field
called MatlCode.

What I need is a way, in a query, to look in TableOne and if the MatlCode in
a record in TableTwo is in the list of 1000 records in TableOne then enter
that code, if it's not in the list in TableOne then enter "Default".

I'm just drawing a blank. Thanks -
Click to expand...
Click to expand...
 
You didn't post your SQL statement?


--

Ken Snell
<MS ACCESS MVP>

Alex said:
Thanks Ken. I am still unsuccessful. I have used the example you gave me and
examples from the Access Bible and from the Help System and I'm still
struggling. I keep getting "Error" in my field and Data Type Mismatch msg
boxes.

The following is my SQL. I don't have TableOne showing in the query, but
it's my understanding that I can lookup values in a table that is not open.

TableOne field is MaterialCode. TableTwo field is MATCODE, For MC: in my
query I need to show, for every record in TableTwo the name of the MATCODE if
it exists in TableOne. If there are no matches to MATCODE in TableOne I need
the word "default" to show in the MC field in my query. Below is my SQL - am
I close at all?

Thanks so much

Ken Snell said:
Check out Nz and DLookup functions to do what you want:

LookedUpValue: Nz(DLookup("MatlCode", "TableOne", "[LinkingFieldName]=" &
[TableTwo].[LinkingFieldName]), "Default")

--

Ken Snell
<MS ACCESS MVP>



Alex said:
TableOne lists 1000 Material Codes (primary key). TableTwo contains a field
called MatlCode.

What I need is a way, in a query, to look in TableOne and if the
Click to expand...
MatlCode
in
a record in TableTwo is in the list of 1000 records in TableOne then enter
that code, if it's not in the list in TableOne then enter "Default".

I'm just drawing a blank. Thanks -
Click to expand...
Click to expand...
Click to expand...
 
Sorry Ken, here it is. Thanks so much for your help.


SELECT TableTwo.MODEL, GeneralInformation.ProductFamily AS COMMCODE,
TableTwo.MATCODE, DLookUp("[Material Code]","[TableOne]","[Material Code]=" &
[TableTwo].[MATCODE]) AS MC, FROM (TableTwo INNER JOIN GeneralInformation ON
TableTwo.MODEL = GeneralInformation.Model) INNER JOIN LeadTime ON
GeneralInformation.Model = LeadTime.Model;


Ken Snell said:
You didn't post your SQL statement?


--

Ken Snell
<MS ACCESS MVP>

Alex said:
Thanks Ken. I am still unsuccessful. I have used the example you gave me and
examples from the Access Bible and from the Help System and I'm still
struggling. I keep getting "Error" in my field and Data Type Mismatch msg
boxes.

The following is my SQL. I don't have TableOne showing in the query, but
it's my understanding that I can lookup values in a table that is not open.

TableOne field is MaterialCode. TableTwo field is MATCODE, For MC: in my
query I need to show, for every record in TableTwo the name of the MATCODE if
it exists in TableOne. If there are no matches to MATCODE in TableOne I need
the word "default" to show in the MC field in my query. Below is my SQL - am
I close at all?

Thanks so much

Ken Snell said:
Check out Nz and DLookup functions to do what you want:

LookedUpValue: Nz(DLookup("MatlCode", "TableOne", "[LinkingFieldName]=" &
[TableTwo].[LinkingFieldName]), "Default")

--

Ken Snell
<MS ACCESS MVP>



TableOne lists 1000 Material Codes (primary key). TableTwo contains a
field
called MatlCode.

What I need is a way, in a query, to look in TableOne and if the MatlCode
in
a record in TableTwo is in the list of 1000 records in TableOne then enter
that code, if it's not in the list in TableOne then enter "Default".

I'm just drawing a blank. Thanks -
Click to expand...
Click to expand...
Click to expand...
 
You're close, if I'm understanding correctly. You have an extraneous comma
in the statement, and you're not using the Nz function to give you "Default"
as the value if the Material Code value isn't in TableOne table. Try this
(assumes that Material Code and MATCODE fields are numeric data types):

SELECT TableTwo.MODEL, GeneralInformation.ProductFamily AS COMMCODE,
TableTwo.MATCODE, Nz(DLookUp("[Material Code]","[TableOne]","[Material
Code]=" &
[TableTwo].[MATCODE]),"Default") AS MC FROM (TableTwo INNER JOIN
GeneralInformation ON
TableTwo.MODEL = GeneralInformation.Model) INNER JOIN LeadTime ON
GeneralInformation.Model = LeadTime.Model;


If Material Code and MATCODE fields are text data types, then use this:

SELECT TableTwo.MODEL, GeneralInformation.ProductFamily AS COMMCODE,
TableTwo.MATCODE, Nz(DLookUp("[Material Code]","[TableOne]","[Material
Code]='" &
[TableTwo].[MATCODE] & "'"),"Default") AS MC FROM (TableTwo INNER JOIN
GeneralInformation ON
TableTwo.MODEL = GeneralInformation.Model) INNER JOIN LeadTime ON
GeneralInformation.Model = LeadTime.Model;

--

Ken Snell
<MS ACCESS MVP>

Alex said:
Sorry Ken, here it is. Thanks so much for your help.


SELECT TableTwo.MODEL, GeneralInformation.ProductFamily AS COMMCODE,
TableTwo.MATCODE, DLookUp("[Material Code]","[TableOne]","[Material Code]=" &
[TableTwo].[MATCODE]) AS MC, FROM (TableTwo INNER JOIN GeneralInformation ON
TableTwo.MODEL = GeneralInformation.Model) INNER JOIN LeadTime ON
GeneralInformation.Model = LeadTime.Model;


Ken Snell said:
You didn't post your SQL statement?


--

Ken Snell
<MS ACCESS MVP>

Alex said:
Thanks Ken. I am still unsuccessful. I have used the example you gave
Click to expand...
me
and
examples from the Access Bible and from the Help System and I'm still
struggling. I keep getting "Error" in my field and Data Type Mismatch msg
boxes.

The following is my SQL. I don't have TableOne showing in the query, but
it's my understanding that I can lookup values in a table that is not open.

TableOne field is MaterialCode. TableTwo field is MATCODE, For MC: in my
query I need to show, for every record in TableTwo the name of the
Click to expand...
MATCODE
if
it exists in TableOne. If there are no matches to MATCODE in TableOne
Click to expand...
I
need
the word "default" to show in the MC field in my query. Below is my
Click to expand...
SQL -
am
I close at all?

Thanks so much

:

Check out Nz and DLookup functions to do what you want:

LookedUpValue: Nz(DLookup("MatlCode", "TableOne",
Click to expand...
"[LinkingFieldName]="
&
[TableTwo].[LinkingFieldName]), "Default")

--

Ken Snell
<MS ACCESS MVP>



TableOne lists 1000 Material Codes (primary key). TableTwo contains a
field
called MatlCode.

What I need is a way, in a query, to look in TableOne and if the MatlCode
in
a record in TableTwo is in the list of 1000 records in TableOne
Click to expand...
then
enter
that code, if it's not in the list in TableOne then enter "Default".

I'm just drawing a blank. Thanks -
Click to expand...
Click to expand...
Click to expand...
 
Thank you so much Ken. I finally got it. I used your example and also had
to create a query that combined my fields in TableOne and TableTwo.

Thanks again, you were a huge help.

Ken Snell said:
You're close, if I'm understanding correctly. You have an extraneous comma
in the statement, and you're not using the Nz function to give you "Default"
as the value if the Material Code value isn't in TableOne table. Try this
(assumes that Material Code and MATCODE fields are numeric data types):

SELECT TableTwo.MODEL, GeneralInformation.ProductFamily AS COMMCODE,
TableTwo.MATCODE, Nz(DLookUp("[Material Code]","[TableOne]","[Material
Code]=" &
[TableTwo].[MATCODE]),"Default") AS MC FROM (TableTwo INNER JOIN
GeneralInformation ON
TableTwo.MODEL = GeneralInformation.Model) INNER JOIN LeadTime ON
GeneralInformation.Model = LeadTime.Model;


If Material Code and MATCODE fields are text data types, then use this:

SELECT TableTwo.MODEL, GeneralInformation.ProductFamily AS COMMCODE,
TableTwo.MATCODE, Nz(DLookUp("[Material Code]","[TableOne]","[Material
Code]='" &
[TableTwo].[MATCODE] & "'"),"Default") AS MC FROM (TableTwo INNER JOIN
GeneralInformation ON
TableTwo.MODEL = GeneralInformation.Model) INNER JOIN LeadTime ON
GeneralInformation.Model = LeadTime.Model;

--

Ken Snell
<MS ACCESS MVP>

Alex said:
Sorry Ken, here it is. Thanks so much for your help.


SELECT TableTwo.MODEL, GeneralInformation.ProductFamily AS COMMCODE,
TableTwo.MATCODE, DLookUp("[Material Code]","[TableOne]","[Material Code]=" &
[TableTwo].[MATCODE]) AS MC, FROM (TableTwo INNER JOIN GeneralInformation ON
TableTwo.MODEL = GeneralInformation.Model) INNER JOIN LeadTime ON
GeneralInformation.Model = LeadTime.Model;


Ken Snell said:
You didn't post your SQL statement?


--

Ken Snell
<MS ACCESS MVP>

Thanks Ken. I am still unsuccessful. I have used the example you gave me
and
examples from the Access Bible and from the Help System and I'm still
struggling. I keep getting "Error" in my field and Data Type Mismatch msg
boxes.

The following is my SQL. I don't have TableOne showing in the query, but
it's my understanding that I can lookup values in a table that is not
open.

TableOne field is MaterialCode. TableTwo field is MATCODE, For MC: in my
query I need to show, for every record in TableTwo the name of the MATCODE
if
it exists in TableOne. If there are no matches to MATCODE in TableOne I
need
the word "default" to show in the MC field in my query. Below is my SQL -
am
I close at all?

Thanks so much

:

Check out Nz and DLookup functions to do what you want:

LookedUpValue: Nz(DLookup("MatlCode", "TableOne", "[LinkingFieldName]="
&
[TableTwo].[LinkingFieldName]), "Default")

--

Ken Snell
<MS ACCESS MVP>



TableOne lists 1000 Material Codes (primary key). TableTwo contains a
field
called MatlCode.

What I need is a way, in a query, to look in TableOne and if the
MatlCode
in
a record in TableTwo is in the list of 1000 records in TableOne then
enter
that code, if it's not in the list in TableOne then enter "Default".

I'm just drawing a blank. Thanks -
Click to expand...
Click to expand...
Click to expand...
 
You're welcome.

--

Ken Snell
<MS ACCESS MVP>

Alex said:
Thank you so much Ken. I finally got it. I used your example and also had
to create a query that combined my fields in TableOne and TableTwo.

Thanks again, you were a huge help.

Ken Snell said:
You're close, if I'm understanding correctly. You have an extraneous comma
in the statement, and you're not using the Nz function to give you "Default"
as the value if the Material Code value isn't in TableOne table. Try this
(assumes that Material Code and MATCODE fields are numeric data types):

SELECT TableTwo.MODEL, GeneralInformation.ProductFamily AS COMMCODE,
TableTwo.MATCODE, Nz(DLookUp("[Material Code]","[TableOne]","[Material
Code]=" &
[TableTwo].[MATCODE]),"Default") AS MC FROM (TableTwo INNER JOIN
GeneralInformation ON
TableTwo.MODEL = GeneralInformation.Model) INNER JOIN LeadTime ON
GeneralInformation.Model = LeadTime.Model;


If Material Code and MATCODE fields are text data types, then use this:

SELECT TableTwo.MODEL, GeneralInformation.ProductFamily AS COMMCODE,
TableTwo.MATCODE, Nz(DLookUp("[Material Code]","[TableOne]","[Material
Code]='" &
[TableTwo].[MATCODE] & "'"),"Default") AS MC FROM (TableTwo INNER JOIN
GeneralInformation ON
TableTwo.MODEL = GeneralInformation.Model) INNER JOIN LeadTime ON
GeneralInformation.Model = LeadTime.Model;

--

Ken Snell
<MS ACCESS MVP>

Alex said:
Sorry Ken, here it is. Thanks so much for your help.


SELECT TableTwo.MODEL, GeneralInformation.ProductFamily AS COMMCODE,
TableTwo.MATCODE, DLookUp("[Material Code]","[TableOne]","[Material Code]=" &
[TableTwo].[MATCODE]) AS MC, FROM (TableTwo INNER JOIN
Click to expand...
GeneralInformation
ON
TableTwo.MODEL = GeneralInformation.Model) INNER JOIN LeadTime ON
GeneralInformation.Model = LeadTime.Model;


:

You didn't post your SQL statement?


--

Ken Snell
<MS ACCESS MVP>

Thanks Ken. I am still unsuccessful. I have used the example you
Click to expand...
gave
me
and
examples from the Access Bible and from the Help System and I'm still
struggling. I keep getting "Error" in my field and Data Type
Click to expand...
Mismatch
msg
boxes.

The following is my SQL. I don't have TableOne showing in the
Click to expand...
query,
but
it's my understanding that I can lookup values in a table that is not
open.

TableOne field is MaterialCode. TableTwo field is MATCODE, For
Click to expand...
MC: in
my
query I need to show, for every record in TableTwo the name of the MATCODE
if
it exists in TableOne. If there are no matches to MATCODE in
Click to expand...
TableOne
I
need
the word "default" to show in the MC field in my query. Below is
Click to expand...
my
SQL -
am
I close at all?

Thanks so much

:

Check out Nz and DLookup functions to do what you want:

LookedUpValue: Nz(DLookup("MatlCode", "TableOne", "[LinkingFieldName]="
&
[TableTwo].[LinkingFieldName]), "Default")

--

Ken Snell
<MS ACCESS MVP>



TableOne lists 1000 Material Codes (primary key). TableTwo contains a
field
called MatlCode.

What I need is a way, in a query, to look in TableOne and if the
MatlCode
in
a record in TableTwo is in the list of 1000 records in
Click to expand...
TableOne
then
enter
that code, if it's not in the list in TableOne then enter "Default".

I'm just drawing a blank. Thanks -
Click to expand...
Click to expand...
Click to expand...
 
Back
Top