Correct dates inserted into incorrect fields in table.

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
142
I have a table that has serial numbers with dates that show when a job was entered, the date it was closed and the date it was installed. The Date of the installation is on the incorrect line and I would like to have it put onto the correct line. How can I do this in Access without going thru every line manually? Below is an example of one serial number with multiple entries.


BarCodeDate_EntDate_ClsDate_Warranty
A-320618
6/2/2017​
6/13/2017
A-320618
3/1/2018​
4/25/20185/11/2018
A-320618
5/16/2018​
6/1/20188/28/2018
A-320618
9/6/2018​
10/3/2018

I would like to have them lined up like this. I have over 73000 entries to go thru. The Date Warranty needs to be greater than the previous Date Cls but less than the next Date Ent.

BarCodeDate_EntDate_ClsDate_Warranty
A-320618
6/2/2017​
6/13/2017
A-320618
3/1/2018​
4/25/2018
A-320618
5/16/2018​
6/1/20185/11/2018
A-320618
9/6/2018​
10/3/20188/28/2018
 

JonXL

Active Member
Joined
Feb 5, 2018
Messages
257
Office Version
365, 2016
Platform
Windows
Given the criteria you've provided, 8/28/2018 would fit in both bottom rows.

How are you deciding which value to use when more than one will work?
 

Tcurtis

Board Regular
Joined
Aug 15, 2009
Messages
142
JonXL. The criteria is that the Date Warranty is greater than the Date Cls but less than the next Date Ent. 8/28/2018 has a greater date than the previous three lines but is only less that the last lines Date_Ent, 9/6/2018. By this criteria it will only fit in the last line.

Thanks
 

xenou

MrExcel MVP, Moderator
Joined
Mar 2, 2007
Messages
16,551
Office Version
2013
Platform
Windows
9/6/2018 happens to be not just the "next" Date_Ent but also the "last" Date_Ent.

I think we need a definition of previous and next. For instance, in this case it looks like you have "previous" looking at other rows but "next" looking at the same row.
 

Forum statistics

Threads
1,081,723
Messages
5,360,870
Members
400,602
Latest member
newaqua

Some videos you may like

This Week's Hot Topics

  • VBA (Userform)
    Hi All, I just would like to know why my code isn't working. Here is my VBA code: [CODE=vba]Private Sub OKButton_Click() Dim i As Integer...
  • List box that changes fill color
    Hello, I have gone through so many pages trying to figure this out. I have a 2020 calendar that depending on the day needs to have a certain...
  • Remove duplicates and retain one. Cross-linked cases
    Hi all I ran out of google keywords to use and still couldn't find a reference how to achieve the results of a single count. It would be great if...
  • VBA Copy and Paste With Duplicates
    Hello All, I'm in need of some input. My VBA skills are sub-par at best. I've assembled this code from basic research and it works but is...
  • Macro
    is it possible for a macro to run if the active cell value is different to the value above it
  • IF DATE and TIME
    I currently use this to check if date has passed but i also need to set a time on it too. Is it possible? [CODE=vba]=IF(B:B>TODAY(),"Not...
Top