Problem with a query loooking at a query.

[BarryMVS]

Hi,

I have a bit of a problem.

Below is a query that runs fine in Access, but fails on an ASP page.

SELECT [Status Codes].Description, Customers.[Address Line 3], [Service Records].[Problem Description], [Service Records].[Date Call Placed], [Service Records].[Service Record ID], [Service Records].[Date Call Completed], mid(MostRecentServiceHistoryRecord.Notes,instr(1,
MostRecentServiceHistoryRecord.Notes,chr(13))+2) AS [Call Notes]
FROM ((([Service Records] LEFT JOIN JobTypeTable ON [Service Records].
[Call Type]=JobTypeTable.samsID) 
LEFT JOIN Customers ON [Service Records].[Customer ID]=Customers.
[Customer ID]) 
LEFT JOIN [Status Codes] ON [Service Records].Status=[Status Codes].
Status) 
LEFT JOIN MostRecentServiceHistoryRecord ON [Service Records].[Service Record ID]=MostRecentServiceHistoryRecord.[Service Record ID]
WHERE (Customers.Group=63) And instr(1,',14,44,59,74,89,104,119,13,43,58,73,88,103,118,177,181,4,34,49,
64,79,94,141,12,42,57,72,87,102,117,11,41,56,71,86,101,116,131,9,39,54,69,
84,99,114,176,180,3,48,63,78,93,136,140,166,167,168,169,170,171,172,173,5,
35,50,65,80,95,142,175,179,',',' & trim(str([Service Records].Status)) & ',')<>0
ORDER BY [Service Records].[Date Call Placed];

The query worked fine in asp until a new requirement was added. 'MostRecentServiceHistoryRecord' is another query, not a table and this is what I have added.

Does anyone know if asp has a problem with SQL looking at another query in the same database?

Any assistance most welcomed.

Thanks,

Barry

ICT Network Administrator
IT Services Manager

 

[r937]

asp would not know which was a table and which was a saved query -- as far as it knows, your sql is just a string of characters

instead of a saved query, how about a subquery?

see thread701-1070956

rudy | r937.com | Ask the Expert | Premium SQL Articles
SQL for Database-Driven Web Sites (next course starts July 10 2005)

 

[BarryMVS]

Hi,

Thanks for your response.

I have managed to resolved the problem.

Cheers,

Barry

ICT Network Administrator
IT Services Manager