Code to reference field in query

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

Guest

Here is what I have put together. Can someone tell me what I did wrong? I
am not getting any errors, but it is pulling all records instead of just what
is "out of spec". I need to pull what is out of spec for the product code
listed which is looked up in the tblProductSpecs table. How do I reference
the product field for each record?

<DLookUp("[MinDelta]","[tblProductSpecs]","[product] ='" & [product] & "'")
And >DLookUp("[MaxDelta]","[tblProductSpecs]","[product] ='" & [product] &
"'")
 
Of course it will return all records.
"[product] ='" & [product] & "'") will always be true.
My guess is the second [product] is a control on your form. You need to
qualify it. Unstructured naming conventions cause this sort of problem.
 
Miranda,

Not knowing what the rest of your SQL looks like, makes this difficult. My
guess is that you have a Table (I'll refer to it as TableA) which contains a
[product] and some sort of value, that you want to compare to the MinDelta
and MaxDelta columns of tblProductSpecs, and select all those records that
are either less than MinDelta or greater than MaxDelta.

Rather than using the DLOOKUP( ) function, I think you would probably be
better off joining these two tables on the product ID, something like:

Select TableA.Product, TableA.SomeValue
FROM TableA
INNER JOIN tblProductSpecs
ON TableA.Product = tblProductSpecs.Product
WHERE TableA.SomeValue < tblProductSpecs.MinValue
OR TableA.SomeValue > tblProductSpecs.MaxValue

HTH
Dale
 
Miranda said:
Here is what I have put together. Can someone tell me what I did wrong? I
am not getting any errors, but it is pulling all records instead of just what
is "out of spec". I need to pull what is out of spec for the product code
listed which is looked up in the tblProductSpecs table. How do I reference
the product field for each record?

<DLookUp("[MinDelta]","[tblProductSpecs]","[product] ='" & [product] & "'")
And >DLookUp("[MaxDelta]","[tblProductSpecs]","[product] ='" & [product] &
Click to expand...


Miranda,

<DLookUp("[MinDelta]","[tblProductSpecs]","[product] ='" & [product] & "'")
And
DLookUp("[MaxDelta]","[tblProductSpecs]","[product] ='" & [product] & "'")
Click to expand...

Your code:

='" & [product] & "'")

Becomes:

='" & [Forms]![YourFormsName]![product] & "'")

Where you replace [YourFormsName] with your form's name.


Sincerely,

Chris O.
 
Back
Top