Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 1046
  • Last Modified:

query sum and display in access form

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]));

Open in new window

0
samipk
Asked:
samipk
  • 6
  • 3
1 Solution
 
therealmongooseCommented:
Here's a solution - bit complex to explain in one go, so have a look at the example...
EE-Date-Parameters.mdb
0
 
samipkAuthor Commented:
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
 
therealmongooseCommented:
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
therealmongooseCommented:
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
 
samipkAuthor Commented:
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
 
therealmongooseCommented:
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
 
samipkAuthor Commented:
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
 
therealmongooseCommented:
Apologies - been out of the country for a copule of days - will review again...
 
0
 
therealmongooseCommented:
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

Windows Server 2016: All you need to know

Learn about Hyper-V features that increase functionality and usability of Microsoft Windows Server 2016. Also, throughout this eBook, you’ll find some basic PowerShell examples that will help you leverage the scripts in your environments!

  • 6
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now