DateAdd to find Expiration Date. (1 Viewer)

[soldat452002]

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

 

[MarkK]

To me it breaks normalization to have two effective dates, and two expiry dates in the same record. If you do it this way, why won't you eventually need a NewNewEff, and where does that end?

 

[soldat452002]

Actually the desired end results will be:


Vendor State Contract NewEff NewExp
300324 AZ M06 1/1/2006 12/31/2007
300324 AZ PRDCR 1/1/2008 12/31/9999

 

[MarkK]

So field names are changed? Is this in a different table? And the duplicate row is deleted (the one ending in 12/31/9999)?

I don't see how you can do all this in a query. I think this will take code, since it seems like it is a multi-step operation.

When does this operation occur, how is it initiated?

 

[soldat452002]

Im running a query to find the Min Eff Date per Vendor, State and Contract, Then I need to do a DateAdd-1 for the Expiration Date. I will update the existing Exp field.

Vendor State Contract Eff Exp
300324 AZ M06 1/1/2006 1/31/2007
300324 AZ M06 1/1/2007 12/31/9999
300324 AZ PRDCR 1/1/2008 12/31/2008
300324 AZ PRDCR 1/1/2009 12/31/9999

 

[plog]

Can you post 2 sets of data to demonstrate what you want?

A. Starting sample data from your table. Include table and field names and enough records to cover all cases.

B. Expected results based on A. Show me what you hope to end up with when you start with the data in A.

 

[soldat452002]

Sure, I attached a Database with my table and a Desired Result table. Thanks for helping me on this problem.

 

[soldat452002]

This is the query I been using:


SELECT ID, Vendor, Eff AS BeginDate, DateAdd("d",-1,DLookUp("Eff","tblPayment","ID = " & Nz(DMin("ID","tblPayment","Vendor = " & [Vendor] & " And ID > " & [ID]),0))) AS EndDate, ID
FROM tblPayment
ORDER BY ID;

 

[plog]

In the Desired_Results table explain to me how the record with a value of 4/28/2014 gets that value. No code, just tell me the logic.

 

[soldat452002]

This Vendor (300259) has 3 different Contract (M06, PRDCR, SA) with 3 different Effective dates (1/1/2006, 2/1/2007, 4/29/2014), each of those should expire when the other Contract begins so the Exp Date are (1/31/2007, 4,28,2014, end of time). If the Contract is the same in another row, always return the Min(Eff).

 

[plog]

each of those should expire when the other Contract begins...

You lost me. Each row of data is independent of another, until you can tell me how to logically connect them. Which you kind of did, but not totally:

If the Contract is the same in another row, always return the Min(Eff).

Then that means every record with a Contract=300259 should have the same NewExp date, which is untrue in your expected results.

So, explain to me how that specific row I referenced gets 4/28/2014 for its NewExp value. If its based on data in another row, you need to explicitly tell me how to connect the records to get to that value.

 

[soldat452002]

There is a new Effective date on the Next row. So the previous expires 1 day prior to that date.


Vendor State Contract Eff Exp NewEff NewExp

300259 AZ PRDCR 2/1/2007 12/31/9999 2/1/2007 4/28/2014
300259 AZ SA 4/29/2014 12/31/9999 4/29/2014

 

[plog]

There is a new Effective date on the Next row.

Nope, 'Next' is not a term you can use about data in a table. Each record is completely independent of another--there is absolutely no order in a table. What you need to do is tell me how to use the values in one record to determine what the 'next' record would be.

 

[soldat452002]

Nope, 'Next' is not a term you can use about data in a table. Each record is completely independent of another--there is absolutely no order in a table. What you need to do is tell me how to use the values in one record to determine what the 'next' record would be.

There is a New Contract (M06, PRDCR, SA, etc) this determines that it is a new contract on the next row. If it were the same, it would be considered the same one.

 

[plog]

Again, there is no 'next' row in a table. Tables have no order to them. Think of tables like a bucket of data that rows get thrown into wily-nily.

Order only exists when you explicitly tell it to exist like in a query (ORDER BY) or when you use the Sort button at the top. When I sort your data in the table by the Eff field the 1st record is 300097-AZ-M06, the 'next' record is 300259-AZ-M06. When I sort your data in the table by the Exp field the 1st record is 300259-AZ-M06 and the 'next' record is 300097-AZ-M06. They are each others 'next' record depending on how you sort your data. It's meaningless.

So please, no more assuming I know what 'next' means. Explicitly tell me how to determine what the 'next' record is on your dataset. The next problem that will be a sticking point is that when you want to reference records relative to another you really need an autonumber primary key to do it effectively--which you don't.

 

[soldat452002]

Again, there is no 'next' row in a table. Tables have no order to them. Think of tables like a bucket of data that rows get thrown into wily-nily.

Order only exists when you explicitly tell it to exist like in a query (ORDER BY) or when you use the Sort button at the top. When I sort your data in the table by the Eff field the 1st record is 300097-AZ-M06, the 'next' record is 300259-AZ-M06. When I sort your data in the table by the Exp field the 1st record is 300259-AZ-M06 and the 'next' record is 300097-AZ-M06. They are each others 'next' record depending on how you sort your data. It's meaningless.

So please, no more assuming I know what 'next' means. Explicitly tell me how to determine what the 'next' record is on your dataset. The next problem that will be a sticking point is that when you want to reference records relative to another you really need an autonumber primary key to do it effectively--which you don't.

I see your point, Its poor table design. That's the reason I had to run a query to assign Expiration dates based on Order By, Criteria, etc. I think that's the reason that when a run my query, the date expiration dates are not sequenced. See my example database. It begins to fail after a couple of hundred records.