parameters not flowing properly to report

G

Guest

Hi folks,

In need of some urgent help on this.

I have a form "reportselection" where I can select the report I want to run
and also input some criterias through some fields. For the fields that the
report uses and I want to have parameters against. I have the criterias in
my query underlying my report (report: "Status Report - Daily Bundle
Activity by Department/Advisor", query: "trackcountquery21).

The criteria point to the fields on the form. I want to be able to open
the report from this form with the "filters" that I have chosen.

Please see the SQL view of my underlying query below.

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


For some reason, the data is not populating properly. In some cases I have
more data then I asked for, or if I "filter" a field, the other records are
still showing, or I am getting #error# on my report.

Any reason why this would be? I suspect its somehow has to do with my
criterias. If I only use one, it usually works, but the minute I use a
"filter" more than one field, the whole thing goes down.

Help is appreciated.
 
A

Allen Browne

You can probably solve the problem by declaring your parameters.

In query design view, choose Parameters on the Query menu.
Enter 4 rows into the dialog:
[Forms]![reportselection]![from] Date/Time
[Forms]![reportselection]![dateto] Date/Time
[Forms]![reportselection]![dept] Long
[Forms]![reportselection]![adv] ???
(I can't tell the data type of your AssignedTo field.)

If the from and dateto text boxes are unbound, it would also be a good idea
to set their Format property to Short Date or similar, so Access understands
the data type.

You may find it better to use a WHERE clause rather than a HAVING clause.
Something like this:

PARAMETERS [Forms]![reportselection]![from] DateTime,
[Forms]![reportselection]![dateto] DateTime,
[Forms]![reportselection]![dept] Long,
[Forms]![reportselection]![adv] Long;
SELECT TrackCount.DateAdded,
TrackCount.fkDeptID,
TrackCount.AssignedTo,
Count(TrackCount.[Bundle#]) AS Bundles,
Sum(TrackCount.[CountOfTrack#]) AS Documents,
Sum(TrackCount.[Problem Logged]) AS [Problem Logged],
Sum(TrackCount.Pending) AS Pending,
Sum(TrackCount.Parked) AS Parked,
Sum(TrackCount.Unparked) AS Unparked,
Sum(TrackCount.Paid) AS Paid,
Sum(TrackCount.Unpaid) AS Unpaid,
Sum(TrackCount.[FI Review]) AS [FI Review],
Sum(TrackCount.Revision) AS [Under Review],
Sum(TrackCount.Complete) AS Complete,
Sum(TrackCount.Incomplete) AS Incomplete
FROM TrackCount
WHERE ((TrackCount.DateAdded Between [Forms]![reportselection]![from]
And [Forms]![reportselection]![dateto])
AND (TrackCount.fkDeptID = [Forms]![reportselection]![dept])
AND (TrackCount.AssignedTo = [Forms]![reportselection]![adv]))
GROUP BY TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.AssignedTo;

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

Reply to group, rather than allenbrowne at mvps dot org.
Jean-Francois Gauthier said:
Hi folks,

In need of some urgent help on this.

I have a form "reportselection" where I can select the report I want to
run
and also input some criterias through some fields. For the fields that
the
report uses and I want to have parameters against. I have the criterias
in
my query underlying my report (report: "Status Report - Daily Bundle
Activity by Department/Advisor", query: "trackcountquery21).

The criteria point to the fields on the form. I want to be able to open
the report from this form with the "filters" that I have chosen.

Please see the SQL view of my underlying query below.

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


For some reason, the data is not populating properly. In some cases I
have
more data then I asked for, or if I "filter" a field, the other records
are
still showing, or I am getting #error# on my report.

Any reason why this would be? I suspect its somehow has to do with my
criterias. If I only use one, it usually works, but the minute I use a
"filter" more than one field, the whole thing goes down.

Help is appreciated.
Click to expand...
 
G

Guest

Hi Allen,

Thanks for the reply. I tried what you said below and also added a little
more. My SQL now looks like this:

PARAMETERS [Forms]![reportselection]![from] DateTime,
[Forms]![reportselection]![dateto] DateTime, [Forms]![reportselection]![dept]
Long, [Forms]![reportselection]![adv] Long;
SELECT TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.AssignedTo,
Count(TrackCount.[Bundle#]) AS Bundles, Sum(TrackCount.[CountOfTrack#]) AS
Documents, Sum(TrackCount.[Problem Logged]) AS [Problem Logged],
Sum(TrackCount.Pending) AS Pending, Sum(TrackCount.Parked) AS Parked,
Sum(TrackCount.Unparked) AS Unparked, Sum(TrackCount.Paid) AS Paid,
Sum(TrackCount.Unpaid) AS Unpaid, Sum(TrackCount.[FI Review]) AS [FI Review],
Sum(TrackCount.Revision) AS [Under Review], Sum(TrackCount.Complete) AS
Complete, Sum(TrackCount.Incomplete) AS Incomplete
FROM TrackCount
WHERE (((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])) 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]))
GROUP BY TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.AssignedTo;


However, my data is still not appearing properly.

What I want to be able to do is that for this particular report I can choose
the from date and the to date and have the option to also choose a department
(dept) and/or the assigned to (adv). In some cases I want to pull the
report with all 3 fields having some sort of value to filter from. However
in other cases I may want to run the report with only certain dates. I'm
thinking this is where my problem lies.

Allen Browne said:
You can probably solve the problem by declaring your parameters.

In query design view, choose Parameters on the Query menu.
Enter 4 rows into the dialog:
[Forms]![reportselection]![from] Date/Time
[Forms]![reportselection]![dateto] Date/Time
[Forms]![reportselection]![dept] Long
[Forms]![reportselection]![adv] ???
(I can't tell the data type of your AssignedTo field.)

If the from and dateto text boxes are unbound, it would also be a good idea
to set their Format property to Short Date or similar, so Access understands
the data type.

You may find it better to use a WHERE clause rather than a HAVING clause.
Something like this:

PARAMETERS [Forms]![reportselection]![from] DateTime,
[Forms]![reportselection]![dateto] DateTime,
[Forms]![reportselection]![dept] Long,
[Forms]![reportselection]![adv] Long;
SELECT TrackCount.DateAdded,
TrackCount.fkDeptID,
TrackCount.AssignedTo,
Count(TrackCount.[Bundle#]) AS Bundles,
Sum(TrackCount.[CountOfTrack#]) AS Documents,
Sum(TrackCount.[Problem Logged]) AS [Problem Logged],
Sum(TrackCount.Pending) AS Pending,
Sum(TrackCount.Parked) AS Parked,
Sum(TrackCount.Unparked) AS Unparked,
Sum(TrackCount.Paid) AS Paid,
Sum(TrackCount.Unpaid) AS Unpaid,
Sum(TrackCount.[FI Review]) AS [FI Review],
Sum(TrackCount.Revision) AS [Under Review],
Sum(TrackCount.Complete) AS Complete,
Sum(TrackCount.Incomplete) AS Incomplete
FROM TrackCount
WHERE ((TrackCount.DateAdded Between [Forms]![reportselection]![from]
And [Forms]![reportselection]![dateto])
AND (TrackCount.fkDeptID = [Forms]![reportselection]![dept])
AND (TrackCount.AssignedTo = [Forms]![reportselection]![adv]))
GROUP BY TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.AssignedTo;

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

Reply to group, rather than allenbrowne at mvps dot org.
Jean-Francois Gauthier said:
Hi folks,

In need of some urgent help on this.

I have a form "reportselection" where I can select the report I want to
run
and also input some criterias through some fields. For the fields that
the
report uses and I want to have parameters against. I have the criterias
in
my query underlying my report (report: "Status Report - Daily Bundle
Activity by Department/Advisor", query: "trackcountquery21).

The criteria point to the fields on the form. I want to be able to open
the report from this form with the "filters" that I have chosen.

Please see the SQL view of my underlying query below.

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


For some reason, the data is not populating properly. In some cases I
have
more data then I asked for, or if I "filter" a field, the other records
are
still showing, or I am getting #error# on my report.

Any reason why this would be? I suspect its somehow has to do with my
criterias. If I only use one, it usually works, but the minute I use a
"filter" more than one field, the whole thing goes down.

Help is appreciated.
Click to expand...
Click to expand...
 
A

Allen Browne

In that case, you would be better off leaving the criteria out of the query
altogether.

Instead create the WhereCondition string for OpenReport from only those
controls where the user entered something. Here's an example using just the
dates:
http://allenbrowne.com/casu-08.html

Here's a more comprehensive example, using all types of field:
http://allenbrowne.com/ser-62.html
That article explains how to apply the filter to the form, but applying the
WhereCondition for OpenReport is identical. (It also explains how to do it
with parameters if you really want to.)

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

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

Jean-Francois Gauthier said:
Hi Allen,

Thanks for the reply. I tried what you said below and also added a little
more. My SQL now looks like this:

PARAMETERS [Forms]![reportselection]![from] DateTime,
[Forms]![reportselection]![dateto] DateTime,
[Forms]![reportselection]![dept]
Long, [Forms]![reportselection]![adv] Long;
SELECT TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.AssignedTo,
Count(TrackCount.[Bundle#]) AS Bundles, Sum(TrackCount.[CountOfTrack#]) AS
Documents, Sum(TrackCount.[Problem Logged]) AS [Problem Logged],
Sum(TrackCount.Pending) AS Pending, Sum(TrackCount.Parked) AS Parked,
Sum(TrackCount.Unparked) AS Unparked, Sum(TrackCount.Paid) AS Paid,
Sum(TrackCount.Unpaid) AS Unpaid, Sum(TrackCount.[FI Review]) AS [FI
Review],
Sum(TrackCount.Revision) AS [Under Review], Sum(TrackCount.Complete) AS
Complete, Sum(TrackCount.Incomplete) AS Incomplete
FROM TrackCount
WHERE (((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])) 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]))
GROUP BY TrackCount.DateAdded, TrackCount.fkDeptID, TrackCount.AssignedTo;


However, my data is still not appearing properly.

What I want to be able to do is that for this particular report I can
choose
the from date and the to date and have the option to also choose a
department
(dept) and/or the assigned to (adv). In some cases I want to pull the
report with all 3 fields having some sort of value to filter from.
However
in other cases I may want to run the report with only certain dates. I'm
thinking this is where my problem lies.

Allen Browne said:
You can probably solve the problem by declaring your parameters.

In query design view, choose Parameters on the Query menu.
Enter 4 rows into the dialog:
[Forms]![reportselection]![from] Date/Time
[Forms]![reportselection]![dateto] Date/Time
[Forms]![reportselection]![dept] Long
[Forms]![reportselection]![adv] ???
(I can't tell the data type of your AssignedTo field.)

If the from and dateto text boxes are unbound, it would also be a good
idea
to set their Format property to Short Date or similar, so Access
understands
the data type.

You may find it better to use a WHERE clause rather than a HAVING clause.
Something like this:

PARAMETERS [Forms]![reportselection]![from] DateTime,
[Forms]![reportselection]![dateto] DateTime,
[Forms]![reportselection]![dept] Long,
[Forms]![reportselection]![adv] Long;
SELECT TrackCount.DateAdded,
TrackCount.fkDeptID,
TrackCount.AssignedTo,
Count(TrackCount.[Bundle#]) AS Bundles,
Sum(TrackCount.[CountOfTrack#]) AS Documents,
Sum(TrackCount.[Problem Logged]) AS [Problem Logged],
Sum(TrackCount.Pending) AS Pending,
Sum(TrackCount.Parked) AS Parked,
Sum(TrackCount.Unparked) AS Unparked,
Sum(TrackCount.Paid) AS Paid,
Sum(TrackCount.Unpaid) AS Unpaid,
Sum(TrackCount.[FI Review]) AS [FI Review],
Sum(TrackCount.Revision) AS [Under Review],
Sum(TrackCount.Complete) AS Complete,
Sum(TrackCount.Incomplete) AS Incomplete
FROM TrackCount
WHERE ((TrackCount.DateAdded Between [Forms]![reportselection]![from]
And [Forms]![reportselection]![dateto])
AND (TrackCount.fkDeptID = [Forms]![reportselection]![dept])
AND (TrackCount.AssignedTo = [Forms]![reportselection]![adv]))
GROUP BY TrackCount.DateAdded, TrackCount.fkDeptID,
TrackCount.AssignedTo;

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

Reply to group, rather than allenbrowne at mvps dot org.
"Jean-Francois Gauthier" <[email protected]>
wrote in message
Hi folks,

In need of some urgent help on this.

I have a form "reportselection" where I can select the report I want to
run
and also input some criterias through some fields. For the fields
that
the
report uses and I want to have parameters against. I have the
criterias
in
my query underlying my report (report: "Status Report - Daily Bundle
Activity by Department/Advisor", query: "trackcountquery21).

The criteria point to the fields on the form. I want to be able to
open
the report from this form with the "filters" that I have chosen.

Please see the SQL view of my underlying query below.

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


For some reason, the data is not populating properly. In some cases I
have
more data then I asked for, or if I "filter" a field, the other records
are
still showing, or I am getting #error# on my report.

Any reason why this would be? I suspect its somehow has to do with my
criterias. If I only use one, it usually works, but the minute I use a
"filter" more than one field, the whole thing goes down.

Help is appreciated.
Click to expand...
Click to expand...
Click to expand...
 
Top