Date query

[mojo1979]

Hi,

I am trying to create a simple query which queries a table with 4 fields - name, project, start date and finish date. I want to be able to enter a start and finish date and for the query to pull up name, project etc for those specified dates. How do I do this?

Thanks

 

[SkipVought]

Hi,


"...to pull up name, project etc for those specified dates."

Do you mean ONLY the rows ...

where the [Start Date] is equal to the [Specified Start Date] and the [End Date] is equal to the [Specified End Date]

or

where the [Start Date] is less than or equal to the [Specified End Date] and the [End Date] is greater than or equal to the [Specified Start Date]?

Skip,

_[red][/red]

 

[mojo1979]

I mean 'between', hence if I enter 1/1/2007 (as the start) and 12/12/2007 (finish) then I will be shown all the projects which fell in between those dates....

 

[PWise]

Where StartDate Between #1/1/7# and #12/12/7#
or EndDate Between #1/1/7# and #12/12/7#
or #1/1/7# Between StartDate and EndDate


For Projects that Started between 1/1/7 and 12/12/7 and ended whenever


For Projects that Ended between 1/1/7 and 12/12/7 and started before 1/1/7


For Projects that Started Before 1/1/7 and ended after 12/12/7

 

[mojo1979]

i used 1/1/7 and 12/12/07 as example dates, in theory the user would input any date not just these two.

In the query design view where would i in type the expression? In Start date field or finish date field?

Thanks

 

[PWise]

PARAMETERS [Enter Start Date] DateTime, [enter End Date] DateTime;
SELECT name, Project, Projects.startdate, Projects.enddate
FROM Projects
Where Startdate Between [Enter Start Date] and [enter End Date] 
or    Enddate Between [Enter Start Date] and [enter End Date] 
or [Enter Start Date] Between Startdate and Enddate

 

[mojo1979]

This is what i have at the moment:

SELECT [Staffing by Project].Project, [Staffing by Project].Location, [Staffing by Project].[Start Date], [Staffing by Project].[Finish Date], [Staffing by Project].PP, [Staffing by Project].[PP Utilisation], [Staffing by Project].PR, [Staffing by Project].[PR Utilisation], [Staffing by Project].MGR, [Staffing by Project].[MGR Utilisation], [Staffing by Project].SA, [Staffing by Project].[SA Utilisation], [Staffing by Project].AS, [Staffing by Project].[AS Utilisation], [Staffing by Project].AN, [Staffing by Project].[AN Utilisation], [Staffing by Project].Total, [Staffing by Project].[Project Team]
FROM [Staffing by Project];

So I guess all I need to do is add the PARAMETERS statement from your suggestion above?

 

[PWise]

yes

And the where Clause

 

[mojo1979]

Thanks alot!

 

[mojo1979]

I may be doing something wrong because with this:

PARAMETERS [Enter Start Date] DateTime, [enter End Date] DateTime;
WHERE Startdate Between [Enter Start Date] and [enter End Date]
or Enddate Between [Enter Start Date] and [enter End Date]
or [Enter Start Date] Between Startdate and Enddate
SELECT [Staffing by Consultant].[Consultant Initals], [Staffing by Consultant].[Consultant Level], [Staffing by Consultant].Project, [Staffing by Consultant].[Partner in charge], [Staffing by Consultant].Mentor, [Staffing by Consultant].[Project Code], [Staffing by Consultant].[Project Start Date], [Staffing by Consultant].[Project Finish Date], [Staffing by Consultant].Utilization, [Staffing by Consultant].Location
FROM [Staffing by Consultant];


I get error messages....

 

[PHV]

The syntax is:
PARAMETERS ...;
SELECT ...
FROM ...
WHERE ...

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[mojo1979]

ok, sorry for being a bit painful here, but am I supposed to include only one of the following lines or all of them:

Where Startdate Between [Enter Start Date] and [enter End Date]
or Enddate Between [Enter Start Date] and [enter End Date]
or [Enter Start Date] Between Startdate and Enddate


Because if I include all of them I get asked numerous times to enter start and finish dates

Thanks for your patience!

 

[PHV]

Use [Project Start Date] instead of Stardate (similar thing with Enddate.
In fact, try to make sense with the suggestions you've got ;-)