Re: query criteria

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

"Allen Browne" wrote:

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
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"JamesDeckert" <JamesDeckert@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:61302FCD-4207-49F3-A1D1-03148CCA4CB3@xxxxxxxxxxxxxxxx
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


.

Relevant Pages

  • RE: Filtering a subform using many combo boxes
    ... SELECT AssignedPriority, AssignedPriorityDetail FROM tblAssignedPriority ... YES for all of the combo boxes.) ... I followed you instructions to the letter and every one of the criteria ... open the query "qryProjects" in design view. ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)
  • Re: using a form with combo box to input criteria
    ... Candia Computer Consulting - Candia NH ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)
  • RE: Filtering a subform using many combo boxes
    ... I followed you instructions to the letter and every one of the criteria ... I described the typical properties of all my combo boxes in my original ... open the query "qryProjects" in design view. ... I have a main table named tblProjectData that contains all the data for each ...
    (microsoft.public.access.forms)
  • Re: Query to search entire record, not one field?
    ... > in criteria for all fields in my Query. ... > There is not information for the "State" field in my master table for this ... >> Rick B ...
    (microsoft.public.access.queries)