Sorting a profit margin by date

[BenW]

I have a query set up to add the total amount of sales made and the total amount of net paid for the product. I subtract both fields to create a profit field, but how do I sort it so I can choose the dates I want to see the profit?

i.e. weekly profit, monthly profit, yearly profit? Thus far, the query only subtracts the total sum of both sales and net so it gives me the total profit made since the beginning, but I want to create some sort of form or combo box where I type two dates and the profit made between those two dates is shown.

 

[MSAccessRookie]

A good start would be adding something like this to the query

WHERE [DateField] BETWEEN [Enter Start Date] and [Enter End Date]

You can also use get much fancier by using functions to determine the Start and End Dates of a week, a month, a quarter, or a year and modify the input accordingly.

Note that this even works for a single day, but it is clumsy since you need to enter the same date twice.

 

[BenW]

A good start would be adding something like this to the query

WHERE [DateField] BETWEEN [Enter Start Date] and [Enter End Date]

You can also use get much fancier by using functions to determine the Start and End Dates of a week, a month, a quarter, or a year and modify the input accordingly.

Note that this even works for a single day, but it is clumsy since you need to enter the same date twice.

would these have to be in seperate fields in the query or do I have to integrate them into the existing fields? So I would put this at the beginning of the profit query to set the value?

After I do this, would I create a form and add combo boxes to make it more user friendly?

 

[boblarson]

I always use an input form for parameters so that someone can enter it once and run multiple reports. Here's a sample:
http://downloads.btabdevelopment.com/Samples/dates/ReportAndForm.zip

 

[BenW]

I always use an input form for parameters so that someone can enter it once and run multiple reports. Here's a sample:
http://downloads.btabdevelopment.com/Samples/dates/ReportAndForm.zip

Thank you the file was extremely helpful. I ran into an issue though trying to model off your file.

I created the fields to type in the date, and redid my query to hold the date function, but now everytime I try to run the query I get an error that says:

Circular reference caused by alias 'Sum of Net$' in query definition SELECT list.

I opened the query in design view and a new field that I never entered is there called SaleNet.

I tried to delete it and save the query and run again, but now it keeps asking me for parameters i.e. what is the value of Sum of Sale$. I already put the table source.

The whole thing looks like a mess right now. I've been at it for several hours, but I can't really understand what's wrong with it.

If you could look at my file and tell me what's wrong then I could try to fix it. The Form I'm using is called FORMSaleNet and FORMTotalDate I want FORMTotalDate to open after I enter the values for FORMSaleNet. The query being used is SaleNet Query and the table is Data Sheet.

I would greatly appreciate anyone that takes the time to help. I realize that the file is probably a mess right now, so I can upload the original before I modified it if it helps at all.

 

[boblarson]

Okay, I think we have quite a bit to fix here.

1. Read up on normalization. You are storing text values of things that should be ID's (like customers, companies, etc.) http://support.microsoft.com/kb/283878

2. You can't put date criteria in a column where it isn't a date (sale-net). The criteria has to go on a date field.

3. You should NOT be using special characters in your field and object names (no using $ or #) as those have special meaning in Access and can honk things up if you start doing that. You should keep all special characters (with the exception of the underscore _ ) out of the names of fields or objects.

4. You should avoid using spaces in field or object names as well.

 

[BenW]

Ok. I think I fixed 2, 3, and 4. The reason I put text for the company ID was because it was originally a number system, but my parents told me it was too difficult for them to remember a company by number so they preferred using text abbreviations for the company's ID.

I put the criteria: Between [forms]![FORMDateProfit]![txtBegDate] And [forms]![FORMSaleNet]![txtEndDate]

into a date field by adding it into my query, and I tried to make create a button that opens a form displaying the profit values between the dates, but after I typed in the dates and hit the button, the report would not open.

I realized that your file was using text boxes and not combo boxes when asking for the date. I changed mine to text boxes as well so I think that fixed the issue.

When the form first opens, I created a button on the left to test the date-profit sorting function. The button 'Test Date Profit' will open the form 'FORMDateProfit' which will ask for 2 dates and then the button on the form will open a report displaying the profit between the two dates.

I think I set up things correctly, but the button will not get the report to open from the form that I created.

Thank you very much for the help thus far.