Re: query criteria

Tech-Archive recommends: Fix windows errors by optimizing your registry

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: using a form with combo box to input criteria
    ... When you removed the criteria, ... Then your criteria in the query would be... ... query behind your main report. ...
    (microsoft.public.access.forms)