Following the DLookup question

  • Thread starter Thread starter Wei
  • Start date Start date
W

Wei

Thanks Michel for fixing my Dlookup expression.

the table "vrg-mce" has fields
like "2000CL", "2001CL", "2002CL" etc. I'm trying to make
the query look up the correct column according to the
VEH_YEAR (vehicle production year).

So here I write:

CL: DLookUp("VEH_YEAR" & "CL","[vrg-
mce]","Vehicle_Code=""" & [VEH_CODE] & """")

and this returns #Error.

Hope someone could help me.

Thanks a lot.

Wei
 
Try...

CL: DLookUp("[" & "VEH_YEAR" & "CL]","[vrg-
mce]","[Vehicle_Code]=""" & [VEH_CODE] & """")
 
I think Chris was very close, but you probably need to get the value of the
field Veh_Year and include that in the field name string.


CL: DLookUp("[" & VEH_YEAR & "CL]",
"[vrg-mce]","[Vehicle_Code]=""" & [VEH_CODE] & """")
Try...

CL: DLookUp("[" & "VEH_YEAR" & "CL]","[vrg-
mce]","[Vehicle_Code]=""" & [VEH_CODE] & """")
-----Original Message-----
Thanks Michel for fixing my Dlookup expression.

the table "vrg-mce" has fields
like "2000CL", "2001CL", "2002CL" etc. I'm trying to make
the query look up the correct column according to the
VEH_YEAR (vehicle production year).

So here I write:

CL: DLookUp("VEH_YEAR" & "CL","[vrg-
mce]","Vehicle_Code=""" & [VEH_CODE] & """")

and this returns #Error.

Hope someone could help me.

Thanks a lot.

Wei
.
Click to expand...
Click to expand...
 
As an aside, your Table Structure doesn't look like it has been properly
designed & implemented.

You actually store data (2000, 2001, 2002) in the Field names which are NOT
recommended (since JET database engine cannot process data in names).

Another point is that it sounds like you create a new/additional Field each
year to store extra data. You end up with repeating groups of Fields (with
one Field in each group). This violates the Principles of the Relational
Database Design also.

Perhaps, you should check out the Relational Database Design Theory and make
sure your Database / Table Structure follows the Theory / Principles.
 
Thanks!

Wei
-----Original Message-----
I think Chris was very close, but you probably need to get the value of the
field Veh_Year and include that in the field name string.


CL: DLookUp("[" & VEH_YEAR & "CL]",
"[vrg-mce]","[Vehicle_Code]=""" & [VEH_CODE] & """")
Try...

CL: DLookUp("[" & "VEH_YEAR" & "CL]","[vrg-
mce]","[Vehicle_Code]=""" & [VEH_CODE] & """")
-----Original Message-----
Thanks Michel for fixing my Dlookup expression.

the table "vrg-mce" has fields
like "2000CL", "2001CL", "2002CL" etc. I'm trying to make
the query look up the correct column according to the
VEH_YEAR (vehicle production year).

So here I write:

CL: DLookUp("VEH_YEAR" & "CL","[vrg-
mce]","Vehicle_Code=""" & [VEH_CODE] & """")

and this returns #Error.

Hope someone could help me.

Thanks a lot.

Wei
.
Click to expand...
Click to expand...
.
Click to expand...
 
Thank you!

Wei
-----Original Message-----
Try...

CL: DLookUp("[" & "VEH_YEAR" & "CL]","[vrg-
mce]","[Vehicle_Code]=""" & [VEH_CODE] & """")

-----Original Message-----
Thanks Michel for fixing my Dlookup expression.

the table "vrg-mce" has fields
like "2000CL", "2001CL", "2002CL" etc. I'm trying to make
the query look up the correct column according to the
VEH_YEAR (vehicle production year).

So here I write:

CL: DLookUp("VEH_YEAR" & "CL","[vrg-
mce]","Vehicle_Code=""" & [VEH_CODE] & """")

and this returns #Error.

Hope someone could help me.

Thanks a lot.

Wei
.
Click to expand...
.
Click to expand...
 
Yes, you are right. I'm new to Access. Thanks for your
suggestions. I will read the Database Design Theory when I
have time. Thanks a lot!

Wei
-----Original Message-----
As an aside, your Table Structure doesn't look like it has been properly
designed & implemented.

You actually store data (2000, 2001, 2002) in the Field names which are NOT
recommended (since JET database engine cannot process data in names).

Another point is that it sounds like you create a new/additional Field each
year to store extra data. You end up with repeating groups of Fields (with
one Field in each group). This violates the Principles of the Relational
Database Design also.

Perhaps, you should check out the Relational Database Design Theory and make
sure your Database / Table Structure follows the Theory / Principles.

--
HTH
Van T. Dinh
MVP (Access)



Thanks Michel for fixing my Dlookup expression.

the table "vrg-mce" has fields
like "2000CL", "2001CL", "2002CL" etc. I'm trying to make
the query look up the correct column according to the
VEH_YEAR (vehicle production year).

So here I write:

CL: DLookUp("VEH_YEAR" & "CL","[vrg-
mce]","Vehicle_Code=""" & [VEH_CODE] & """")

and this returns #Error.

Hope someone could help me.

Thanks a lot.

Wei
Click to expand...


.
Click to expand...
 
Back
Top