Query and Is Null expression (1 Viewer)

[sara82]

I have a query with Product, Product Directorate, Date Received, Date Delivered, and Date approved fields.

Product field criteria: "NAT CONV"

Product Directorate criteria:
([forms]![frmWhatDates].[cboPD]) or ([forms]![frmWhatDates].[cboPD is null)

Date Received criteria: (Between [forms]![frmWhatDates].[txtRecvStart] And [forms]![frmWhatDates].[txtRecvEnd]) or(([forms]![frmWhatDates].[txtRecvStart] is null) or ([forms]![frmWhatDates].[txtRecvEnd] is null))

and so forth

When I closed my query, and opened it up again it looked bizarre. Because of the Is Null it added criteria multiple times.. I don't know what's going on but this makes it hard if i want to edit it in the future.

This is what it looks like:

Why did Access do this to my query? Is there anyway I can make this simpler and how?

What I have is a form that filters records according to what is input and it generates a report.
If that date fields are left blank.. I want it to display all the records

 

[edtab]

Product Directorate criteria:
([forms]![frmWhatDates].[cboPD]) or ([forms]![frmWhatDates].[cboPD is null)

If you examine the above entry, you will notice that you are missing a
closing square bracket in "cboPD". I think this might be your problem.

 

[Jon K]

When I closed my query, and opened it up again it looked bizarre.

It's a query Design View problem. The Design View has to re-arrange the criteria according to all the AND and OR operators and put all the related ANDs in one row, building as many rows as necessary.


You can put each criterion with the "OR a form control Is Null" expression in a new column in the query grid like this (one new column for one field):
------------------------------------
Field: [ProductDirectorate]=[forms]![frmWhatDates].[cboPD] or [forms]![frmWhatDates].[cboPD] is null

Show: uncheck

Criteria: True

------------------------------------
Field: [Date Received] Between [forms]![frmWhatDates].[txtRecvStart] And [forms]![frmWhatDates].[txtRecvEnd] or [forms]![frmWhatDates].[txtRecvStart] is null or [forms]![frmWhatDates].[txtRecvEnd] is null

Show: uncheck

Criteria: True


This way, Access will leave the criteria intact when the query is closed, making it easy for you to subsequently edit or add new criteria to the query.
.

 

[sara82]

Jon K:

Thank you so much!! That worked perfectly and it will make it a whole lot easier if I need to add criteria.
Thanks again.. I'm learning new things about Access everyday

 

[Pat Hartman]

Jon K's example omitted your parentheses. Put them back (at least the necessary ones).

(Between [forms]![frmWhatDates].[txtRecvStart] And [forms]![frmWhatDates].[txtRecvEnd]) or([forms]![frmWhatDates].[txtRecvStart] is null or [forms]![frmWhatDates].[txtRecvEnd] is null)

 

[Keith27]

Originally posted by Pat Hartman
Jon K's example omitted your parentheses. Put them back (at least the necessary ones).

(Between [forms]![frmWhatDates].[txtRecvStart] And [forms]![frmWhatDates].[txtRecvEnd]) or([forms]![frmWhatDates].[txtRecvStart] is null or [forms]![frmWhatDates].[txtRecvEnd] is null)

Probably Pat has overlooked there is a Between there!


In any expression, the operator AND takes precedence over OR. So in an expression that contains a mixture of AND and OR we would sometimes use brackets to alter the order of operation, if necessary.


But BETWEEN ... AND must be used together and is considered as one operator, so the expression:

[Date Received] Between [forms]![frmWhatDates].[txtRecvStart] And [forms]![frmWhatDates].[txtRecvEnd] or [forms]![frmWhatDates].[txtRecvStart] is null or [forms]![frmWhatDates].[txtRecvEnd] is null

doesn't contain a mixture of AND and OR. So any brackets there are unnecessary.
.

 

[Pat Hartman]

Yup, didn't notice the between.