query criteria

[JamesDeckert]

I have a criteria test in the [DR].[Received Date] field of a query. The
criteria is based on data which is entered into a form.

=(IIf(IsNull([Forms]![CSR dg]![txtEarliestReceivedDate]),[DR].[Received Date],[Forms]![CSR dg]![txtEarliestReceivedDate]))

The true side of the IIF allows for all received dates if no data is entered
into the form. I would like to instead not do a criteria test at all if there
is no date entered into the form. I've tried things like "" or NULL in the
criteria, but to no avail.

The reason for wanting to do this, is that I have two tables linked (table1
and table2). Table1 being a master, table2 a detail. These are joined with a
left join which allows me to have all data in the master even if there is no
detail. Except that if I do a criteria check on table2 (as per above), it
short circuits the left join. If no criteria check is done when no receive
date is entered on the form, then the left join should work properly.

I have a report based on this query, and so might be able to use the Filter
field to filter based on the receive date. But I've tried to figure out this
kind of problem in the past, so would like to see if it can be accomplished
using the query only.

thanks,
James Deckert

 

[Allen Browne]

One way around this would be to stack this query on another one.

Create a query using the table on the outer side of the join (DR, is it?).
Swtich this query to SQL View, and set up the WHERE clause like this:
WHERE (([Forms]![CSR dg]![txtEarliestReceivedDate] Is Null)
OR (DR.[Date Received] = [Forms]![CSR
dg]![txtEarliestReceivedDate]))

Save the query. Now use this query instead of the DR table in the outer side
of the join in your main query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a criteria test in the [DR].[Received Date] field of a query. The
criteria is based on data which is entered into a form.

=(IIf(IsNull([Forms]![CSR dg]![txtEarliestReceivedDate]),[DR].[Received
Date],[Forms]![CSR dg]![txtEarliestReceivedDate]))

The true side of the IIF allows for all received dates if no data is
entered
into the form. I would like to instead not do a criteria test at all if
there
is no date entered into the form. I've tried things like "" or NULL in the
criteria, but to no avail.

The reason for wanting to do this, is that I have two tables linked
(table1
and table2). Table1 being a master, table2 a detail. These are joined with
a
left join which allows me to have all data in the master even if there is
no
detail. Except that if I do a criteria check on table2 (as per above), it
short circuits the left join. If no criteria check is done when no receive
date is entered on the form, then the left join should work properly.

I have a report based on this query, and so might be able to use the
Filter
field to filter based on the receive date. But I've tried to figure out
this
kind of problem in the past, so would like to see if it can be
accomplished
using the query only.

thanks,
James Deckert

 

[JamesDeckert]

Thanks Allen for the response,
I tried your idea but it returns all records on the left outer join (master)
table side when a criteria is placed in the earliest received date field
(detail) on the form instead of restricting to only those which meet this
criteria.

I took your germ of the idea and tested the form field for null within my
original query on the OR criteria row and I think it works the way I want it
to.

There you go broadening my vision for what can be accomplished in Access.

thanks so much,
James

One way around this would be to stack this query on another one.

Create a query using the table on the outer side of the join (DR, is it?).
Swtich this query to SQL View, and set up the WHERE clause like this:
WHERE (([Forms]![CSR dg]![txtEarliestReceivedDate] Is Null)
OR (DR.[Date Received] = [Forms]![CSR
dg]![txtEarliestReceivedDate]))

Save the query. Now use this query instead of the DR table in the outer side
of the join in your main query.

--
Allen Browne - Microsoft MVP. Perth, Western Australia

Reply to group, rather than allenbrowne at mvps dot org.

I have a criteria test in the [DR].[Received Date] field of a query. The
criteria is based on data which is entered into a form.

=(IIf(IsNull([Forms]![CSR dg]![txtEarliestReceivedDate]),[DR].[Received
Date],[Forms]![CSR dg]![txtEarliestReceivedDate]))

The true side of the IIF allows for all received dates if no data is
entered
into the form. I would like to instead not do a criteria test at all if
there
is no date entered into the form. I've tried things like "" or NULL in the
criteria, but to no avail.

The reason for wanting to do this, is that I have two tables linked
(table1
and table2). Table1 being a master, table2 a detail. These are joined with
a
left join which allows me to have all data in the master even if there is
no
detail. Except that if I do a criteria check on table2 (as per above), it
short circuits the left join. If no criteria check is done when no receive
date is entered on the form, then the left join should work properly.

I have a report based on this query, and so might be able to use the
Filter
field to filter based on the receive date. But I've tried to figure out
this
kind of problem in the past, so would like to see if it can be
accomplished
using the query only.

thanks,
James Deckert