SQL query in MS access 2003

Hello,

I have a query in sql server 2000 , which I should make it work in MS access 2003.

Can anybody help me out in coverting the below query.

SELECT A.Project_ID, A.Title, A.comm1 AS Comments, A.Partner AS PM, A.Staff_Assigned AS TL, A.Contact_Name AS FL, A.MD, A.Status, A.Project_Type,
ISNULL(B.Delivered_Date, B.Delivery_Date) AS Start_Date, ISNULL(C.Delivered_Date, C.Delivery_Date) AS End_Date FROM dbo.PROJECT A LEFT OUTER JOIN dbo.PROJDATE B ON A.Project_ID = B.Project_ID AND B.Date_Type = 'Start Date' LEFT OUTER JOIN
dbo.PROJDATE C ON A.Project_ID = C.Project_ID AND C.Date_Type = 'End Date'


Thanks

In an Access MDB/MDE, you can use ADO, DAO or a pass-through query to pass any valid T-SQL script to SQL Server. Use a ptq for as the recordsource for comboboxes, listboxes, reports, etc. ADO and DAO are not for binding data in MDB/MDEs.

<air code>
... create sql statement ...
... set a QueryDef object to the ptq ...
... add the sql string ...
... set the connect string for the querydef ...
... close the QueryDef ...
... open the report ...
... reset the SQL string and connection string on the QueryDef ...
</air code>

In an Access ADP/ADE, you can connect directly to SQL Server, and only T-SQL syntax can be used.

Hi thanks for the reply..

Might sound silly , but where can I find the option for PTQ.

I am searching for it, but could not figure it out.

Thanks

Hi,

Thank you very much . I have got it working.

Thanks

Hi

I have got a question. I am running the report using the PTQ.

But it always prompts me for the DSN connection..and the window just keeps on poping up , when I go the design view.

what should I do.

thanks

Work out the air code that I posted, put it in an unbound Access form.

Add some unbound fields and comboboxes to the form that you can concatenate as your WHERE clause.

Dim strSQL As String
Dim q As DAO.QueryDef

If Me![colA] <> vbNullString Then
strSQL = "colA LIKE '" & Replace(Me![colA], Chr(39), Chr(39) & Chr(39)) & "'"
End If

If Me![colB] <> vbNullString Then
If Len(strSQL) > 0 Then strSQL = strSQL & " AND "
strSQL = strSQL & "colB LIKE '" & Replace(Me![colB], Chr(39), Chr(39) & Chr(39)) & "'"
End If

If Len(strSQL) > 0 Then strSQL = " WHERE " & strSQL

strSQL = "SELECT column_list FROM table " & strSQL


Set q = CurrentDb().QueryDefs(ptq_name)
q.SQL = strSQL
q.ConnectString = "................."
Set q = Nothing

DoCmd.OpenReport report_name, vbPreview

Set q = CurrentDb().QueryDefs(ptq_name)
q.SQL = "()"
q.ConnectString = vbNullstring

[edit - don't ever forget to set the object to nothing]
Set q = Nothing