Trying to do Datediff and Now() in a Passthrough Query Access97

[syoung4]

I am trying to create a Pass Through query in Access 97 which extracts data 24 weeks from the current date.

I will be using this data as Target trend lines in Graphs.

This query works ok in a normal access query but as I am working with a large number of records it takes a long time to run.

I am trying to find a way to use datedif and now() functions in this query as I dont think pass through queries use these function.

Would anyone know how I could get a difference between the week part of a date and todays date so that I could just put in 24 weeks to return the data.
This is my query below. If I can get this going I will finaly have to devide the results for each of the companies by 6 to get each companies target figures for the previous 6 months.

SELECT tblCompany.CompanyName, DateDiff("w",[DateReceived],Now()) AS 24weeks, tblA537.DateReceived, tblA537.Cancelled, tblA537.JobCategory, tblA537.AuthorisedValue
FROM ( tblA537 INNER JOIN tblDepot ON tblA537.DepotID = tblDepot.DepotID) INNER JOIN tblCompany ON tblDepot.CompanyID = tblCompany.CompanyID
WHERE (((DateDiff("w",[DateReceived],Now()))=24) AND (( tblA537.JobCategory) Not Like 'M%') AND (( tblA537.AuthorisedValue)>0));

regards,

Sid.

 

[PHV]

A Passthrough query should use the native SQL dialect, not JetSQL.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[syoung4]

Thank you for your help.
I have looked everywhere on the web to find a native Sql function to replace the Now()and any information to solve my problem. But I am not having much success.
Regards,
Sid.

 

[dhookom]

GetDate() is basically the same as Now().

Duane MS Access MVP
[green]Ask a great question, get a great answer.[/green] [red]Ask a vague question, get a vague answer.[/red]
[green]Find out how to get great answers faq219-2884.[/green]

 

[r937]

sid, what database system are you passing the query through to? oracle? mysql? db2? informix? sybase?

r937.com | rudy.ca