Populating a column based values in an existing column

[mkirwan]

Hi all,

In the organisation that I work for employees get paid every 2 weeks on a Saturday. So for this financial year the pay period end dates have been 8th July 2006, 22nd July 2006, 5th August 2006 etc.

I have a column in an Access table listing dates. I want the next column to be populated with the next pay period end date. How do I do this?

Kind Regards,

Matthew

 

[PHV]

Like this (SQL code) ?
INSERT INTO yourTable (yourDateField) SELECT 14+Max(yourDateField) FROM yourTable

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[mkirwan]

This just seems to add 14 days to the value in the DATE column and puts this value in the PAY PERIOD END column ie if the DATE is 27/12/2006 it will put the value 10/01/2007 in the PAY PERIOD END column, if the DATE is 28/12/2006 it will put the date 11/01/2007 in the PAY PERIOD END column etc.

I don't want it to work like that.

If the DATE is 27/01/2006 it should put 04/01/2007 in the PAY PERIOD END column as 04/01/2007 is the first pay date of 2007. Similarly if the DATE is 03/01/2007 it should put 04/01/2007 and if the DATE is 15/01/2007 it should put 18/01/2007 ie it should choose the nearest pay date after the Date and put that in the PAY PERIOD END column.

 

[PHV]

Sorry, misunderstood the issue.
I suggest to create a table of pay period end dates.
tblPayEndDates: EndDate DateTime

And now the SQL way:
UPDATE yourTable
SET [PAY PERIOD END] = (SELECT Min(EndDate) FROM tblPayEndDates WHERE EndDate >= [DATE])
WHERE [PAY PERIOD END] Is Null

The VBA way in the AfterUpdate event procedure of the control bound to your [DATE] field:
Me![PAY PERIOD END] = DMin("EndDate", "tblPayEndDates", "EndDate>=#" & Format(Me![DATE], "yyyy-mm-dd") & "#")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886