The AND and OR phrases in the WHERE clause are ambiguous, since:
(X AND Y) OR Z
is not the same as:
X AND (Y OR Z)
It is also a good idea to declare the paramters (especially the dates) so
they are understood correctly. You can do that through Parameters on the
Query menu in query design.
I think this is what you are after:
PARAMETERS [Forms]![Artwork query]![Company] Text (255),
[Forms]![Artwork query]![Category] Text(255),
[Forms]![Artwork query]![StartDate] DateTime,
[Forms]![Artwork query]![EndDate] DateTime;
SELECT Company.Company,
[Artwork approval].InStoreDate,
[Artwork approval].Retailer,
[Artwork approval].Character,
[Artwork approval].Category,
[Artwork approval].Units,
[Artwork approval].Price,
[Artwork approval].CostPrice,
[Artwork approval].Sales,
[Artwork approval].Royalty
FROM Company INNER JOIN [Artwork approval]
ON Company.CompanyID = [Artwork approval].CompanyID
WHERE (Company.Company Like [Forms]![Artwork query]![Company] & "*")
AND ([Artwork approval].Category Like [Forms]![Artwork query]![Category] &
"*")
AND (([Forms]![Artwork query]![StartDate] Is Null)
Or ([Forms]![Artwork query]![EndDate] Is Null)
Or ([Artwork approval].InStoreDate)
Between [Forms]![Artwork query]![StartDate]
And [Forms]![Artwork query]![EndDate]));
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
message
Thanks Allen,
I thought I'd solved this. Now I find that other criteria are not working.
I'm sure it's syntax related, but can't seem to work out where I'm going
wrong.
The full query is as follows;
SELECT Company.Company, [Artwork approval].InStoreDate, [Artwork
approval].Retailer, [Artwork approval].Character, [Artwork
approval].Category, [Artwork approval].Units, [Artwork approval].Price,
[Artwork approval].CostPrice, [Artwork approval].Sales, [Artwork
approval].Royalty
FROM Company INNER JOIN [Artwork approval] ON Company.CompanyID = [Artwork
approval].CompanyID
WHERE (((Company.Company) Like [Forms]![Artwork query]![Company] & "*")
AND
(([Forms]![Artwork query]![StartDate] Is Null) Or ([Forms]![Artwork
query]![EndDate] Is Null) Or ([Artwork approval].InStoreDate) Between
[Forms]![Artwork query]![StartDate] And [Forms]![Artwork query]![EndDate])
AND (([Artwork approval].Category) Like [Forms]![Artwork query]![Category]
&
"*"));
The date part works fine, but at the expense of other criteria!?
Allen Browne said:
Switch the query to SQL View (View menu)
You will see the WHERE clause something like this:
WHERE [Table1].[MyDateField] Between
[Forms]![Artwork query]![StartDate]
And [Forms]![Artwork query]![EndDate]
Change it to:
WHERE (([Forms]![Artwork query]![StartDate] Is Null)
OR ([Forms]![Artwork query]![EndDate] Is Null)
OR ([Table1].[MyDateField] Between
[Forms]![Artwork query]![StartDate]
And [Forms]![Artwork query]![EndDate]))
If either text box is left blank, the query will return all records. If
they
both have a date, only records between the 2 dates are returned.
If this query is the source for a report, it might be more efficient to
leave the criteria out of the query, and use the WhereCondition of
OpenReport instead. For details, see:
Limiting a Report to a Date Range
at:
http://allenbrowne.com/casu-08.html
--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Reply to group, rather than allenbrowne at mvps dot org.
Graeme at Raptup said:
I am using parameters to run a query.
I would like to set the second parameter below so that like (1) below
it
is
not required and will output all records if it is left blank.
Can this be done without too much hassle?
(1) Like [Forms]![Artwork query]![Company] & "*"
(2) Between [Forms]![Artwork query]![StartDate] And [Forms]![Artwork
query]![EndDate]
