Report by date range

[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?

 

[Marshall Barton]

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.

 

[Dan]

what would that criteria be?

Yes.

 

[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#

 

[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?

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?

 

[KARL DEWEY]

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

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?

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.

 

[Dan]

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

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

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?

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.