convert sql to access queries

[slimjen]

All, I have an archive query that I got to work successfully in SQL 2008. When I converted it to us in Access 2003 query pass thru; its doesn't work.

SELECT dbo_tblSPInvoice.txtINVOICENUM, dbo.tblSPInvoice.txtCARRIERID, dbo_tblSPInvoice.txtCARRIERNAME, dbo_tblSPInvoice.txtCARRIERACCTNUM, dbo_tblSPInvoice.dteDATAENTDTE
dbo_tblSPInvoice.dteINVOICEDTE, dbo_tblSPInvoice.dteMONTHPAID
FROM dbo_tblSPInvoice
WHERE DATEDIFF("d", [dteMONTHPAID],getdate ())>90;

I changed the . to _. It seems to be failing at "where"
Can someone point out whats the problem please.
Thanks

[pbaldy]

If that's being run in an Access query (not a pass through), getdate would need to be Date (or Now if you want the time component).

[slimjen]

Thank you for replying. I may want to change this to a pass thru in the future so I would want both ways. I'd appreciate your input.

[pbaldy]

No problem. Just use the function appropriate to the engine; Getdate() in SQL Server, Now() or Date() in Access.

[slimjen]

Will do. Thanks so much.

[slimjen]

Ok I tried:

Date()>"90"

in the date field query design; but it did not return the expected records. I m thinking it's because the table is a sql backend and the date is formatted as yyyy-mm-dd and access doesn't recognize because it thinks its a string???? And why is the "" around 90?
I don't know. Anybody?

[pbaldy]

If Access added the quotes, it's seeing the field as text, not a date. If that's all you have, it wouldn't be a valid comparison anyway. Your original SQL looked correct, so you might convert this to SQL view and see what it's producing.