ACCESS FEED: Re: [MS_AccessPros] SQL find Max or most recent dates

Jumat, 14 Agustus 2015

Re: [MS_AccessPros] SQL find Max or most recent dates

SELECT MyTable.myDateField
, CDate(Int([MyTable].[myDateField])) AS myDateNewName
FROM MyTable
WHERE (((MyTable.myDateField) Is Not Null))
ORDER BY MyTable.myDateField DESC;

warm regards,
crystal

2015 Microsoft MVP Virtual Conference for Access on Channel 9 (MSDN)
https://channel9.msdn.com/events/MVP-Virtual-Conference/MVP-Virtual-Conference-Americas-2015/CONS2-Using-Access-and-SharePoint-for-Travel-Expenses-and-Pulling-up-Maps

~ be awesome today ~

On 8/14/2015 8:53 AM, John Viescas JohnV@msn.com [MS_Access_Professionals] wrote:

Phucon-

I would have suggested CDate or similar, but if that's giving you an error, we'll have to come up with something else. How many records are you dealing with? If it's not too many, it might be best to dump the rows from the server into a local Access table, then use DateValue against that table.

John Viescas, Author

Microsoft Access 2010 Inside Out

Microsoft Access 2007 Inside Out

Microsoft Access 2003 Inside Out

Building Microsoft Access Applications

SQL Queries for Mere Mortals

http://www.viescas.com/

(Paris, France)

On Aug 14, 2015, at 4:42 PM, saigonf7q5@yahoo.com [MS_Access_Professionals] <MS_Access_Professionals@yahoogroups.com> wrote:

Good morning sr.

The production table which I used in this query to get the most 2 recent dates, is a query from SQL Server (I am only guessing, since the production query is written by someone else).

The date field in the production query contains the time portion, as a result some of the records are showing the same date twice. e.g.

5005 10/5/2012 3:29:33

5005 10/5/2012 3:29:15

How do I tell the query to ignore the time portion, so it can pull a different date instead? for example like the one here shown below.

5005 10/5/2012 3:29:33

5005 10/4/2012 13:20:15

I've tried to remove the time portion by using differrent format functions like FormatDateTime, CDate, DateValue... it does not work but generated an ODBC-Call failed #195 error.

Thanks

Phucon