Solved

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

Posted on 2006-06-29
7
837 Views
Last Modified: 2013-12-05

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
Comment
Question by:alexjlewis
  • 3
  • 2
7 Comments
 
LVL 12

Accepted Solution

by:
Einstine98 earned 125 total points
ID: 17008352
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
 
LVL 1

Author Comment

by:alexjlewis
ID: 17008929
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
 
LVL 1

Author Comment

by:alexjlewis
ID: 17008999
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
 
LVL 1

Author Comment

by:alexjlewis
ID: 17009343
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
 
LVL 12

Expert Comment

by:Einstine98
ID: 17013176
Cool... :-)
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
JSON is being used more and more, besides XML, and you surely wanted to parse the data out into SQL instead of doing it in some Javascript. The below function in SQL Server can do the job for you, returning a quick table with the parsed data.
With Microsoft Access, learn how to specify relationships between tables and set various options on the relationship. Add the tables: Create the relationship: Decide if you’re going to set referential integrity: Decide if you want cascade upda…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

776 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question