Changing the Recourd Source (underlying query) of a report

G

Guest

Hi there,

I'm trying to build a QBF form to generate numerous types of data.

On my form "reportselection", I have a bunch of fields where selections
"criterias" can be inputted by the user. The first one is a drop-down box
listing the names of the reports available for viewing by the user. The
user selects the report he/she wants to see.

Based on the report chosen, certain fields will be made available (Visible)
to the user. From the fields, the user will be able to make his query based
on the criteria fields on the form. Press a button which previews the
report.

I am building the different types of reports and have a bunch of underlying
queries. In cases where more than 2 fields are chosen however, the
underlying query is not providing the right information. I suspect this has
to do with null fields on the form that are left but are being called in the
criteria part of the query.

So the way I think to resolve this is by copying the same query and add the
criteria chosen as the end of the name of it. So I will have many of the
same queries, only having pre-selected the criteria. One query for example
would call dates on the form. Another query would call the dept. Another
query would call dates and dept on the form. This way, any way the user
selects his information he will have the right information. The only
problem I am getting at being able to say in a series of VBA coding on the
"enter" button which selects the report to preview, but making its record
source the query name that corresponds to a series of ifs statements.

Such as if:

me.report = "invoice report" and me.date is not null
then
reporttoopen = invoice report
this is where I get lost......Is there a way to say
report.underlying query or report.recordsource as invoice
report.invoicereportdate where invoicereportdate is the underlying query of
report!invoicereport.


Sort of confusing so if you are not understanding let me know. Its a bit
weird for me to explain.
 
T

tina

before you go the "multiple identical queries" route, suggest you post the
SQL statement of a query that has form-based criteria on more than two
fields. perhaps we can help you write a query that will work regardless of
the combination of criteria that are entered by the user.

i'd think it should be possible; i use the same concept on a form to filter
data in an unlinked subform, and currently have some subform RecordSource
SQL statements that dynamically use criteria on up to six fields, based on
the user's selections.

hth
 
G

Guest

Ok, here would be the query SQL with all 5 fields available (I might choose
to add more later on, but these would be nice to have). There is a field
for date, if possible to make it so that if the user enters something in the
"from" box on the form, but nothing in the "dateto" box on the form, then to
provide all the relevant data as well as provide if neither fields had a date
chosen. (Dates are provided in medium format via calendar as per
calendarfor statement of "on enter" clause in box.


So I need to make it so that if there all 5 fields, 4 of the 5 fields, 3 of
the 5 fields etc etc are not null, that it provides the information from
those boxes as criteria to the query. If none of the boxes are provided
info in them, then provide all the system information.

Here is the SQL:

SELECT TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.BizUnitName,
TrackCount.AssignedTo, TrackCount.DocumentType, Count(TrackCount.[Bundle#])
AS Bundles, Sum(TrackCount.[CountOfTrack#]) AS Documents,
Sum(TrackCount.Complete) AS Complete, Sum(TrackCount.Incomplete) AS
Incomplete, Sum(TrackCount.[Problem Logged]) AS [Problem Logged],
Sum(TrackCount.Parked) AS Parked, Sum(TrackCount.Paid) AS Paid,
Sum(TrackCount.[FI Review]) AS [FI Review], Sum(TrackCount.Pending) AS
Pending, Sum(TrackCount.Unparked) AS Unparked, Sum(TrackCount.Unpaid) AS
Unpaid, Sum(TrackCount.Revision) AS [Under Review]
FROM TrackCount
GROUP BY TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.BizUnitName,
TrackCount.AssignedTo, TrackCount.DocumentType
HAVING (((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]))
ORDER BY TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.BizUnitName,
TrackCount.AssignedTo, TrackCount.DocumentType;



If it is also possible to change the order of the 5 main fields on the query
(list dept first rather than the date field for example) that the user can do
this via the form. I'm not sure how to go about this other than again,
multiples of our new queries with the fields in different order and having
the user again chose from the form which fields he/she wants first.

So on the form I could have something like 5 boxes marked as field1, field2,
field3 and so on....where the user could choose the fields to be listed
first. These fields would be realligned in the query and the report
requested.

Anyhow, lets deal with this multiple query deal first and if we can at least
get this resolved, I will be more than happy!
 
G

Guest

Ok, I have two new SQL statements, but neither are working. I'm thinking
there are too many arguments (should be 36 in total) as I am working on this
particular one with 5 different field options.

Here is the first SQL: (this is returning all data even after I have
inputted certain criterias on the form.

SELECT TrackCount.DateAdded AS [Date Added], TrackCount.fkDeptID AS Dept,
TrackCount.BizUnitName AS [Business Unit Name], TrackCount.AssignedTo AS
[Assigned To], TrackCount.DocumentType AS [Document Type],
Count(TrackCount.[Bundle#]) AS Bundles, Sum(TrackCount.[CountOfTrack#]) AS
Documents, Sum(TrackCount.Complete) AS Complete, Sum(TrackCount.Incomplete)
AS Incomplete, Sum(TrackCount.[Problem Logged]) AS [Problem Logged],
Sum(TrackCount.Parked) AS Parked, Sum(TrackCount.Paid) AS Paid,
Sum(TrackCount.[FI Review]) AS [FI Review], Sum(TrackCount.Pending) AS
Pending, Sum(TrackCount.Unparked) AS Unparked, Sum(TrackCount.Unpaid) AS
Unpaid, Sum(TrackCount.Revision) AS [Under Review]
FROM TrackCount
GROUP BY TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.BizUnitName,
TrackCount.AssignedTo, TrackCount.DocumentType
HAVING (((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept])) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit])) OR
(((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.DocumentType)=[forms]![reportselection]![doctype]))
ORDER BY TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.BizUnitName,
TrackCount.AssignedTo, TrackCount.DocumentType;


Now I also have a second query that I built trying to put the Isnull
function after the field for those fields that should be empty so that it
doesn't take these in terms of criteria. (This query returns nothing to me).

SELECT TrackCount.DateAdded AS [Date Added], TrackCount.fkDeptID AS Dept,
TrackCount.BizUnitName AS [Business Unit Name], TrackCount.AssignedTo AS
[Assigned To], TrackCount.DocumentType AS [Document Type],
Count(TrackCount.[Bundle#]) AS Bundles, Sum(TrackCount.[CountOfTrack#]) AS
Documents, Sum(TrackCount.Complete) AS Complete, Sum(TrackCount.Incomplete)
AS Incomplete, Sum(TrackCount.[Problem Logged]) AS [Problem Logged],
Sum(TrackCount.Parked) AS Parked, Sum(TrackCount.Paid) AS Paid,
Sum(TrackCount.[FI Review]) AS [FI Review], Sum(TrackCount.Pending) AS
Pending, Sum(TrackCount.Unparked) AS Unparked, Sum(TrackCount.Unpaid) AS
Unpaid, Sum(TrackCount.Revision) AS [Under Review]
FROM TrackCount
GROUP BY TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.BizUnitName,
TrackCount.AssignedTo, TrackCount.DocumentType
HAVING (((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![bizunit]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![dept]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
((([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null))
ORDER BY TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.BizUnitName,
TrackCount.AssignedTo, TrackCount.DocumentType;


Is there anything I am missing?

I'm thinking the multiple identical queries idea will be the only thing to
work.

Your thoughts are much appreciated.
 
G

Guest

Hi Tina,

Ok, I got my query to work with the 5 fields I wanted. So problem is solved.

Basically you have to think of it as a Yes or No decision for each field.
So for 5 fields, you would have 2 exponential 5, or 36 lines of criterias to
enter. For the Yes's you put the field from the form that it comes from and
when no you enter that same field, but adding "Is Null" after the field.
This takes care of those fields that are left blank by the user.

So like I said, I have solved my problem and my report(s) are running
perfectly as I wanted them to.

I have another report that I would like to build that would have about 8 to
11 fields where I would like the user to be able to Query from the form on.
If I take these 11 fields as criteria, so 2 exponential 11 = 2046 criterias
or lines of criterias in a query. Is this something that is doable in
access? Can it handle that much? I would really like to know before I try
to work this report out. Any help knowledge on this issue is greatly
appreciated!!!! Is there any "quick" ways of doing this as well for that
many lines of criterias?

Jean-Francois Gauthier said:
Ok, I have two new SQL statements, but neither are working. I'm thinking
there are too many arguments (should be 36 in total) as I am working on this
particular one with 5 different field options.

Here is the first SQL: (this is returning all data even after I have
inputted certain criterias on the form.

SELECT TrackCount.DateAdded AS [Date Added], TrackCount.fkDeptID AS Dept,
TrackCount.BizUnitName AS [Business Unit Name], TrackCount.AssignedTo AS
[Assigned To], TrackCount.DocumentType AS [Document Type],
Count(TrackCount.[Bundle#]) AS Bundles, Sum(TrackCount.[CountOfTrack#]) AS
Documents, Sum(TrackCount.Complete) AS Complete, Sum(TrackCount.Incomplete)
AS Incomplete, Sum(TrackCount.[Problem Logged]) AS [Problem Logged],
Sum(TrackCount.Parked) AS Parked, Sum(TrackCount.Paid) AS Paid,
Sum(TrackCount.[FI Review]) AS [FI Review], Sum(TrackCount.Pending) AS
Pending, Sum(TrackCount.Unparked) AS Unparked, Sum(TrackCount.Unpaid) AS
Unpaid, Sum(TrackCount.Revision) AS [Under Review]
FROM TrackCount
GROUP BY TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.BizUnitName,
TrackCount.AssignedTo, TrackCount.DocumentType
HAVING (((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept])) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit])) OR
(((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.DocumentType)=[forms]![reportselection]![doctype]))
ORDER BY TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.BizUnitName,
TrackCount.AssignedTo, TrackCount.DocumentType;


Now I also have a second query that I built trying to put the Isnull
function after the field for those fields that should be empty so that it
doesn't take these in terms of criteria. (This query returns nothing to me).

SELECT TrackCount.DateAdded AS [Date Added], TrackCount.fkDeptID AS Dept,
TrackCount.BizUnitName AS [Business Unit Name], TrackCount.AssignedTo AS
[Assigned To], TrackCount.DocumentType AS [Document Type],
Count(TrackCount.[Bundle#]) AS Bundles, Sum(TrackCount.[CountOfTrack#]) AS
Documents, Sum(TrackCount.Complete) AS Complete, Sum(TrackCount.Incomplete)
AS Incomplete, Sum(TrackCount.[Problem Logged]) AS [Problem Logged],
Sum(TrackCount.Parked) AS Parked, Sum(TrackCount.Paid) AS Paid,
Sum(TrackCount.[FI Review]) AS [FI Review], Sum(TrackCount.Pending) AS
Pending, Sum(TrackCount.Unparked) AS Unparked, Sum(TrackCount.Unpaid) AS
Unpaid, Sum(TrackCount.Revision) AS [Under Review]
FROM TrackCount
GROUP BY TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.BizUnitName,
TrackCount.AssignedTo, TrackCount.DocumentType
HAVING (((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![bizunit]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![dept]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
Click to expand...
 
T

tina

take a look at the Access specifications for queries in Help; that should
answer your "do-able?" question. as for the "easy way" of writing the SQL
statement, i use the query Design view. write the query as you need it; then
for the criteria i'll use an example: let's say you want criteria on
FieldA, using the value found in ControlA on FormA. enter the following in
*one* line of the criteria grid, as

Forms!FormA!ControlA Or Forms!FormA!ControlA Is Null

do the same for all fields you want to set criteria on. when you save and
close the query, Access will write all those lines of SQL code - so you
don't have to do it manually.

hth


Jean-Francois Gauthier said:
Hi Tina,

Ok, I got my query to work with the 5 fields I wanted. So problem is solved.

Basically you have to think of it as a Yes or No decision for each field.
So for 5 fields, you would have 2 exponential 5, or 36 lines of criterias to
enter. For the Yes's you put the field from the form that it comes from and
when no you enter that same field, but adding "Is Null" after the field.
This takes care of those fields that are left blank by the user.

So like I said, I have solved my problem and my report(s) are running
perfectly as I wanted them to.

I have another report that I would like to build that would have about 8 to
11 fields where I would like the user to be able to Query from the form on.
If I take these 11 fields as criteria, so 2 exponential 11 = 2046 criterias
or lines of criterias in a query. Is this something that is doable in
access? Can it handle that much? I would really like to know before I try
to work this report out. Any help knowledge on this issue is greatly
appreciated!!!! Is there any "quick" ways of doing this as well for that
many lines of criterias?

Jean-Francois Gauthier said:
Ok, I have two new SQL statements, but neither are working. I'm thinking
there are too many arguments (should be 36 in total) as I am working on this
particular one with 5 different field options.

Here is the first SQL: (this is returning all data even after I have
inputted certain criterias on the form.

SELECT TrackCount.DateAdded AS [Date Added], TrackCount.fkDeptID AS Dept,
TrackCount.BizUnitName AS [Business Unit Name], TrackCount.AssignedTo AS
[Assigned To], TrackCount.DocumentType AS [Document Type],
Count(TrackCount.[Bundle#]) AS Bundles, Sum(TrackCount.[CountOfTrack#]) AS
Documents, Sum(TrackCount.Complete) AS Complete, Sum(TrackCount.Incomplete)
AS Incomplete, Sum(TrackCount.[Problem Logged]) AS [Problem Logged],
Sum(TrackCount.Parked) AS Parked, Sum(TrackCount.Paid) AS Paid,
Sum(TrackCount.[FI Review]) AS [FI Review], Sum(TrackCount.Pending) AS
Pending, Sum(TrackCount.Unparked) AS Unparked, Sum(TrackCount.Unpaid) AS
Unpaid, Sum(TrackCount.Revision) AS [Under Review]
FROM TrackCount
GROUP BY TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.BizUnitName,
TrackCount.AssignedTo, TrackCount.DocumentType
HAVING (((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept])) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit])) OR
(((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.AssignedTo)=[forms]![reportselection]![adv])) OR
(((TrackCount.DocumentType)=[forms]![reportselection]![doctype]))
ORDER BY TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.BizUnitName,
TrackCount.AssignedTo, TrackCount.DocumentType;


Now I also have a second query that I built trying to put the Isnull
function after the field for those fields that should be empty so that it
doesn't take these in terms of criteria. (This query returns nothing to me).

SELECT TrackCount.DateAdded AS [Date Added], TrackCount.fkDeptID AS Dept,
TrackCount.BizUnitName AS [Business Unit Name], TrackCount.AssignedTo AS
[Assigned To], TrackCount.DocumentType AS [Document Type],
Count(TrackCount.[Bundle#]) AS Bundles, Sum(TrackCount.[CountOfTrack#]) AS
Documents, Sum(TrackCount.Complete) AS Complete, Sum(TrackCount.Incomplete)
AS Incomplete, Sum(TrackCount.[Problem Logged]) AS [Problem Logged],
Sum(TrackCount.Parked) AS Parked, Sum(TrackCount.Paid) AS Paid,
Sum(TrackCount.[FI Review]) AS [FI Review], Sum(TrackCount.Pending) AS
Pending, Sum(TrackCount.Unparked) AS Unparked, Sum(TrackCount.Unpaid) AS
Unpaid, Sum(TrackCount.Revision) AS [Under Review]
FROM TrackCount
GROUP BY TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.BizUnitName,
TrackCount.AssignedTo, TrackCount.DocumentType
HAVING (((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype])) OR
(((TrackCount.DateAdded) Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![bizunit]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR (((TrackCount.DateAdded)
Between [forms]![reportselection]![from] And
[forms]![reportselection]![dateto]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.fkDeptID)=[forms]![reportselection]![dept]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![bizunit]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![dept]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.AssignedTo)=[forms]![reportselection]![adv]) AND
(([forms]![reportselection]![doctype]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
((TrackCount.DocumentType)=[forms]![reportselection]![doctype]) AND
(([forms]![reportselection]![adv]) Is Null) AND
(([forms]![reportselection]![dept]) Is Null) AND
(([forms]![reportselection]![from]) Is Null) AND
(([forms]![reportselection]![dateto]) Is Null)) OR
(((TrackCount.BizUnitName)=[forms]![reportselection]![bizunit]) AND
Click to expand...
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 Parameter using multiple options 0
Report loses sorting criteria of underlying query 9
Form with combo box to set criteria for query and report 2
underlying query for report 1
Create report from form through vba 1
Main / Sub Form 3
When I close my Access report, the underlying query stays open. 2
how to pass a parameter to a query base of a query that is the base of a report 0

Top