Report by date range

D

Dan

I have qry which gives me a summary of item sales by location:

SELECT DISTINCTROW tbl_locationtype.LocationtypeID,
tbl_locationtype.Locationtype, qrysales.Item, Sum(qrysales.Sold) AS
ItemSales, Sum(qrysales.Sales) AS ProductSales
FROM tbl_locationtype INNER JOIN ((tbllocation INNER JOIN (qrysales INNER
JOIN tblitemsales ON qrysales.ItemsalesID = tblitemsales.ItemsalesID) ON
tbllocation.LocationID = qrysales.LocationID) INNER JOIN tblevent ON
qrysales.EventID = tblevent.EventID) ON tbl_locationtype.LocationtypeID =
tbllocation.LocationtypeID
GROUP BY tbl_locationtype.LocationtypeID, tbl_locationtype.Locationtype,
qrysales.Item
ORDER BY tbl_locationtype.Locationtype;

Which does what I want, except for dates. I have a field called "Edate"
which when I add, I get item sales for each date. I dont want this, I just
want a summary of sales between specified dates. what is the best way to do
this. I have a form, for entering a start and an end date, which than calls
the report.

Can I set the total field for Edate to "where" and then set criteria?
 
M

Marshall Barton

Dan said:
I have qry which gives me a summary of item sales by location:

SELECT DISTINCTROW tbl_locationtype.LocationtypeID,
tbl_locationtype.Locationtype, qrysales.Item, Sum(qrysales.Sold) AS
ItemSales, Sum(qrysales.Sales) AS ProductSales
FROM tbl_locationtype INNER JOIN ((tbllocation INNER JOIN (qrysales INNER
JOIN tblitemsales ON qrysales.ItemsalesID = tblitemsales.ItemsalesID) ON
tbllocation.LocationID = qrysales.LocationID) INNER JOIN tblevent ON
qrysales.EventID = tblevent.EventID) ON tbl_locationtype.LocationtypeID =
tbllocation.LocationtypeID
GROUP BY tbl_locationtype.LocationtypeID, tbl_locationtype.Locationtype,
qrysales.Item
ORDER BY tbl_locationtype.Locationtype;

Which does what I want, except for dates. I have a field called "Edate"
which when I add, I get item sales for each date. I dont want this, I just
want a summary of sales between specified dates. what is the best way to do
this. I have a form, for entering a start and an end date, which than calls
the report.

Can I set the total field for Edate to "where" and then set criteria?
Click to expand...


Yes.
 
M

Marshall Barton

Whatever is appropriate to your situation.

For a date range, you xould use something like:

Between #2008-8-1# And #2008-9-30#
 
D

Dan

I think I have the criteria thing worked out, my problem is,how do I get a
report based on a date range I enter into a form?

Marshall Barton said:
Whatever is appropriate to your situation.

For a date range, you xould use something like:

Between #2008-8-1# And #2008-9-30#
--
Marsh
MVP [MS Access]

what would that criteria be?
Click to expand...
Click to expand...
 
K

KARL DEWEY

Between [Forms]![YourFormName]![StartTextBox] And
[Forms]![YourFormName]![EndTextBox]
--
KARL DEWEY
Build a little - Test a little


Dan said:
I think I have the criteria thing worked out, my problem is,how do I get a
report based on a date range I enter into a form?

Marshall Barton said:
Whatever is appropriate to your situation.

For a date range, you xould use something like:

Between #2008-8-1# And #2008-9-30#
--
Marsh
MVP [MS Access]

what would that criteria be?

:

Dan wrote:

I have qry which gives me a summary of item sales by location:

SELECT DISTINCTROW tbl_locationtype.LocationtypeID,
tbl_locationtype.Locationtype, qrysales.Item, Sum(qrysales.Sold) AS
ItemSales, Sum(qrysales.Sales) AS ProductSales
FROM tbl_locationtype INNER JOIN ((tbllocation INNER JOIN (qrysales INNER
JOIN tblitemsales ON qrysales.ItemsalesID = tblitemsales.ItemsalesID) ON
tbllocation.LocationID = qrysales.LocationID) INNER JOIN tblevent ON
qrysales.EventID = tblevent.EventID) ON tbl_locationtype.LocationtypeID =
tbllocation.LocationtypeID
GROUP BY tbl_locationtype.LocationtypeID, tbl_locationtype.Locationtype,
qrysales.Item
ORDER BY tbl_locationtype.Locationtype;

Which does what I want, except for dates. I have a field called "Edate"
which when I add, I get item sales for each date. I dont want this, I just
want a summary of sales between specified dates. what is the best way to do
this. I have a form, for entering a start and an end date, which than calls
the report.

Can I set the total field for Edate to "where" and then set criteria?


Yes.
Click to expand...
Click to expand...
Click to expand...
 
D

Dan

Thanks for turning the light on over my head! Works perfect. I also over
complicated the CMD button on my form.

KARL DEWEY said:
Between [Forms]![YourFormName]![StartTextBox] And
[Forms]![YourFormName]![EndTextBox]
--
KARL DEWEY
Build a little - Test a little


Dan said:
I think I have the criteria thing worked out, my problem is,how do I get a
report based on a date range I enter into a form?

Marshall Barton said:
Whatever is appropriate to your situation.

For a date range, you xould use something like:

Between #2008-8-1# And #2008-9-30#
--
Marsh
MVP [MS Access]


Dan wrote:
what would that criteria be?

:

Dan wrote:

I have qry which gives me a summary of item sales by location:

SELECT DISTINCTROW tbl_locationtype.LocationtypeID,
tbl_locationtype.Locationtype, qrysales.Item, Sum(qrysales.Sold) AS
ItemSales, Sum(qrysales.Sales) AS ProductSales
FROM tbl_locationtype INNER JOIN ((tbllocation INNER JOIN (qrysales INNER
JOIN tblitemsales ON qrysales.ItemsalesID = tblitemsales.ItemsalesID) ON
tbllocation.LocationID = qrysales.LocationID) INNER JOIN tblevent ON
qrysales.EventID = tblevent.EventID) ON tbl_locationtype.LocationtypeID =
tbllocation.LocationtypeID
GROUP BY tbl_locationtype.LocationtypeID, tbl_locationtype.Locationtype,
qrysales.Item
ORDER BY tbl_locationtype.Locationtype;

Which does what I want, except for dates. I have a field called "Edate"
which when I add, I get item sales for each date. I dont want this, I just
want a summary of sales between specified dates. what is the best way to do
this. I have a form, for entering a start and an end date, which than calls
the report.

Can I set the total field for Edate to "where" and then set criteria?


Yes.
Click to expand...
Click to expand...
Click to expand...
 
Top