On Feb 5, 11:58 am, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
I'm still not sure why you don't add tblProducts to your report's record
source and join the MaterialNumber to whatever is displayed in txtRequiredMN.
However, it isn't clear whether MaterialNumber is numeric or text. Since your
Nz() might return a string let's assume it is text:
If Numeric:
=DLookUp("[Catalog]","tblProducts","[MaterialNumber] =""" &
Nz([txtRequiredMN],"") & """")
--
Duane Hookom
Microsoft Access MVP
:
On Feb 2, 1:54 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
I would get rid of lookup fields in all tables and use combo boxes on forms.
You might want to read through this linkhttp://
www.mvps.org/access/lookupfields.htm.
--
Duane Hookom
Microsoft Access MVP
:
On Feb 2, 12:54 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
Your error message suggests your fields aren't the same data type. We would
need to know more about your table structures and relationships in order to
provide better assistance.
BTW: I assume you know Column(1) is actually the second column in the select
statement of the combo box.
--
Duane Hookom
Microsoft Access MVP
:
On Jan 31, 5:04 pm, Duane Hookom <duanehookom@NO_SPAMhotmail.com>
wrote:
If this is truly for a report, you would normally place qryRequiredEach in
the record source and join it based on rqdCatalog from your existing query.
However, if rqdCatalog is text then you must use:
=DLookUp("Each","qryRequiredEach","[rqdCatalog] = """ & Nz(cbxRequired!
Column(1),"") & """")
I also try to avoid using combo boxes in reports.
--
Duane Hookom
Microsoft Access MVP
:
I have corrected my last Dlookup statement which was pulling a value
from another field on my report, instead of from a query. Here is my
new DlookUp:
=DLookUp("Each","qryRequiredEach","[rqdCatalog] = " & Nz(cbxRequired!
Column(1),"") & "")
qryRequiredEach = The domain that I am grabbing my values off of and
trying to compare a value from my report to
Each = A number value on my query (price) ie: 37.65
rqdCatalog = A text value that displays the catalog number ie: HXJ3T
[cbxRequired] = The combo box source of which I am trying to compare
to my query, so that it selects the correct [Each]
SQL for cbxRequired: SELECT tblRequired.rqdAutoID,
tblRequired.rqdCatalog FROM tblRequired;
All I get is #Error, any help is much appreciated.
Thanks,
Matt Pierringer
Hmmm, my last post didn't seem to update, let me try again. Ok, so
the Dlookup isn't working, so I went to your first suggestion. I
added qryRequiredEach to the record source, the only problem with this
is when I tried setting rqdCatalog = Required it said Mismatch
Expression, here is why... how do I fix it?
Required: Is a field from tblProducts(the main part of my record
source) and is linked with tblRequired(which has the field rqdCatalog)
I am not sure if this enough information, but please tell me if there
is anything else I need to post.
Ok... well here is the SQL of the query of my record source:
SELECT tblProducts.Catalog, tblProducts.MaterialNumber,
tblProducts.Manufacturer AS Products_Manufacturer,
tblProducts.Category, tblProducts.Description AS Products_Description,
tblProducts.Each, tblProducts.[Sub-Category], tblProducts.AddedNote,
tblProducts.Required, tblProducts.NoList, tblProducts.ProductID,
tblManufacturer.Manufacturer AS Manufacturer_Manufacturer,
tblCategories.CategoryName, tblCategories.Description AS
Categories_Description, [tblSub-Categories].Type
FROM qryRequiredEach, [tblSub-Categories] INNER JOIN (tblManufacturer
INNER JOIN (tblCategories INNER JOIN tblProducts ON tblCategories.ID =
tblProducts.Category) ON tblManufacturer.ID =
tblProducts.Manufacturer) ON [tblSub-Categories].ID = tblProducts.[Sub-
Category]
ORDER BY tblProducts.Catalog;
The query doing very well with the qryRequiredEach being in there. It
is giving me a whole bunch of repeats, which when I try making the
relationship to only include results from qryRequiredEach when
matching with Required from tblProducts it just gives me that "Type of
mismatch in expression" which is probably because it is trying to
compare to a lookup field in my table, which is Data Type- Number. Do
I just have to change the data type, or can I not do this since it is
a lookup field...
Heh, well ok I just deleted all of my lookups so it will probably be a
while before I respond(I think I had 5 of them
), I am assuming
many things will be easier now. I will probably be able to figure out
the this problem on my own in light of this new information. Well
thanks for steering me in the right direction.
Matt Pierringer
Ok, I deleted all off the table lookups, now I have things setup kind
of differently to overcome using the lookups. Instead of going using
any queries, I am just trying to use a Dlookup directly with my main
table(tblProducts). Here is my syntax of the Dlookup I am currently
trying to use:
=DLookUp("[Catalog]","tblProducts","[MaterialNumber] =
Nz([txtRequiredMN],"")")
Catalog: Text field from tblProducts
tblProducts: My domain for my report(a Product Catalog)
txtRequiredMN: A field on my report which displays the control source
"Required"(This is the same as Catalog, except for it displays very
sparingly when this field contains data). The reason for having
txtRequiredMN is because it is displayed when the displayed
"Catalog"(on the report) requires another product to be purchased in
order for that product(the Catalog) to function.
Again I have tried many different variations of Dlookup and I can not
get it to work.
Thanks for your help,
Matt Pierringer
tblProducts is my record source for my report. So let me see if I can
better explain what I am trying to do with examples.
In my detail section I have:
txtCatalog txtMaterialNumber txtDescription
txtEach
txtrqdCatalog txtRequiredMN
txtRqdEach
(Unbound-Dlookup) (Numeric)
(Unbound-Dlookup)
---------------------------------------------------------------------------------------------------------------
Real Example from print preview:
6XW54DWH 871727382 2X4-4 LAMP ENERGY..... $87.55
#Error
829390403 #Error
Note: the second line is conditional if the txtRequiredMN is null or
not, I have not coded this
I am just trying to match the txtRequiredMN against the
MaterialNumber(txtMaterialNumber on report) in the tblProducts, to
find the corresponding Catalog and Each(Price).
I don't think it is possible to join a field contained in the
table(tblProducts) to itself unless I am just doing it wrong.
