Dlookup( One more thing...

[Matt Pierringer]

Ok, I have almost got it with the correct syntax. I have corrected my
last Dlookup statement which was pulling a value from the same report I
had dlookup on which wasn't doing me any good, because then it was
providing me duplicate values. Here is my info:
=DLookUp("qryRequiredEach!Each","qryRequiredEach","qryRequiredEach!rqdCatalog
= " & Nz([cbxRequired],0))
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

Currently it is grabbing the first price in the Each column from the
query, how do I get it to compare the right values and then take the
correct Each

 

[Matt Pierringer]

I tried entering in the expression you put down but is missing the Nz(
function to tell me whether it has usable data and it didn't work very
well when I tried just entering what you had, here is what I changed it
to(I know I didn't include the Nz function but it kept saying wrong
number of arguments when I would try to add it on the cbxRequired):
=DLookUp("[Each]","qryRequiredEach","[rqdCatalog] = '" & [cbxRequired]
& "'")

It doesn't show anything when I enter this expression

Since rqdCatalog is text, you need to use the correct syntax for a text
field. Also, including the domain name in a DLookup for each field is
unnecessary.

=DLookUp("[Each]","qryRequiredEach","[rqdCatalog] = '" & Me.cbxRequired &
"'"))

--
Dave Hargis, Microsoft Access MVP

Ok, I have almost got it with the correct syntax. I have corrected my
last Dlookup statement which was pulling a value from the same report I
had dlookup on which wasn't doing me any good, because then it was
providing me duplicate values. Here is my info:
=DLookUp("qryRequiredEach!Each","qryRequiredEach","qryRequiredEach!rqdCatalog
= " & Nz([cbxRequired],0))
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

Currently it is grabbing the first price in the Each column from the
query, how do I get it to compare the right values and then take the
correct Each

 

[Matt Pierringer]

I set the Combo Box to visible and had it print to the report. It
showed a value the same as the rqdCatalog in the query, here is the sql
query SELECT tblRequired.rqAutoIDd , tblRequired.rqdCatalog FROM
tblRequired;
Does it matter which one is first?

Also here is the sql query for qryRequiredEach:
SELECT tblRequired.rqdCatalog, tblProducts.Catalog, tblProducts.Each,
tblProducts.[Material Number]
FROM tblRequired INNER JOIN tblProducts ON tblRequired.rqdCatalog =
tblProducts.Catalog;
As you can see I am pretty sure I don't have other filtering going on.
The syntax of access can puzzle me sometimes because I don't understand
how all the little things have effect.

The Nz function where you want to put it would not really do anthing
different; however, if you want to try it, the correct syntax should be:
=DLookUp("[Each]","qryRequiredEach","[rqdCatalog] = '" & Nz([cbxRequired],
"") & "'")

If no records are being returned and no error is returned, here are the
possibilities:
1. There are no records in the table that match the value of the combo box.
2. The query you are using has other filtering going on
--
Dave Hargis, Microsoft Access MVP

I tried entering in the expression you put down but is missing the Nz(
function to tell me whether it has usable data and it didn't work very
well when I tried just entering what you had, here is what I changed it
to(I know I didn't include the Nz function but it kept saying wrong
number of arguments when I would try to add it on the cbxRequired):
=DLookUp("[Each]","qryRequiredEach","[rqdCatalog] = '" & [cbxRequired]
& "'")

It doesn't show anything when I enter this expression

Since rqdCatalog is text, you need to use the correct syntax for a text
field. Also, including the domain name in a DLookup for each field is
unnecessary.
=DLookUp("[Each]","qryRequiredEach","[rqdCatalog] = '" & Me.cbxRequired &
"'"))
--
Dave Hargis, Microsoft Access MVP
:
Ok, I have almost got it with the correct syntax. I have corrected my
last Dlookup statement which was pulling a value from the same report I
had dlookup on which wasn't doing me any good, because then it was
providing me duplicate values. Here is my info:
=DLookUp("qryRequiredEach!Each","qryRequiredEach","qryRequiredEach!rqdCatalog
= " & Nz([cbxRequired],0))
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
Currently it is grabbing the first price in the Each column from the
query, how do I get it to compare the right values and then take the
correct Each