Query criteria - exclude data and return all records if null

C

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?
 
M

Marshall Barton

CS said:
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?
Click to expand...


Try using this kind of criteria:

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

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


Marshall Barton said:
CS said:
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?
Click to expand...


Try using this kind of criteria:

<>Forms!Actual_Monthly_Data![Exclude Transaction Type] OR
Forms!Actual_Monthly_Data![Exclude Transaction Type] Is Null
Click to expand...
 
M

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);
Click to expand...
 

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

Similar Threads

Query to Find Records with only 1 transaction 6
microsoft access 1
Exclude records with no Text 6
exclude records 3
How to exclude certain records 7
Criteria not excluding data 2
two null fields 4
Exclude Records based on a joined field? 8

Top