• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 853
  • Last Modified:

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)
0
alexjlewis
Asked:
alexjlewis
  • 3
  • 2
1 Solution
 
Einstine98Commented:
SELECT *
FROM viewMaterialsConsumed
WHERE [DATE] Between @StartDate and @EndDate

@StartDate and @EndDate are variables that you have to define and pass back to the stored procedure/query
0
 
alexjlewisAuthor Commented:
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?
0
 
alexjlewisAuthor Commented:
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?
0
 
alexjlewisAuthor Commented:
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.
0
 
Einstine98Commented:
Cool... :-)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

What Kind of Coding Program is Right for You?

There are many ways to learn to code these days. From coding bootcamps like Flatiron School to online courses to totally free beginner resources. The best way to learn to code depends on many factors, but the most important one is you. See what course is best for you.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now