Returning Sum Only in Report (1 Viewer)

[JahJr]

I am trying to transfer all of my excel bookkeeping data to a access db. I have the table built will all of the data in it. My columns are as follows:
ID, Date, Food Sales, Liquor Sales, Beer Sales, Daiquiri Sales, T-Shirt Sales
I have a form where you can select two dates and it will run a query and show all the sales numbers for the days in between those 2 dates. I have a report that is showing the data from the Query. My problem is that the report is showing each individual days sales. I would like the report to ONLY show what the total Food Sales are for this range of dates.

 

[ypma]

I am assumming your sales numbers are currency ? I am no Pro but I would let the query do the summing and the report would reflect the query. Its possible do summing in the report and i have produced a demo showing both ways . Hope this is of help.

The attachement is in AC2010

 

[JahJr]

Thank you for your reply. I just figured out the summing in the query and have the following.

SELECT [Forms]![BetweenDates].[StartDate] AS ["Start Date"], [Forms]![BetweenDates].[endDate] AS ["End Date"], Sum([Food Sales]) AS ["Total Food"], Sum([Liquor Sales]) AS ["Total Liquor"], Sum([Beer Sales]) AS ["Total Beer"], Sum([Daiquiri Sales]) AS ["Total Daiquiri"], Sum([Wine Sales]) AS ["Total Wine"], Sum([T-Shirts Sales]) AS ["Total T-Shirts"], Sum(Nz([Food Sales],0)+Nz([Liquor Sales],0)+Nz([Beer Sales],0)+Nz([Daiquiri Sales],0)+Nz([Wine Sales],0)+Nz([T-Shirts Sales],0)) AS ["Total Product"]
FROM [Funkys Sales]
WHERE ((([Funkys Sales].[Business Date]) Between [Forms]![BetweenDates].[startDate] And [Forms]![BetweenDates].[endDate]));

It is basically working but I need a little fine tuning.
1. The "Total Product" Field in the Query does not show up as Currency
Format
2. The Start Date and End Date are not being transferred from the Form to the Query

 

[ypma]

I take it you are not using AC2010 ?
You should not have to Format the Product field of the query ? eg. FormatCurrency(Tot): TotalProduct

my sql view
SELECT Sum([txtFoodSales]+Nz([txtLiaqureSales])+Nz([txtDaiaquiriSales])+Nz([txtT-ShirtsSales])) AS totProductSales
FROM tblTotalSales
WHERE (((tblTotalSales.DateofSale)>#1/1/2012#));

 

[JahJr]

I am using access 2010. This is very strange I put you SQL code in your sheet and the total product was formatted. I cleaned up my SQL and I still have the same problem.
My SQL View
SELECT [Forms]![BetweenDates].[StartDate] AS ["Start Date"], [Forms]![BetweenDates].[endDate] AS [End Date], Sum([Food Sales]) AS [Total Food], Sum([Liquor Sales]) AS [Total Liquor], Sum([Beer Sales]) AS [Total Beer], Sum([Daiquiri Sales]) AS [Total Daiquiri], Sum([Wine Sales]) AS [Total Wine], Sum([T-Shirt Sales]) AS [Total T-Shirt], Sum(Nz([Food Sales])+Nz([Liquor Sales])+Nz([Beer Sales])+Nz([Daiquiri Sales])+Nz([Wine Sales])+Nz([T-Shirt Sales])) AS [Total Product], Sum([Tax]) AS [Total Tax], Sum([Comp]) AS [Total Comp], Sum([Disc]) AS [Total Disc], Sum([Visa]) AS [Total Visa], Sum([American Express]) AS [Total American Express], Sum([Discover]) AS [Total Discover], Sum([Master Card]) AS [Total Master Card], Sum([Cash Paid Out]) AS [Total Cash Paid Out], Sum([Cash Paid In]) AS [Total Cash Paid In], Sum([Total Cash]) AS [Total Total Cash], Sum([Donation]) AS [Total Donation], Sum([Gift Certificates]) AS [Total Gift Certificates], Sum([Open Drawer]) AS [Total Open Drawer], Sum([Close Drawer]) AS [Total Close Drawer], Sum([Cash Tips]) AS [Total Cash Tips], Sum([Credit Card Tips Taken In]) AS [Total Credit Card Tips Taken In], Sum([Net Sales Entered]) AS [Total Net Sales Entered], Sum([Miscellaneous Hold Back]) AS [Total Miscellaneous Hold Back], Sum([Bartender 1 Tips]) AS [Total Bartender 1 Tips], Sum([Bartender 2 Tips]) AS [Total Bartender 2 Tips], Sum([Bartender 3 Tips]) AS [Total Bartender 3 Tips], Sum([Bartender 4 Tips]) AS [Total Bartender 4 Tips], Sum([Bartender 5 Tips]) AS [Total Bartender 5 Tips], Sum([Cook Tips]) AS [Total Cook Tips]
FROM [Funkys Sales]
WHERE ((([Funkys Sales].[Business Date]) Between [Forms]![BetweenDates].[startDate] And [Forms]![BetweenDates].[endDate]));

As I mentioned earlier The dates are not being pulled from the form to the query. The very first line is what I thought would accompolish this.

 

[JahJr]

It is very frustrating that the fix was this easy. In design view I right clicked all the way at the bottom of the Total Product field. I then selected properties and in format I selected currency.

Still need help getting the dates from the Form into the query??

 

[ypma]

Congratulations on solving that one. As regards to your between dates where clause .In my sql example i used all after date which works fine and as you can see it only appears once not at the beginning, could this be the problem ? If you have other queries using the between dates as a filter have a look at their SQL .
Best of Luck to you Andy