multiple date ranges

[GiBB]

Hello,

I am trying to create a query to search for various date ranges in a table.

to make it easier for the user i am trying to create the query so they only
need to open one query which shows pop ups for "start dates for field one"
"end dates for field one" "start dates for field two" "end dated for field
two" etc and if they only enter details in one set of pop ups (say they only
fill in the start and end dates for field one and left two, three and four
blank) it will on display results for that set of ranges only.

my sql is as follows:

SELECT ChangeDetails.[Change ID], ChangeDetails.Region, ChangeDetails.Corp,
ChangeDetails.Priority, ChangeDetails.System, ChangeDetails.FunctionalArea,
ChangeDetails.[Changes Made], ChangeDetails.[Authorised By],
ChangeDetails.[Authorised Date], ChangeDetails.[1st Checker],
ChangeDetails.[1st Check Date], ChangeDetails.[2nd Checker],
ChangeDetails.[2nd check Date], ChangeDetails.Implemented,
ChangeDetails.ImplementedDate, ChangeDetails.[Detail Attached],
ChangeDetails.[Details of Change]
FROM ChangeDetails
WHERE

(((ChangeDetails.[Authorised Date]) Between [Requested start date] And
[Requested end date]) or (([Requested start date]) Is Null) and (([Requested
end date]) Is Null)

and

(((ChangeDetails.[1st Check Date]) Between [First check start date] And
[First check end date])) or (([First check start date]) Is Null) and
(([First check end date]) Is Null)

and

(((ChangeDetails.[2nd check Date]) Between [Second check start date] And
[Second check end date])) or (([Second check start date]) Is Null) and
(([Second check end date]) Is Null)

and

(((ChangeDetails.ImplementedDate) Between [Done date start] And [Done date
end])) or (([Done date start]) Is Null) AND (([Done date end]) Is Null))



which it is partialy doing what i want as in i can search for all four field
ranges at once, however if i leave any of the popups blank it will either
show all the records in the table or it will not show any records at all even
if i define criteria which should only bring up one "test" record.

please help.

Cheers

 

[Amy Blankenship]

Hello,

I am trying to create a query to search for various date ranges in a
table.

to make it easier for the user i am trying to create the query so they
only
need to open one query which shows pop ups for "start dates for field one"
"end dates for field one" "start dates for field two" "end dated for field
two" etc and if they only enter details in one set of pop ups (say they
only
fill in the start and end dates for field one and left two, three and four
blank) it will on display results for that set of ranges only.

my sql is as follows:

SELECT ChangeDetails.[Change ID], ChangeDetails.Region,
ChangeDetails.Corp,
ChangeDetails.Priority, ChangeDetails.System,
ChangeDetails.FunctionalArea,
ChangeDetails.[Changes Made], ChangeDetails.[Authorised By],
ChangeDetails.[Authorised Date], ChangeDetails.[1st Checker],
ChangeDetails.[1st Check Date], ChangeDetails.[2nd Checker],
ChangeDetails.[2nd check Date], ChangeDetails.Implemented,
ChangeDetails.ImplementedDate, ChangeDetails.[Detail Attached],
ChangeDetails.[Details of Change]
FROM ChangeDetails
WHERE

(((ChangeDetails.[Authorised Date]) Between [Requested start date] And
[Requested end date]) or (([Requested start date]) Is Null) and
(([Requested
end date]) Is Null)

and

(((ChangeDetails.[1st Check Date]) Between [First check start date] And
[First check end date])) or (([First check start date]) Is Null) and
(([First check end date]) Is Null)

and

(((ChangeDetails.[2nd check Date]) Between [Second check start date] And
[Second check end date])) or (([Second check start date]) Is Null) and
(([Second check end date]) Is Null)

and

(((ChangeDetails.ImplementedDate) Between [Done date start] And [Done date
end])) or (([Done date start]) Is Null) AND (([Done date end]) Is Null))



which it is partialy doing what i want as in i can search for all four
field
ranges at once, however if i leave any of the popups blank it will either
show all the records in the table or it will not show any records at all
even
if i define criteria which should only bring up one "test" record.

This should help

http://support.microsoft.com/kb/290178/

 

[Jerry Whittle]

Amy has shown you an answer; however, the root problem is that your tables
are not normalized properly. If your data was properly normalized, this would
probably be a very simple query. Consider this: What happens if the business
rules change and you now have another Checker and Check date? Probably your
queries, forms, and reports would all need major revisions.

I highly suggest getting some relational database training or reading
"Database Design for Mere Mortals" by Hernandez before proceeding any further
on this database.
--
Jerry Whittle, Microsoft Access MVP
Light. Strong. Cheap. Pick two. Keith Bontrager - Bicycle Builder.

Hello,

I am trying to create a query to search for various date ranges in a table.

to make it easier for the user i am trying to create the query so they only
need to open one query which shows pop ups for "start dates for field one"
"end dates for field one" "start dates for field two" "end dated for field
two" etc and if they only enter details in one set of pop ups (say they only
fill in the start and end dates for field one and left two, three and four
blank) it will on display results for that set of ranges only.

my sql is as follows:

SELECT ChangeDetails.[Change ID], ChangeDetails.Region, ChangeDetails.Corp,
ChangeDetails.Priority, ChangeDetails.System, ChangeDetails.FunctionalArea,
ChangeDetails.[Changes Made], ChangeDetails.[Authorised By],
ChangeDetails.[Authorised Date], ChangeDetails.[1st Checker],
ChangeDetails.[1st Check Date], ChangeDetails.[2nd Checker],
ChangeDetails.[2nd check Date], ChangeDetails.Implemented,
ChangeDetails.ImplementedDate, ChangeDetails.[Detail Attached],
ChangeDetails.[Details of Change]
FROM ChangeDetails
WHERE

(((ChangeDetails.[Authorised Date]) Between [Requested start date] And
[Requested end date]) or (([Requested start date]) Is Null) and (([Requested
end date]) Is Null)

and

(((ChangeDetails.[1st Check Date]) Between [First check start date] And
[First check end date])) or (([First check start date]) Is Null) and
(([First check end date]) Is Null)

and

(((ChangeDetails.[2nd check Date]) Between [Second check start date] And
[Second check end date])) or (([Second check start date]) Is Null) and
(([Second check end date]) Is Null)

and

(((ChangeDetails.ImplementedDate) Between [Done date start] And [Done date
end])) or (([Done date start]) Is Null) AND (([Done date end]) Is Null))



which it is partialy doing what i want as in i can search for all four field
ranges at once, however if i leave any of the popups blank it will either
show all the records in the table or it will not show any records at all even
if i define criteria which should only bring up one "test" record.

please help.

Cheers

 

[GiBB]

Thank you both.

i have and am mentioning to my manager that i am in need of some training
with access as i only have basic knowlege from high school/colllege.

I have been looking into normalizing the database as this is one i have just
inherited from my manager, but at the moment this is just a quick fix on the
old database to keep him happy while i try and create a new database.

Also i have followed the details on the page suggested, and i now have the
query working.

one last question, can anyone else suggest any other good access or possibly
sql books/resources as there are so many on the market.

cheers