All records returned

  • Thread starter Thread starter Diane
  • Start date Start date
D

Diane

I created a query to prompt beginning and end dates of the
follow-up field, but it returns all records or does not
work. Here is the SQL view of my query. Can someone
please help. Thanks.

SELECT tblProposals.[SAM Proposal No], tblProposals.
[Revision No], tblProposals.[RFQ No],
tblProposals.Customer, tblCustomerList.City,
tblIndustryClassification.ClassificationName, tblProposals.
[RFQ Date In], tblProposals.[Date Out], tblProposals.
[Follow-up], tblProposals.Principal, tblProposals.
[Principal's Division], tblProposals.Description,
tblProposals.[Proposal Type], tblProposals.Value,
tblProposals.Currency, tblProposals.[SAM Responsible],
tblProposals.Status, tblProposals.Comments,
tblCustomerList.[Customer Industry]
FROM tblOffice RIGHT JOIN (tblIndustryClassification RIGHT
JOIN (tblCustomerList RIGHT JOIN tblProposals ON
tblCustomerList.Customer = tblProposals.Customer) ON
tblIndustryClassification.ClassificationName =
tblCustomerList.[Customer Industry]) ON tblOffice.Office =
tblCustomerList.Office
WHERE (((tblProposals.Follow-up) Between [Enter the
beginning date] And [enter
the end date])) OR (((tblProposals.Follow-up) Like IIf
(IsNull([Enter the
beginning date]),"*")));
 
It's the OR clause that's messing you up. I assume the user can just press
enter to on or both parameter prompts, so you need to test for Null. Try
this:

WHERE tblProposals.Follow-up Between
NZ([Enter the beginning date], #01/01/1900#) And
NZ([enter the end date], #12/31/3000#)

(This code will break after December 31, 3000, but I don't think you'll be
around to worry about that! <s>)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
 
Using this code, it now returns nothing no matter what
dates I put.
-----Original Message-----
It's the OR clause that's messing you up. I assume the user can just press
enter to on or both parameter prompts, so you need to test for Null. Try
this:

WHERE tblProposals.Follow-up Between
NZ([Enter the beginning date], #01/01/1900#) And
NZ([enter the end date], #12/31/3000#)

(This code will break after December 31, 3000, but I don't think you'll be
around to worry about that! <s>)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Diane said:
I created a query to prompt beginning and end dates of the
follow-up field, but it returns all records or does not
work. Here is the SQL view of my query. Can someone
please help. Thanks.

SELECT tblProposals.[SAM Proposal No], tblProposals.
[Revision No], tblProposals.[RFQ No],
tblProposals.Customer, tblCustomerList.City,
tblIndustryClassification.ClassificationName, tblProposals.
[RFQ Date In], tblProposals.[Date Out], tblProposals.
[Follow-up], tblProposals.Principal, tblProposals.
[Principal's Division], tblProposals.Description,
tblProposals.[Proposal Type], tblProposals.Value,
tblProposals.Currency, tblProposals.[SAM Responsible],
tblProposals.Status, tblProposals.Comments,
tblCustomerList.[Customer Industry]
FROM tblOffice RIGHT JOIN (tblIndustryClassification RIGHT
JOIN (tblCustomerList RIGHT JOIN tblProposals ON
tblCustomerList.Customer = tblProposals.Customer) ON
tblIndustryClassification.ClassificationName =
tblCustomerList.[Customer Industry]) ON tblOffice.Office =
tblCustomerList.Office
WHERE (((tblProposals.Follow-up) Between [Enter the
beginning date] And [enter
the end date])) OR (((tblProposals.Follow-up) Like IIf
(IsNull([Enter the
beginning date]),"*")));
Click to expand...


.
Click to expand...
 
Well, I've been assuming all along that Follow-up is a date/time data type.
Is it? If not, then you won't be able to do this sort of comparison. I'm
also suspicious about the string of RIGHT JOINs, but that shouldn't be
affecting the outcome because your predicate is filtering a column in the
rightmost table.

Also, you should explicitly declare the data type of the parameter - choose
Parameters from the query menu. It shouldn't matter in this case, but
sometimes it helps.

So, is Follow-up date/time? What are you trying to enter as the "from" and
"to" dates? Do you get all rows if you press Enter (Null) for both
parameters?

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Diane said:
Using this code, it now returns nothing no matter what
dates I put.
-----Original Message-----
It's the OR clause that's messing you up. I assume the user can just press
enter to on or both parameter prompts, so you need to test for Null. Try
this:

WHERE tblProposals.Follow-up Between
NZ([Enter the beginning date], #01/01/1900#) And
NZ([enter the end date], #12/31/3000#)

(This code will break after December 31, 3000, but I don't think you'll be
around to worry about that! <s>)

--
John Viescas, author
"Microsoft Office Access 2003 Inside Out" (coming soon)
"Running Microsoft Access 2000"
"SQL Queries for Mere Mortals"
http://www.viescas.com/
(Microsoft Access MVP since 1993)
Diane said:
I created a query to prompt beginning and end dates of the
follow-up field, but it returns all records or does not
work. Here is the SQL view of my query. Can someone
please help. Thanks.

SELECT tblProposals.[SAM Proposal No], tblProposals.
[Revision No], tblProposals.[RFQ No],
tblProposals.Customer, tblCustomerList.City,
tblIndustryClassification.ClassificationName, tblProposals.
[RFQ Date In], tblProposals.[Date Out], tblProposals.
[Follow-up], tblProposals.Principal, tblProposals.
[Principal's Division], tblProposals.Description,
tblProposals.[Proposal Type], tblProposals.Value,
tblProposals.Currency, tblProposals.[SAM Responsible],
tblProposals.Status, tblProposals.Comments,
tblCustomerList.[Customer Industry]
FROM tblOffice RIGHT JOIN (tblIndustryClassification RIGHT
JOIN (tblCustomerList RIGHT JOIN tblProposals ON
tblCustomerList.Customer = tblProposals.Customer) ON
tblIndustryClassification.ClassificationName =
tblCustomerList.[Customer Industry]) ON tblOffice.Office =
tblCustomerList.Office
WHERE (((tblProposals.Follow-up) Between [Enter the
beginning date] And [enter
the end date])) OR (((tblProposals.Follow-up) Like IIf
(IsNull([Enter the
beginning date]),"*")));
Click to expand...


.
Click to expand...
Click to expand...
 
Back
Top