get max date from linked sql server table

[ragtopcaddy via AccessMonster.com]

How do I return the latest date from a linked SQL Server table in a pass
through query?

 

[John Spencer]

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

 

[ragtopcaddy via AccessMonster.com]

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,

 

[John Spencer]

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,

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.

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

 

[ragtopcaddy via AccessMonster.com]

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
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]

 

[ragtopcaddy via AccessMonster.com]

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