Link current query to a table?

  • Thread starter Thread starter Robbro
  • Start date Start date
R

Robbro

I'm trying to create a cost estimate form, and I'm working on one underlying
query to power it.
Its based off a lot number. A certain digit in our lot number indicates the
blend of the product. I have a table of blend codes and descriptions based
on that digit. I have a column to calculate the blend code from the lot
number but I am having no luck getting the description into either the query
or the form. I have tried a Dlookup but I seem to have trouble getting it to
refer back to the query that it is in.

I know I could create another table of Lots and their associated blend
codes, but was trying to avoid that since the blend code is embeded in the
lot # already.

So what I have in the query where description is what I cannot get imported
is:

Lot blendcode description



and in my table I have

blendcode description


What is the proper way of getting my description into my query?
 
If you have a table that connects a blendcode to a description, and you have
a way to determine blendcode, one approach might be to create your first
query to derive blendcode, then a second query to join the results of the
first to your table with blendcode & description.

By the way, by embedding more than one fact in a field (e.g., your
"lotnumber" with embedded blendcode), you make more work for yourself.

Good luck!

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
You can join on a calculated field but then you can not open it in design
view. The easiest way is to save the query with the calculated field and use
it in a follow-on query where you join the table of blend codes and
descriptions.
 
If you wish to use DLookup or a join you should be able to.

Lets say the 8th character of the Lot number indicates the blend code and the
blendcode is text. With DLookup you could use something like the following.

DLOOKUP("Description","BlendcodeTable","BlendCode=""" &
Mid([YourTable.[Lot],8,1) & """")

If blendCode is a number field, then you need to remove the extraneous quotes
and you get

DLOOKUP("Description","BlendcodeTable","BlendCode=" & Mid([YourTable.[Lot],8,1))



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
 
I believe this is exactly what I'm looking for, but when I copy/paste and
only change the names to match mine I get an error:
" is not a valid name

I have 3 quotes before the first & and 4 quotes after the last just as in
your post.


John Spencer said:
If you wish to use DLookup or a join you should be able to.

Lets say the 8th character of the Lot number indicates the blend code and the
blendcode is text. With DLookup you could use something like the following.

DLOOKUP("Description","BlendcodeTable","BlendCode=""" &
Mid([YourTable.[Lot],8,1) & """")

If blendCode is a number field, then you need to remove the extraneous quotes
and you get

DLOOKUP("Description","BlendcodeTable","BlendCode=" & Mid([YourTable.[Lot],8,1))



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I'm trying to create a cost estimate form, and I'm working on one underlying
query to power it.
Its based off a lot number. A certain digit in our lot number indicates the
blend of the product. I have a table of blend codes and descriptions based
on that digit. I have a column to calculate the blend code from the lot
number but I am having no luck getting the description into either the query
or the form. I have tried a Dlookup but I seem to have trouble getting it to
refer back to the query that it is in.

I know I could create another table of Lots and their associated blend
codes, but was trying to avoid that since the blend code is embeded in the
lot # already.

So what I have in the query where description is what I cannot get imported
is:

Lot blendcode description



and in my table I have

blendcode description


What is the proper way of getting my description into my query?
Click to expand...
.
Click to expand...
 
I'm in accounting, and have no control over the information I am presented, I
just try to make the best use of it :)

I had considered the query on top of query approach, just wondered if it was
better or worse in any way than using Dlookup or some other approach really.
 
K, fixed it with a missing bracker after [Yourtable and then using 2 quotes
before the first & and 3 quotes after the last instead of 3 and 4......

Robbro said:
I believe this is exactly what I'm looking for, but when I copy/paste and
only change the names to match mine I get an error:
" is not a valid name

I have 3 quotes before the first & and 4 quotes after the last just as in
your post.


John Spencer said:
If you wish to use DLookup or a join you should be able to.

Lets say the 8th character of the Lot number indicates the blend code and the
blendcode is text. With DLookup you could use something like the following.

DLOOKUP("Description","BlendcodeTable","BlendCode=""" &
Mid([YourTable.[Lot],8,1) & """")

If blendCode is a number field, then you need to remove the extraneous quotes
and you get

DLOOKUP("Description","BlendcodeTable","BlendCode=" & Mid([YourTable.[Lot],8,1))



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I'm trying to create a cost estimate form, and I'm working on one underlying
query to power it.
Its based off a lot number. A certain digit in our lot number indicates the
blend of the product. I have a table of blend codes and descriptions based
on that digit. I have a column to calculate the blend code from the lot
number but I am having no luck getting the description into either the query
or the form. I have tried a Dlookup but I seem to have trouble getting it to
refer back to the query that it is in.

I know I could create another table of Lots and their associated blend
codes, but was trying to avoid that since the blend code is embeded in the
lot # already.

So what I have in the query where description is what I cannot get imported
is:

Lot blendcode description



and in my table I have

blendcode description


What is the proper way of getting my description into my query?
Click to expand...
.
Click to expand...
Click to expand...
 
Ok, I was testing with a forced value when the 2 and 3 quotes worked, when I
put Mid() back in there the 3 and 4 were necessary so the only problem was
the missing bracket.


Robbro said:
I believe this is exactly what I'm looking for, but when I copy/paste and
only change the names to match mine I get an error:
" is not a valid name

I have 3 quotes before the first & and 4 quotes after the last just as in
your post.


John Spencer said:
If you wish to use DLookup or a join you should be able to.

Lets say the 8th character of the Lot number indicates the blend code and the
blendcode is text. With DLookup you could use something like the following.

DLOOKUP("Description","BlendcodeTable","BlendCode=""" &
Mid([YourTable.[Lot],8,1) & """")

If blendCode is a number field, then you need to remove the extraneous quotes
and you get

DLOOKUP("Description","BlendcodeTable","BlendCode=" & Mid([YourTable.[Lot],8,1))



John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County
I'm trying to create a cost estimate form, and I'm working on one underlying
query to power it.
Its based off a lot number. A certain digit in our lot number indicates the
blend of the product. I have a table of blend codes and descriptions based
on that digit. I have a column to calculate the blend code from the lot
number but I am having no luck getting the description into either the query
or the form. I have tried a Dlookup but I seem to have trouble getting it to
refer back to the query that it is in.

I know I could create another table of Lots and their associated blend
codes, but was trying to avoid that since the blend code is embeded in the
lot # already.

So what I have in the query where description is what I cannot get imported
is:

Lot blendcode description



and in my table I have

blendcode description


What is the proper way of getting my description into my query?
Click to expand...
.
Click to expand...
Click to expand...
 
JOPO (just one person's opinion) ...

I prefer to "chain" queries together, rather than trying to invoke DLookup()
(or other Dx functions).

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or pseudocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.
 
Back
Top