If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Calculating expressions between two dates
([HTGL-HISTORY]![Date]) Between Date()-14 And Date()-8
I'm using the above statement as a criteria in a Query. It is not working and I'm getting errors. What I want to do is get a Sum for Product Qty sold between two given dates. Most products are sold every day and my database hase qty sold for each date but I want to be able to sum those for each week, month, year, etc. I have tried a host of various expressions but I think my problem is that I don't know how to use the "Between" expression with dates. If it can not be used with dates is there something of the equivalent I could use? |
#2
|
|||
|
|||
Calculating expressions between two dates
Post your complete SQL statement.
-- KARL DEWEY Build a little - Test a little "JCowell" wrote: ([HTGL-HISTORY]![Date]) Between Date()-14 And Date()-8 I'm using the above statement as a criteria in a Query. It is not working and I'm getting errors. What I want to do is get a Sum for Product Qty sold between two given dates. Most products are sold every day and my database hase qty sold for each date but I want to be able to sum those for each week, month, year, etc. I have tried a host of various expressions but I think my problem is that I don't know how to use the "Between" expression with dates. If it can not be used with dates is there something of the equivalent I could use? |
#3
|
|||
|
|||
Calculating expressions between two dates
One approach would be to group a query by week, month, year etc. To get the
sum for each product you need to group by the product also. For the years and months you can use the year function. For the week you can use the DatePart function; this takes various interval arguments, that for week being "ww". When grouping by month or week, however you also need to group by year, or rows for the same months or weeks from different years will be grouped together. So for yearly grouping the query would be something like this: SELECT YEAR([Date]) AS [Sale Year], Product, SUM(Qty) AS [Total Quantity] FROM [HTGL-HISTORY] GROUP BY YEAR([Date]), Product; For month SELECT YEAR([Date]) AS [Sale Year], MONTH([Date]) AS [Sale Month], Product, SUM(Qty) AS [Total Quantity] FROM [HTGL-HISTORY] GROUP BY YEAR([Date]), MONTH([Date]), Product; And for weekly grouping: SELECT YEAR([Date]) AS [Sale Year], DATEPART("ww", [Date]) AS [Sale Week], Product, SUM(Qty) AS [Total Quantity] FROM [HTGL-HISTORY] GROUP BY YEAR([Date]), DATEPART("ww", [Date]), Product; If you want to restrict the results to a particular date range you can also include a WHERE clause with parameters for the start and end of the range, e.g. in the case of the weekly grouping: PARAMETERS [Enter start date:] DATETIME, [Enter end date:] DATETIME; SELECT YEAR([Date]) AS [Sale Year], DATEPART("ww", [Date]) AS [Sale Week], Product, SUM(Qty) AS [Total Quantity] FROM [HTGL-HISTORY] WHERE [Date] BETWEEN [Enter start date:] AND [Enter end date:] GROUP BY YEAR([Date]), DATEPART("ww", [Date]), Product; When using date/time parameters it’s prudent to declare them as otherwise a date entered in short date format could be interpreted as an arithmetical expression and give the wrong result. An alternative approach would be to create a report to return total quantities per product for all three intervals simultaneously. Base the report on the following query: SELECT YEAR([Date]) AS [Sale Year], MONTH([Date] AS [Sale Month], DATEPART("ww", [Date]) AS [Sale Week], Product, Qty FROM [HTGL-HISTORY]; You can again include a WHERE clause with parameters if you wish to limit the result to a specific date range. As you'd be showing all three intervals in the report, however, it would be best to restrict the result to full years or the figures could be misleading, e.g. SELECT YEAR([Date]) AS [Sale Year], MONTH([Date] AS [Sale Month], DATEPART("ww", [Date]) AS [Sale Week], Product, Qty FROM [HTGL-HISTORY] WHERE YEAR([Date]) = [Enter year:] OR [Enter year:] IS NULL; Its not really necessary to declare the parameter in this case. By testing for the parameter being NULL it becomes optional, so if no year is entered all years are returned. Group the report via the sorting and grouping dialogue in design view by Product, Sale Year, Sale Month and Sale Week. Give the Product group a group header and give the last three groups group footers. Set the height of the report's detail section to zero and in each group footer put an unbound text box with a ControlSource of: =Sum([Qty]) Also in each footer, alongside the unbound text box, put a text box (labelled Year, Month and Week Number as appropriate) with ControlSources of: [Sale Year] =Format([Date],"mmmm") [Sale Week] The second of these will show the month as its name rather than number. In the product group header put a bound text box with a ControlSource of: [Product] BTW I'd suggest you avoid using date as a column name; it could be confused with the Date function. If you do use it be sure to include it in square brackets. Ken Sheridan Stafford, England "JCowell" wrote: ([HTGL-HISTORY]![Date]) Between Date()-14 And Date()-8 I'm using the above statement as a criteria in a Query. It is not working and I'm getting errors. What I want to do is get a Sum for Product Qty sold between two given dates. Most products are sold every day and my database hase qty sold for each date but I want to be able to sum those for each week, month, year, etc. I have tried a host of various expressions but I think my problem is that I don't know how to use the "Between" expression with dates. If it can not be used with dates is there something of the equivalent I could use? |
Thread Tools | |
Display Modes | |
|
|