Query criteria - exclude data and return all records if null

[CS]

I'm trying to set up a form to allow the user to exclude certain transactions
if they enter the transaction code to exclude, but if they leave the field
blank, then that means to return all transactions (ie. do not exclude any
records). I tried the following query criteria which works only if an entry
is made (ie. specify the transaction code to exclude), but if the field is
blank, then the query returns no records at all.

Not Like NZ([Forms]![Actual_Monthly_Data]![Exclude Transaction Type],"*")
And Is Not Null

Any ideas how to make the query return all records if the field is blank?

 

[Marshall Barton]

I'm trying to set up a form to allow the user to exclude certain transactions
if they enter the transaction code to exclude, but if they leave the field
blank, then that means to return all transactions (ie. do not exclude any
records). I tried the following query criteria which works only if an entry
is made (ie. specify the transaction code to exclude), but if the field is
blank, then the query returns no records at all.

Not Like NZ([Forms]![Actual_Monthly_Data]![Exclude Transaction Type],"*")
And Is Not Null

Any ideas how to make the query return all records if the field is blank?

Try using this kind of criteria:

<>Forms!Actual_Monthly_Data![Exclude Transaction Type] OR
Forms!Actual_Monthly_Data![Exclude Transaction Type] Is Null

 

[CS]

It didn't work... I'm actually trying to do something more complicated. I've
got a form with multiple criteria. What I want is for this one criteria to
exclude a certain transaction code if it is entered, or show all transaction
codes if none is entered. Here's the full SQL code for the whole query:

SELECT FMSDATA_GL101T01_V.TP_DEPT_GROUP, FMSDATA_GL101T01_V.TP_PAYPERIOD,
Right([S_CHARGE_CODE],12) AS [Charge Code], Right([S_CHARGE_CODE],4) AS [Obj
Detail], FMSDATA_GL101T01_V.SUBSYSTEM, FMSDATA_GL101T01_V.TP_TRANS_TYPE,
[CSUN_Transaction Type].Abbrev, [CSUN_Transaction Type].Description,
FMSDATA_GL101T01_V.TP_TRAN_DATE, Sum(FMSDATA_GL101T01_V.EXTENDED_AMOUNT) AS
SumOfEXTENDED_AMOUNT, FMSDATA_GL101T01_V.DESCRIPTION,
FMSDATA_GL101T01_V.S_STATUS, FMSDATA_GL001M01_V.S_SOURCE,
FMSDATA_GL001M01_V.AC_ACCOUNT, FMSDATA_GL001M01_V.TITLE
FROM ([CSUN_Transaction Type] INNER JOIN FMSDATA_GL101T01_V ON
[CSUN_Transaction Type].[Transaction Type] =
FMSDATA_GL101T01_V.TP_TRANS_TYPE) INNER JOIN FMSDATA_GL001M01_V ON
(FMSDATA_GL101T01_V.TP_DEPT_GROUP = FMSDATA_GL001M01_V.DEPT_GROUP) AND
(FMSDATA_GL101T01_V.TP_CHARGE_CODE = FMSDATA_GL001M01_V.CHARGE_CODE)
GROUP BY FMSDATA_GL101T01_V.TP_DEPT_GROUP, FMSDATA_GL101T01_V.TP_PAYPERIOD,
Right([S_CHARGE_CODE],12), Right([S_CHARGE_CODE],4),
FMSDATA_GL101T01_V.SUBSYSTEM, FMSDATA_GL101T01_V.TP_TRANS_TYPE,
[CSUN_Transaction Type].Abbrev, [CSUN_Transaction Type].Description,
FMSDATA_GL101T01_V.TP_TRAN_DATE, FMSDATA_GL101T01_V.DESCRIPTION,
FMSDATA_GL101T01_V.S_STATUS, FMSDATA_GL001M01_V.S_SOURCE,
FMSDATA_GL001M01_V.AC_ACCOUNT, FMSDATA_GL001M01_V.TITLE,
FMSDATA_GL101T01_V.TP_DEPT_GROUP
HAVING
(((FMSDATA_GL101T01_V.TP_DEPT_GROUP)=[Forms]![Actual_Monthly_Data]![TP_DEPT_GROUP])
AND ((FMSDATA_GL101T01_V.TP_PAYPERIOD) Between
[Forms]![Actual_Monthly_Data]![TP_PAYPERIOD_1] And
[Forms]![Actual_Monthly_Data]![TP_PAYPERIOD_2]) AND
((Right([S_CHARGE_CODE],12)) Like
NZ([Forms]![Actual_Monthly_Data]![CHARGE_CODE],"*") And
(Right([S_CHARGE_CODE],12)) Is Not Null) AND ((Right([S_CHARGE_CODE],4))
Between [Forms]![Actual_Monthly_Data]![Obj_Detail_1] And
[Forms]![Actual_Monthly_Data]![Obj_Detail_2]) AND
((FMSDATA_GL101T01_V.TP_TRANS_TYPE)<>[Forms]![Actual_Monthly_Data]![Exclude
Transaction Type]) AND ((FMSDATA_GL001M01_V.S_SOURCE) Between
[Forms]![Actual_Monthly_Data]![S_SOURCE_1] And
[Forms]![Actual_Monthly_Data]![S_SOURCE_2]) AND
((FMSDATA_GL001M01_V.AC_ACCOUNT) Between
[Forms]![Actual_Monthly_Data]![AC_ACCOUNT_1] And
[Forms]![Actual_Monthly_Data]![AC_ACCOUNT_2]))
ORDER BY FMSDATA_GL101T01_V.TP_DEPT_GROUP, FMSDATA_GL101T01_V.TP_PAYPERIOD,
Right([S_CHARGE_CODE],12);

I'm trying to set up a form to allow the user to exclude certain transactions
if they enter the transaction code to exclude, but if they leave the field
blank, then that means to return all transactions (ie. do not exclude any
records). I tried the following query criteria which works only if an entry
is made (ie. specify the transaction code to exclude), but if the field is
blank, then the query returns no records at all.

Not Like NZ([Forms]![Actual_Monthly_Data]![Exclude Transaction Type],"*")
And Is Not Null

Any ideas how to make the query return all records if the field is blank?

Try using this kind of criteria:

<>Forms!Actual_Monthly_Data![Exclude Transaction Type] OR
Forms!Actual_Monthly_Data![Exclude Transaction Type] Is Null

 

[Marshall Barton]

"It didn't work" is a completely useless sentence to solve a
problem, especially when you didn't even post the query that
you tried.

In looking at your query, I see that you are using a HAVING
clause inappropriately. What you have in the HAVING clause
should be in a WHERE clause.
--
Marsh
MVP [MS Access]

It didn't work... I'm actually trying to do something more complicated. I've
got a form with multiple criteria. What I want is for this one criteria to
exclude a certain transaction code if it is entered, or show all transaction
codes if none is entered. Here's the full SQL code for the whole query:

SELECT FMSDATA_GL101T01_V.TP_DEPT_GROUP, FMSDATA_GL101T01_V.TP_PAYPERIOD,
Right([S_CHARGE_CODE],12) AS [Charge Code], Right([S_CHARGE_CODE],4) AS [Obj
Detail], FMSDATA_GL101T01_V.SUBSYSTEM, FMSDATA_GL101T01_V.TP_TRANS_TYPE,
[CSUN_Transaction Type].Abbrev, [CSUN_Transaction Type].Description,
FMSDATA_GL101T01_V.TP_TRAN_DATE, Sum(FMSDATA_GL101T01_V.EXTENDED_AMOUNT) AS
SumOfEXTENDED_AMOUNT, FMSDATA_GL101T01_V.DESCRIPTION,
FMSDATA_GL101T01_V.S_STATUS, FMSDATA_GL001M01_V.S_SOURCE,
FMSDATA_GL001M01_V.AC_ACCOUNT, FMSDATA_GL001M01_V.TITLE
FROM ([CSUN_Transaction Type] INNER JOIN FMSDATA_GL101T01_V ON
[CSUN_Transaction Type].[Transaction Type] =
FMSDATA_GL101T01_V.TP_TRANS_TYPE) INNER JOIN FMSDATA_GL001M01_V ON
(FMSDATA_GL101T01_V.TP_DEPT_GROUP = FMSDATA_GL001M01_V.DEPT_GROUP) AND
(FMSDATA_GL101T01_V.TP_CHARGE_CODE = FMSDATA_GL001M01_V.CHARGE_CODE)
GROUP BY FMSDATA_GL101T01_V.TP_DEPT_GROUP, FMSDATA_GL101T01_V.TP_PAYPERIOD,
Right([S_CHARGE_CODE],12), Right([S_CHARGE_CODE],4),
FMSDATA_GL101T01_V.SUBSYSTEM, FMSDATA_GL101T01_V.TP_TRANS_TYPE,
[CSUN_Transaction Type].Abbrev, [CSUN_Transaction Type].Description,
FMSDATA_GL101T01_V.TP_TRAN_DATE, FMSDATA_GL101T01_V.DESCRIPTION,
FMSDATA_GL101T01_V.S_STATUS, FMSDATA_GL001M01_V.S_SOURCE,
FMSDATA_GL001M01_V.AC_ACCOUNT, FMSDATA_GL001M01_V.TITLE,
FMSDATA_GL101T01_V.TP_DEPT_GROUP
HAVING
(((FMSDATA_GL101T01_V.TP_DEPT_GROUP)=[Forms]![Actual_Monthly_Data]![TP_DEPT_GROUP])
AND ((FMSDATA_GL101T01_V.TP_PAYPERIOD) Between
[Forms]![Actual_Monthly_Data]![TP_PAYPERIOD_1] And
[Forms]![Actual_Monthly_Data]![TP_PAYPERIOD_2]) AND
((Right([S_CHARGE_CODE],12)) Like
NZ([Forms]![Actual_Monthly_Data]![CHARGE_CODE],"*") And
(Right([S_CHARGE_CODE],12)) Is Not Null) AND ((Right([S_CHARGE_CODE],4))
Between [Forms]![Actual_Monthly_Data]![Obj_Detail_1] And
[Forms]![Actual_Monthly_Data]![Obj_Detail_2]) AND
((FMSDATA_GL101T01_V.TP_TRANS_TYPE)<>[Forms]![Actual_Monthly_Data]![Exclude
Transaction Type]) AND ((FMSDATA_GL001M01_V.S_SOURCE) Between
[Forms]![Actual_Monthly_Data]![S_SOURCE_1] And
[Forms]![Actual_Monthly_Data]![S_SOURCE_2]) AND
((FMSDATA_GL001M01_V.AC_ACCOUNT) Between
[Forms]![Actual_Monthly_Data]![AC_ACCOUNT_1] And
[Forms]![Actual_Monthly_Data]![AC_ACCOUNT_2]))
ORDER BY FMSDATA_GL101T01_V.TP_DEPT_GROUP, FMSDATA_GL101T01_V.TP_PAYPERIOD,
Right([S_CHARGE_CODE],12);