Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2006-06-29
7
Medium Priority
?
842 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
7 Comments
 
LVL 12

Accepted Solution

by:
Einstine98 earned 500 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

Will your db performance match your db growth?

In Percona’s white paper “Performance at Scale: Keeping Your Database on Its Toes,” we take a high-level approach to what you need to think about when planning for database scalability.

Question has a verified solution.

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

In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Microsoft Access is a place to store data within tables and represent this stored data using multiple database objects such as in form of macros, forms, reports, etc. After a MS Access database is created there is need to improve the performance and…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

705 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