HELP!!! Searching for latest dates in one field

CeCe

Registered User.
Local time
Today, 12:20
Joined
Oct 26, 2010
Messages
14
Hi All.... I am using Access 2010. :eek:I am trying to figure out how to get the latest date in a table that houses all dates in the same field. The difference is the tag number. Can someone please help me. It is greatly appreciated.
 
It worked on the farthest date but I guess I didn't know what I was asking for. It looks like I have 3+ dates in the field. The first date is 4/1/2012 and the second date is 7/1/2013(example... Not all second dates are the same and the third date is (for argument purposes) 1/1/2028. I need to capture the second date in the scenerio. Can you help with that..
 
What if you have Four dates - Date1, Date2, Date3, Date4 (in Ascending order), what date should be selected?

E.g.:
1/1/2010
23/3/2010
6/7/2011
18/9/2012

What date should it pick?
 
It worked on the farthest date but I guess I didn't know what I was asking for. It looks like I have 3+ dates in the field. The first date is 4/1/2012 and the second date is 7/1/2013(example... Not all second dates are the same and the third date is (for argument purposes) 1/1/2028. I need to capture the second date in the scenerio. Can you help with that..

Does this mean that all of the Dates are in a single field that is character or text in nature (like below)?

MyDates: "4/1/2012 7/1/2013 1/1/2028"

This is not normalized at all, and if possible, it should be changed to be more normalized.

Despite this, the next question to ask is regarding the SubField separators within the Field. In my example, I separated the dates with a space. If your data has similar rules, you could try to parse out the three dates using Left(), Right(), Mid(), and Instr().
 
Does this mean that all of the Dates are in a single field that is character or text in nature (like below)?

MyDates: "4/1/2012 7/1/2013 1/1/2028"

This is not normalized at all, and if possible, it should be changed to be more normalized.
It looks like you're right there MSAccessGenius ;)
... worth a try :)

Cece does state that here (which I missed):
I am trying to figure out how to get the latest date in a table that houses all dates in the same field.

The Split() function would also do the trick.
 
Thank you for the compliment, but I must not be such the genius since I missed the Split() Function which could end up being the best resolution in this case.
 
vbaInet
I would be trying to get the date 23/3/2010.
 
MSAccessRookie
The dates are in the same field in the table but they are not running the way you are showing in your post. Various dates references the same tag number. A new record is started when changes are made. I think I said that correctly. There are more than one record per tag.
 
But 23/3/2010 is not the latest date field. Are you sure this is what you're after?

Perhaps you could show us a screenshot of what these dates look like in a query.
 

Users who are viewing this thread

Back
Top Bottom