IIf statement blank field returns all data

G

Guest

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
 
D

Douglas J. Steele

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
 
G

Guest

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

Douglas J. Steele said:
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!)



SJH said:
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
 
G

Guest

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?

SJH said:
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

Douglas J. Steele said:
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!)



SJH said:
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
 
D

Douglas J. Steele

Did you try using the same criteria related to your SubCat combo box?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



SJH said:
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?

SJH said:
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

Douglas J. Steele said:
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
 
G

Guest

Yes, exactly the same syntax, it is in the same query entered on the same
line.

Douglas J. Steele said:
Did you try using the same criteria related to your SubCat combo box?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



SJH said:
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?

SJH said:
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
 
D

Douglas J. Steele

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!)



SJH said:
Yes, exactly the same syntax, it is in the same query entered on the same
line.

Douglas J. Steele said:
Did you try using the same criteria related to your SubCat combo box?

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



SJH said:
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
 
G

Guest

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!)



SJH said:
Yes, exactly the same syntax, it is in the same query entered on the same
line.

Douglas J. Steele said:
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
 
D

Douglas J. Steele

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!)



SJH said:
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
 
G

Guest

OK thanks.. will do.

I am not sure why you say the criteria will only work if category = closed
though as it is the status field only that I am checking for 'closed'?

If I can't get it to work I will post a new post with further details of
what I am trying to achieve. Like I said it nearly works. If the category and
sub-cat fields are blank the report shows all call logs that are status
'closed' and if a user chooses a particular Category and Sub-Cat this also
works, it is just if they leave the sub-cat field blank it doesn't return
anything when I want it to return all calls under the Category chosen
regardless of the sub-cat field.

Thanks for trying.. I will try and re-do the sql again.

Cheers

Sue

Douglas J. Steele said:
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
 
D

Douglas J. Steele

I must have misread something. I looked again, and agree that you're right
to be puzzled by my comment!

Sorry about that.

--
Doug Steele, Microsoft Access MVP

(no e-mails, please!)



SJH said:
OK thanks.. will do.

I am not sure why you say the criteria will only work if category = closed
though as it is the status field only that I am checking for 'closed'?

If I can't get it to work I will post a new post with further details of
what I am trying to achieve. Like I said it nearly works. If the category and
sub-cat fields are blank the report shows all call logs that are status
'closed' and if a user chooses a particular Category and Sub-Cat this also
works, it is just if they leave the sub-cat field blank it doesn't return
anything when I want it to return all calls under the Category chosen
regardless of the sub-cat field.

Thanks for trying.. I will try and re-do the sql again.

Cheers

Sue

Douglas J. Steele said:
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));

:

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top