RE: searching a table using multiple criteria

Tech Tip: Click here to run a free scan for Windows Errors and optimize PC performance

LOL
I have the same problem.
There is a frequent MVP poster named Douglas J. Steele whom I consider to be
not only the most knowledgable person I know when it comes to Access, but
also has the best approach to most problems.
--
Dave Hargis, Microsoft Access MVP


"dfeigen115" wrote:

Oops, no really meant you Dave (some one named Doug stepped into my ioffice
as I started to compose the question). Multi-tasking will be the end of me
soon enough....
Dan

"Klatuu" wrote:

Doug?
If you are confusing me with Douglas J. Steele, I am honored and he is
probably upset :)

You can use the same DLookup in the report. You would put it in the control
source of a text box. Instead of referencing the control names on the report
(val1, val2), you would reference the corresponding controls on the report.
--
Dave Hargis, Microsoft Access MVP


"dfeigen115" wrote:

Doug, a followup question. If I needed to do something similar during the
generation of a report, woudl the dllokup work? In the report val1 & val2
are populated, but would be used inm the dlookup against the same table.
thanks,Dan

"Klatuu" wrote:

The DLookup should do it for you:

DLookup("[Field3]", "MyTable", "[Field1] = " & Me.Val1 & " And [Field2] <
" & Me.Val2])

A couple of notes:
The syntax above assumes Field1 and Field2 are both numeric. If they are
not, you will need to modify the code to include the delimiters for other
data types.

DLookup will return Null if no match is found.
--
Dave Hargis, Microsoft Access MVP


"dfeigen115" wrote:

I have a table with 3 fields (Field1, Field2, Field3). On a form, there are
2 fields, Val1 and Val2. I need to be able to search the table, returning
the value in Fields 3 where Val1 = Field1 and Val2 < Field2. The table is
sorted by Field1 ascending and Field2 descending.

Thanks in advance, Dan
.

Relevant Pages