Need criteria for use with DFirst function

[IllinoisREI]

This has me completely stumped. Using this table as an example,

ID Date Field1 Field2 Field3
1 1/1/09 600 0 1/4/09
2 1/4/09 600 600 1/4/09
3 2/1/09 1200 600 2/15/09
4 2/15/09 1200 1200 2/15/09
5 3/1/09 1800 1200 4/1/09
6 3/8/09 1800 1200 4/1/09
7 4/1/09 2400 1900 null
8 4/6/09 2400 2100 null

I need a way to find the value in Field3, which is the first instance (by date) when the value in Field2 (in a current or later entry) equaled or exceeded the value in Field1 for that specific record. I'm thinking the dfirst function could do it but I can't figure out how.

I would gladly donate to the favorite charity of anyone with a solution.

Thanks,

Kevin

 

[PHV]

Typed, untested (SQL code):
SELECT Field3
FROM yourTable
WHERE [Date]=(SELECT Min([Date]) FROM yourTable WHERE Field2>=Field1)

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[PWise]

Dmin("Feild1","Tablename","field2>=field1")

 

[PWise]

PHV:

Why not

SELECT Min([Date]) FROM yourTable WHERE Field2>=Field1

 

[PHV]

Because the OP want the corresponding Field3 value.

 

[IllinoisREI]

Thanks for the feedback guys. I wasn't clear in my post. Field3 needs to be the result of an expression. It does not exist yet as a field in any table or query. The expression would result in a date. I need it to look at the value of field1 in that record, then look through all the records and find the first one (by date) where the field2 value is equal or greater.

Thanks for your help.

 

[PHV]

Field3: Dmin("[Date]","yourTable","Field2>=" & [Field1])

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[IllinoisREI]

PHV: Thanks again for the reply. I gave it a try but I'm getting a "Synatx error (missing operator in query expression)". Would the domain being a query versus a table cause a problem with the Dmin function? What is the reasoning for Field1 in square brackets and not Field2? Also, what does the "&" operator do in this case?

Thanks again for your help.

 

[lespaul]

What's the exact function you are trying to run? the brackets are used when you have a field with a keyword as it's name (like a field named DATE) or if there are spaces in the [field name].

The & is a concatenator, it adds the strings together.

The missing operator would be a missing " or a paren or some other required punctuation.

Leslie

Have you met Hardy Heron?