I'd suggest you've definitely got something wrong with your criteria in your
query: you're getting the same criteria showing up 2 and 3 times in that SQL
statement.
Look at the first few lines of that SQL:
WHERE ((([Call Log].Status)="closed") AND (([Call Log].DateClosed) Between
[forms]![Reports Menu]![StartDate] And [forms]![Reports Menu]![EndDate] And
([Call Log].DateClosed) Between [forms]![Reports Menu]![StartDate] And
[forms]![Reports Menu]![EndDate]) AND (([Call
Log].Category)=[forms]![reports
menu]![category]) AND (([Call Log].SubCat)=[forms]![reports
menu]![subcat]))
You've got [Call Log].Status = "closed" AND [Call
Log].Category=[forms]![reports
menu]![category]
That will only work if the category control on the reports menu form is set
to closed, so there's really no reason for both. You've also got two
separate references there to [Call Log].DateClosed) Between
[forms]![Reports Menu]![StartDate] And [forms]![Reports Menu]![EndDate]
(plus more of them further on in the query)
See whether you can simplify that query yourself. If not, post back with the
exact details of what it should be doing, what controls you have on the form
that you're trying to refer back to and we'll see what we can do to help.
Since we've already gone fairly deep in this particular post, I'd suggest
posting it as a new post.
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
SJH said:
Thanks.. as requested:
SELECT [Call Log].*, [First Name] & " " & [surname] AS Caller
FROM Users INNER JOIN [Call Log] ON Users.UserID = [Call Log].UserID
WHERE ((([Call Log].Status)="closed") AND (([Call Log].DateClosed) Between
[forms]![Reports Menu]![StartDate] And [forms]![Reports Menu]![EndDate] And
([Call Log].DateClosed) Between [forms]![Reports Menu]![StartDate] And
[forms]![Reports Menu]![EndDate]) AND (([Call Log].Category)=[forms]![reports
menu]![category]) AND (([Call Log].SubCat)=[forms]![reports menu]![subcat]))
OR ((([Call Log].Status)="closed") AND (([Call Log].DateClosed) Between
[forms]![Reports Menu]![StartDate] And [forms]![Reports Menu]![EndDate]) AND
(([Call Log].Category)=[forms]![reports menu]![category]) AND
(([forms]![reports menu]![subcat]) Is Null)) OR ((([Call
Log].Status)="closed") AND (([Call Log].DateClosed) Between [forms]![Reports
Menu]![StartDate] And [forms]![Reports Menu]![EndDate] And ([Call
Log].DateClosed) Between [forms]![Reports Menu]![StartDate] And
[forms]![Reports Menu]![EndDate]) AND (([forms]![reports menu]![subcat]) Is
Null) AND (([forms]![reports menu]![category]) Is Null)) OR ((([Call
Log].Status)="closed") AND (([Call Log].DateClosed) Between [forms]![Reports
Menu]![StartDate] And [forms]![Reports Menu]![EndDate]) AND (([Call
Log].SubCat)=[forms]![reports menu]![subcat]) AND (([forms]![reports
menu]![category]) Is Null));
Douglas J. Steele said:
What does the SQL for your query look like? (with the query open in Design
mode, select View | SQL View from the menu. Copy the SQL that appears into
your post)
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Yes, exactly the same syntax, it is in the same query entered on the same
line.
:
Did you try using the same criteria related to your SubCat combo box?
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
Scrap that sorry - I copied and pasted it and got this error. I redid
by
typing manually and this seems to have worked now THANKS! If the
fields
are
left blank it now shows all data regardless of category and subcat.
There is only one more thing which isn't working, if the user chooses
a
Category but leaves the SubCat combo box blank (to view all categories
regardless of the
subcategory) this does not produce any results?
:
Thanks, as suggested I put this:
= [forms]![Reports Menu]![Category] OR [forms]![Reports
Menu]![Category]
IS
Null
in the query but received an error "You can use the IS operator only
in
an
expression with Null or Not Null"
Sue
:
You can't use an IIf statement like that.
Try using
= [forms]![Reports Menu]![Category] OR [forms]![Reports
Menu]![Category] IS
NULL
as the criteria
--
Doug Steele, Microsoft Access MVP
(no e-mails, please!)
I have two combo boxes on a form for Category and SubCat, I have
a
report
based on a query which uses this criteria to display its
results.
What I
want
to do though is add the functionality to display all categories
or/and
subcat's if one of the fields is left blank. I know I can
probably
do this
by
having extra queries and reports and calling them if the field
is
null but
I
would like to do it from the one query - is this possible with
an
IIf
statement maybe?
I have tried the following in the query criteria on one of the
fields:
IIf(IsNull([forms![Reports Menu]![Category],like
"*",[forms![![Reports
Menu]![Category])
The False argument still works but if true the query does not
return
any
rows.
Any ideas?
Thanks