I need to find the most resent reading, it does not mean “todays” date but the most resent date. I have attached a view of the listing. Do I need to modify the date format first?

http://i448.photobucket.com/albums/qq210/cornhusker-power/date1.jpg

Date Format is YYMMDD
Let me know what you guys or gals think.
Thanks

a sub query of: Select max(yourdate) from yourTable
Will fetch that date for you and can restrict your returned infomration.

Welcome to AWF, and sorry cannot "touch" bucket at work.

now it shows the most resent at the top on some and other do not. It looks to me like Y2k thing. Hmm. Let know what you think after you look at the db. Thanks

max() might be a hard one to do with the data you are saving. it looks to me like your 'date' field is actually a text field (correct me if i'm wrong) with the format YYMMDD.

this would cause your 1998 date to 'appear' as more 'recent' than your 2008 read b/c your 2008 read begins with "08" and your 1998 date begins with "98".

98 is bigger (more 'max') than 08.

what you may need to do if first parse out the actual year, month and date into three separate fields on a query, recompile them as a real date, then do a max() on those.

- by the way, how did you apply the max() function? in a query or VBA? or did you use the totals option in query design...?

I'm using the design view to make my query. How would i go about putting it into a date format. That's what i was thinking. And yes it looks to me like its a name field.

you may be able to just do a simple conversion in a query.

as one of the fields (not as a criteria in a field), put in


ConvertedDate: CDate([READDATE])
Ooops, ahead of myself. you may first need to extract year month and date by making these fields in the query:

i've used this code for converting the following text format (01 JAN 1986) to date (01/01/1986)


Year: Right([Bir_Date],4)
Month: Left(Right([Bir_Date],8),3)
Day: Left([Bir_Date],2)
so yours (format from 010101 to 01/01/2001) might be something like:


Year: Left([READDATE],2)
Month: Left(Right([READDATE],4),2)
Day: Right([READDATE],2)
.. but i can't remember how i then concatenated those into a real date on one field... BUT i do remember finding the answer in these forums!! LOL

Year: Left([READDATE],2)
Month: Left(Right([READDATE],4),2)
Day: Right([READDATE],2)
.. but i can't remember how i then concatenated those into a real date on one field... BUT i do remember finding the answer in these forums!! LOL

That answer would then probably have come from me, the self appointed unofficial AWF date officionado...

Instead of this
Month: Left(Right([READDATE],4),2)
Working but roundabout way of doing it, try using the Mid function
Mid([ReadDate,3,2)
I think thats right, but it could be
Mid([ReadDate,2,2)
I always F-up with this cause it is different in different database types (Oracle/SQL Server/Access/DB2/ AS400/MySQL, I all use)

Dateserial (year, Month, Day) is your ticket to a real date.

Note: You cannot use these columns AND use these columns in the function, inside the function you have to use the Left, Right and Mid options

OK i think im getting it for the most part, at least peaceing it along. I have seprated the dates into YY MM DD. Then i pulled it into another queary. Now is it possible to change the YY to YYYY like 99 to 1999. If i can do that then i think we might have something.

OK i think im getting it for the most part, at least peaceing it along. I have seprated the dates into YY MM DD. Then i pulled it into another queary. Now is it possible to change the YY to YYYY like 99 to 1999. If i can do that then i think we might have something.

maybe if you append "19" to any "year" greater than or equal to "10" or "20" to any year less than or equal to "09" (you don't have any years that are from 1909, do you?)

how would we do that... is the question.... unless access assumes the last 100 years is implied when putting together the dateserial...?

maybe if you try this in the query field: and see if it yeilds the correct centuries (19 and 20)?

Year: Format(Left([READDATE],2),"YYYY")

maybe if you append "19" to any "year" greater than or equal to "10" or "20" to any year less than or equal to "09" (you don't have any years that are from 1909, do you?)

how would we do that... is the question.... unless access assumes the last 100 years is implied when putting together the dateserial...?

Dateserial does do this for you... Dateserial ( 92,2,25) makes for 25 feb 1992
Offcourse keep in mind the Y2K problem when dealing with this, having YY lacking the YYYY your stuck with it though :(