 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Queries / April 2008get max date from linked sql server table
How do I return the latest date from a linked SQL Server table in a pass through query?  SignatureBill Reed "If you can't laugh at yourself, laugh at somebody else" If you are using MS SQL server, the SQL statement should read as follows. SELECT Max(SomeField) as LastDate FROM SomeTable Of course, I believe you want more than just that, but you haven't said that you want more than that. And if so, what you do want. John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > How do I return the latest date from a linked SQL Server table in a pass > through query? John, Thanks for you response. The problem is the date formatting on the server table. If I use SELECT Max( convert( varchar, [MonthEnd],101)) MonthEnd I get 12/31/2006. The actual max date is currently 03/31/2008. I should have clarified my question. Thanks, >If you are using MS SQL server, the SQL statement should read as follows. > [quoted text clipped - 11 lines] >> How do I return the latest date from a linked SQL Server table in a pass >> through query? SignatureBill Reed "If you can't laugh at yourself, laugh at somebody else" What type of field is MonthEnd? If it is a date field then you need to get the MAX BEFORE you convert it to a string. If I am reading what you are doing correctly, you are converting to a string and then getting the max value John Spencer Access MVP 2002-2005, 2007-2008 Center for Health Program Development and Management University of Maryland Baltimore County > John, > [quoted text clipped - 21 lines] >>> How do I return the latest date from a linked SQL Server table in a pass >>> through query? John. It's a date/time field, Mmm dd, yyyy hh:mm:ss. I thought the convert function would return a date type with short date mm/dd/yyyy. If I understand you correctly, it returns a text field. So I'll have to convert it to short date text, and convert that to a date. The problem is, it's a pass through query and I'm not familiar enough with TSQL to put the query together. Thanks, >What type of field is MonthEnd? If it is a date field then you need to get the >MAX BEFORE you convert it to a string. If I am reading what you are doing [quoted text clipped - 10 lines] >>>> How do I return the latest date from a linked SQL Server table in a pass >>>> through query? SignatureBill Reed "If you can't laugh at yourself, laugh at somebody else" I've got a workaround. I have a static passthrough query, qryMaxMonthEnd: SELECT Max([MonthEnd]) MonthEnd FROM dbo.tblName Then I use the result from that query in my code to format it as a short date: Set rsLast = .OpenRecordset("qryMaxMonthEnd") strLastDt = Format(rsLast.Fields(0), "mm/dd/yyyy") rsLast.Close >If you are using MS SQL server, the SQL statement should read as follows. > [quoted text clipped - 11 lines] >> How do I return the latest date from a linked SQL Server table in a pass >> through query? SignatureBill Reed "If you can't laugh at yourself, laugh at somebody else" |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.