asked on

Parameter query question (ADP front end, SQL Server 2005 backend)

I used to write parameter queries in Access MDB easily enough but have recently migrated at a ADP front-end and SQL Server 2005 back end, which I have very little experience of.

Simply, I am trying to run a report in the Access ADP which is based on a SQL view which contains dates, in a column named [Date]. I want to prompt the user for a date range and return the filtered records.

What is the best way to achieve this please?

(I am currently returning all records to the report by specifying the following in the Record Source property of the report: SELECT * FROM viewMaterialsConsumed)

ASKER CERTIFIED SOLUTION

Einstine98

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

alexjlewis

ASKER

Thanks Einstine98, but I already tried that, and although the report does ask for the dates when opening it, the records are not filtered - it ruturns all records.

Any ideas?

alexjlewis

ASKER

OK - it IS filtering but...

If I enter a date like 15/01/2006, it accepts it as 15th January (intended), but if i enter 2/01/2006, it think I mean 1st February, not the 2nd of January as intended.

How can I resolve this?

alexjlewis

ASKER

Ok, got it. I need to convert to the correct date time format with the following included in my SQL statement:

WHERE (Date BETWEEN CONVERT(DATETIME, @[Start Date], 103) AND CONVERT(DATETIME, @[End Date], 103))

Thanks anyway for your help.

Einstine98

Cool... :-)