Solved: SQL subquery into Access syntax

[babsc01]

Hi, folks! I'm trying to convert the following SQL subquery into MS Access format:

[VBA]
(select top 1 (case when isdate(right(valueset,10))=1 then convert(varchar(10),cast(right(valueset,10)as datetime),101) else NULL end)

-- max(cast(right(valueset,10) as datetime))

from ststats

where

isnumeric(salesordno)=1 and salesordno=a.salesordno and statustype like '%Ship Date%'

and timestmp <=(select max(timestmp) from ststats where salesordno=a.salesordno

and valueset ='INLAB')

order by timestmp desc) as 'Ship Date at INLAB',

[/VBA]
I'm having trouble with the WHEN, CAST, and CONVERT statements, although I've found the CDate expression will work for CONVERT in this case.

Table names are StStats and Folder (aliased as "a"). Any takers that may be able to decipher what I'm doing wrong? Thanks!!!

[Norie]

Well I don't actually think Access supports cast, convert or when.

What is the query supposed to return?

[babsc01]

Norie...thanks for taking a look. Here's what the subquery should do. I wish I could post the tables and data, but this should be able to help somewhat:

Take a SALESORDNO.

Go and look in Ststats for all rows for that SO. From them, Pick the rows which have a valueset = 'INLAB'. If there are many such rows, pick the row which has the latest timestmp.

Take that timestamp, and now compare that timstamp with the timestamps for all rows which have a statustype with *Ship Date* and timestmp <= timestmp at INLAB. Pull all such rows.

Now, for all such rows, check the last 10 chars of the valueset. Pick the max date from that. That is the Ship Date at INLAB.

So, the basics of the subquery is to pull a record with a specific date.

Anything you can do to help me get this into Access would be of great help. Thanks.

[Norie]

I think the best way to go about this would be to build each query in steps.

Build the first query for the sales order no, then build the next query based on the 1st query and so on.

This wouldn't give you a single SQL statement but you should end up with a query that returns the correct data.

By the way you can attach files, press the Go Advanced button and look for Manage Attachments.

[xCav8r]

I think Norie's right. If the data is in Access, then you cannot use CASE, CAST, or CONVERT. Replace CASE with IIF, and you've already figured out CAST and CONVERT can be replaced with the various conversion functions that are available in Access. Those things changed, it should work (except for the % wildcard which is * unless you have ANSI 92 checked in your options). See below for more details on the conversion functions.

http://msdn.microsoft.com/library/de...xFunctions.asp

If the data is in SQL Server 2000, I still think you might have problems with these same functions, since the graphical query designer can't show them, and it often reports errors for valid SQL in such cases.

If all else fails, Norie's suggestion will work even if it isn't as slick as using the T-SQL functions. Another alternative would be to write some custom VBA functions to perform the conversions and do more sophisticated conditional statements than IIF allows, then you could use them in your query.

[babsc01]

Yep...I will certainly keep those things in mind. Thanks for the replies.

[xCav8r]

If our advice leads to a solution, or you come up with one on your own, please post it here so that others might learn from your experience.

[babsc01]

I certainly will...we're still working on a solution, but I'll let everyone know how it comes out.