Solved

# query sum and display in access form

Posted on 2009-04-29
994 Views
i have a query that generates sum of some fields according to the date that a user enter in the popup dialogs (i.e. Enter Starting Date and Enter Ending Date) and then i show those sums in text fields on a form

what i want to do is to show the date that is entered in the Enter Starting Date and Enter Ending Date popup dialogs in text fields on the form along with the rest of the data and i also want to sum these date values and return a total of how many weekdays were in that range i.e. if a user searched for data from 16 april to 23 april i want the form to display 6 days in a text box in the form

i am attaching the query i am using
``````SELECT Sum(tblDataSummaries.TV) AS SumOfTotalVolume, Sum(tblDataSummaries.TValue) AS SumOfTotalValue, Sum(tblDataSummaries.TV1125) AS SumOfTV1125, Sum(tblDataSummaries.TV2650) AS SumOfTV2650, Sum(tblDataSummaries.TV101125) AS SumOfTV101125, Sum(tblDataSummaries.TV110) AS SumOfTV110, Sum(tblDataSummaries.TV126150) AS SumOfTV126150, Sum(tblDataSummaries.TV151175) AS SumOfTV151175, Sum(tblDataSummaries.TV176200) AS SumOfTV176200, Sum(tblDataSummaries.TV5175) AS SumOfTV5175, Sum(tblDataSummaries.TV76100) AS SumOfTV76100, Sum(tblDataSummaries.TVabove200) AS SumOfTVabove200

FROM tblDataSummaries

WHERE (((tblDataSummaries.DataDate) BETWEEN [Enter Starting Date] And [Enter Ending Date]));
``````
0
Question by:samipk

LVL 10

Expert Comment

Here's a solution - bit complex to explain in one go, so have a look at the example...
EE-Date-Parameters.mdb
0

LVL 6

Author Comment

thank you for the design but i am a novice at access and its fairly complex what you attached....isnt there an easy way to do this? or can you explain how to do it one by one
0

LVL 10

Expert Comment

There are two text boxes on the form, txtFromDate and txtToDate. Each of these has a button next to them which when is pressed calls a bespoke vba (Visual Basic for Applications) function called "fncGetDate() - this returns a date to the text box concerned...
To view the code behind the buttons , right click and choose build event you will see (for example):
Private Sub comSetFromDate_Click()

Me.txtFromDate = fncGetDate(Date - 1, "Select From Date")

End Sub
You will see 2 values in the brackets of the function - these are called arguments. The first is date-1 - this means yesterdays date, and the argument sets the default selected value of the calender control. The second argument is "Select From Date" - this is the caption at the top of the calender control - these 2 arguments allow better cusomization of the control (for example if you use the control as a date selector on a monthly report, you may want to default the first date to date - clng(format(date, "dd")) +1 - which equates to the first day of the current month.
You don't need to worry too much about the calender control or  fncGetDate - these aren't really part of the solution, but just provide a good way for your user to select a date.
The main bulk of the solution is how the process gets the data from the database. Pressing the Display Results button fires a section of code which build an sql statement - this is the main issue as it's difficult to automate a query with parameters in VBA
(i.e. WHERE (((tblDataSummaries.DataDate) BETWEEN [Enter Starting Date] And [Enter Ending Date] contains parameters "[Enter Starting Date]" and "[Enter Ending Date] ". The query will fall over in VBA as there's no easy way to pass these values to the query. The get around is to build an sql statement which includes date values supplied by the user on the form (i.e. txtFromDate and txtToDate) and build these into the statement. These are converted to data type long values using cLNG() function. this turns the date into a whole number and makes the query run better as dates can often be confused in query statements depending on the regional settings on your PC.
To get your results to display on the form, a recordset object is create which uses the sql statement as it's sql. The if statements at the beginning of the procedure check if a user has entered a valid date range. the values from the recordset are then pasted to the controls on the form to display the totals...

Where txtTotalVolume is the name of a control on the form and rst!totalVolume is the field in the recordset.

Finally the recordset is closed to clear it from memory using the rst.close and set rst = nothing statements.

I appreciate the is a lot to take in on this one, and if you are a novice to Access would recommend a copule of books:
http://www.amazon.co.uk/Inside-Relational-Databases-Mark-Whitehorn/dp/1852334010

This is great for understanding table structure and design etc...
The other book is
http://www.amazon.co.uk/Beginning-Access-2002-Dave-Sussman/dp/186100821X/ref=sr_1_2?ie=UTF8&s=books&qid=1241008220&sr=1-2
Which is a good introduction to Visual Basic for Applications programming.

The solution should work if you just import the frmMain + frmCalender forms, and the module mod_fnc_GetDate into your existing database. You may need to rename frmMain, but this shouldn't cause any problems. To import to your existing database, save my file to your hard drive and open your database - choose Import from the file menu and then find my database - select the above objects and click import / open....
Just read your original question and looks like I missed the point about displaying separte dates - I'll post an example again shortly.

Good Luck

0

LVL 10

Expert Comment

Ok - here's a revised file with a different method - there are 2 queries in the database - the "qry_Results_Base" qyert reads whatever dates are in the from and to boxes on the form frmMain2 and selects source data from the table.
The second query qry_Results_Display sums the totals for the data selected and displays by date - the form frmMain2 uses this query as it's row source.
The "Display results" button checks that valid date range and requeries the form to show the new results....
As before, if you import into your database the solution should work...

Kind regards,
Mongoose

EE-Date-Parameters.mdb
0

LVL 6

Author Comment

thank you for your detailed feedback and detailed explanation i just wanted to point out some problems i am facing....

firstly i would not like to have the calendar as i want users to input the values manually(they feel better as if they want to go a record 3 4 years back they dont have to scroll)

secondly as you can from the picture i attached my resulting form displays the sum of each field between the entered dates in a single form view....i tried using your code to display results in this manner but it doesnt happen and it displays data for each date individually but i require that the sum of the data of each field be displayed

also as per my question how can i display the total number of workddays during the date range selected?
untitled.JPG
0

LVL 10

Expert Comment

Always helps if you read the question properly (oops ;-) ) When you say week day, do you mean Mon - Fri or count of days in range? I can amend the database accordingly...
0

LVL 6

Author Comment

well you can just count the number of datedates that are in that range , i,e, if i select a range from 14/04/09 to 17/04/09 and i have the data of all those 4 dates it should display 4 but if i pick data from 14/04/09 to 17/04/09 but i dont have data for 16/04/09 it should display 3
0

LVL 10

Expert Comment

Apologies - been out of the country for a copule of days - will review again...

0

LVL 10

Accepted Solution

I have amended the solution to show number of days in the range.... Hopefully this now does what you need...
EE-Date-Parameters.mdb
0

## Featured Post

### Suggested Solutions

It took me quite some time to sort out all the different properties of combo and list boxes available from Visual Basic at run-time. Not that the documentation is lacking: the help pages are quite thorough and well written. The problem was rather wh…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…