soldat452002
Registered User.
- Local time
- Today, 11:28
- Joined
- Dec 4, 2012
- Messages
- 50
Hi,
I have a question. How can I achieve this on a query, If the "Vendor", "State" and "Contract" are the same between multiple rows, Return the Minimum "Eff" per Contract and give it an expiration date. If there is another "Contract", then the Expiration Date has to be a Date prior (See example)
here is my query statement:
DateAdd("d",-1,DLookUp("Eff","tblPayment","Vendor = " & Nz(DMin("Vendor","tblPayment","Vendor = " & [Vendor] & " And Vendor > " & [Vendor]),0)))
Vendor State Contract Eff Exp NewEff NewExp
300324 AZ M06 1/1/2006 1/31/2007 1/1/2006 12/31/2007
300324 AZ M06 1/1/2007 12/31/9999
300324 AZ PRDCR 1/1/2008 12/31/2008 1/1/2008 12/31/9999
300324 AZ PRDCR 1/1/2009 12/31/9999
I have a question. How can I achieve this on a query, If the "Vendor", "State" and "Contract" are the same between multiple rows, Return the Minimum "Eff" per Contract and give it an expiration date. If there is another "Contract", then the Expiration Date has to be a Date prior (See example)
here is my query statement:
DateAdd("d",-1,DLookUp("Eff","tblPayment","Vendor = " & Nz(DMin("Vendor","tblPayment","Vendor = " & [Vendor] & " And Vendor > " & [Vendor]),0)))
Vendor State Contract Eff Exp NewEff NewExp
300324 AZ M06 1/1/2006 1/31/2007 1/1/2006 12/31/2007
300324 AZ M06 1/1/2007 12/31/9999
300324 AZ PRDCR 1/1/2008 12/31/2008 1/1/2008 12/31/9999
300324 AZ PRDCR 1/1/2009 12/31/9999