SQL/Access date problem

[Darrenc]

Hi, i hope someone can help.

I've be trying to create a simple report using 'report server project' in Visual Studio.

The report I'm trying to create gets its data from an Access database. All I'm trying to do is get some data with the following date range:

QuotCompleteDate = Between Date() AND Date()-60

The above parameter works in access fine but the 'query designer' in visual studio doesn't play by the same set of rules. i get the error 'Invalid or missing expression'

I have simplified the parameter a bit so that I'm just looking at today's date minus 60 days.

Here is what i have tried so far:

QuoteCompleteDate < Date() -60
And i get the error 'Your entry cannot be converted to a valid date time value'

QuoteCompleteDate < NOW() -60
And i get the error 'Cannot convert entry to a valid date/time; TO_DATE function might be required'

TO_DATE(QuoteCompleteDate, 'dd-mm-yyy')
Undefined function 'TO_DATE' in expression.

QuoteCompleteDate = DATEADD(dd, 0, DATEDIFF(dd, 0, GETDATE() - 1))
Undefined function 'GET_DATE' in expression.

And lots of other variations.

I guess because I'm connecting to an access database i have to play by access's rules, but it doesn't let my use access type parameters.

Does anyone have any suggestion of what to try next?


Regards

Darren

 

[Vassago]

Is this a reporting services project? Have you tried using a getdate() instead of date?

 

[Darrenc]

Is this a reporting services project? Have you tried using a getdate() instead of date?

Thanks for the reply Vassago, yes this is a reporting services project, and unfortunately the only problem i have is when having to connect to an access backend...

Anyway, If i try getdate() i get the error 'Cannot convert to valid date/time; TO_DATE function might be required'.

If i try the TO-DATE function i get an error saying 'undefined function 'TO-DATE' in expression.

It's bit of an annoying loop. I find these type of errors most frustrating it seems like such a simple thing to try and do and yet I've spent bloody hours trying to get it to work...

 

[FoFa]

Can you use SQL's dateadd function to calculate instead?