Results 1 to 13 of 13
  1. #1
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128

    Question Simple Calculation, but how to manage Date Variable (Range)

    I have a formula that at first glance I thought would be simple to do as an expression:


    Take a range of days for two fields and multiply their values for each day. Then add each of those values to the other days. Finally, divide by the sum value of one of the fields.


    EXAMPLE:


    4/1/12 - [numItems] = 5; [numBuys] = 3
    4/2/12 - [numItems] = 7; [numBuys] = 4


    Sum Total of "numItems" = 12


    Formula = ((5*3)+(7*4))/12 = 3.5833




    My problem is that because the user can have a date range, I am unsure how to address this. I was thinking that VBA may be better...?


    Any help would be appreciated. Thank you.

  2. #2
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Till someone comes along, just check out if below gives some guidelines :
    Code:
    SELECT 
        Sum(a.numItems) AS SumOfnumItems, 
        Sum(a.NumItemsXnumBuys) AS SumOfNumItemsXnumBuys, 
        [SumOfNumItemsXnumBuys]/[CountOfnumItems] AS TheResult
    FROM 
        (
            SELECT 
                myTable.DayDate, 
                myTable.numItems, 
                myTable.numBuys, 
                [numItems]*[numBuys] AS NumItemsXnumBuys
            FROM 
                myTable
            WHERE 
                (((myTable.DayDate)>=#4/1/2012# And (myTable.DayDate)<=#4/2/2012#));
        ) AS a;
    Not in to reports, but perhaps should be easier in reports

    Thanks

  3. #3
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    Thank you Recyan, I think the SQL does help for guidance.

    But what if the dates are different as the dates will never be definitive?

  4. #4
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    You can replace the Dates in the Query with Form field names, etc., if you plan to use a form to pass the start date & end date, of the range.

    Thanks

  5. #5
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    Hi recyan,

    I was getting some help I suggested your route. They came back with this code:

    Function fnCalcWhatINeed(StartDate as date, EndDate as sate) as double

    dim strQRY as string
    dim sunItems as long
    dim sumItemsBuys as long

    strQRY = "Select [numItems], [numItems]*[numBuys] As [ItemsBuys] " & "From [Yourtable] " & "WHERE Format([ItemDate], 'yyyymmdd') >= " & Format(StartDate, "yyyymmdd") & " AND Format([ItemDate], 'yyyymmdd') <= " & Format(EndDate, "yyyymmdd")

    sunItems = DSUM("[NumItems]", strQRY)
    sumItemsBuys = DSUM("[ItemsBuys]", strQRY)

    fnCalcWhatINeed = sumItemsBuys/sumItems end function

    I created a dummy database to test this and can't get it working quite right. I also have an expression in a textbox in a report that is meant to call the function, which may be where the problem lies.


    I also replaced the WHERE statement in this code with:

    "WHERE [ItemDate] BETWEEN " & Format(StartDate, "\#mm\/dd\/yyyy\#") & " AND " & Format(EndDate, "\#mm\/dd\/yyyy\#")

    The only different in my DB is that [ItemDate] is [Days]. Any help would be appreciated!
    Attached Files Attached Files

  6. #6
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Am not in to VBA.
    We will have to wait for some one to come along.

    Thanks

  7. #7
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    Thank you for replying.

  8. #8
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Till someone comes along, just check attachment.
    Open the form & see how it goes.

  9. #9
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    This is actually brilliant! I didn't even consider this!

    My only problem is that they don't pick/place dates on a form, it is part of the criteria in the query. I can get the first date to multiply (and the rest of the equation works), but I don't know how to get the other dates to multiply since they aren't called from a form.

    Any thoughts?

    Thank you!

  10. #10
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Quote Originally Posted by Heatshiver View Post
    My only problem is that they don't pick/place dates on a form, it is part of the criteria in the query.
    Where does the query get the Start & End Dates from ?

    Quote Originally Posted by Heatshiver View Post
    I can get the first date to multiply (and the rest of the equation works), but I don't know how to get the other dates to multiply since they aren't called from a form.
    Could not understand this?

    Thanks

  11. #11
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    Sorry.

    The dates pop-up from the query. The Criteria for [Days] is: Between [Start Date] And [End Date]

    By leaving the above as-is, I get the Start Date to multiply across the two fields. However, any dates in between (including the End Date), are not multiplied. The rest of the equation is performed, e.g. add the numbers together then divide by the total sum of the first field.

  12. #12
    recyan's Avatar
    recyan is offline Expert
    Windows 2K Access 2000
    Join Date
    Dec 2011
    Posts
    662
    Sorry, my brain's gone blank.
    Just post your latest TestDB & someone should be able to help you along.

    Thanks

  13. #13
    Heatshiver is offline Competent Performer
    Windows 7 64bit Access 2010 64bit
    Join Date
    Dec 2011
    Posts
    128
    No worries, got it down. My error!

    Not that it matters, but in case anyone ever stumbles upon this, I took your sum textboxes from the DB and actually put them into one expression into one textbox using Sum.

    Thanks!

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Simple calculation box
    By jordz in forum Reports
    Replies: 1
    Last Post: 03-19-2012, 01:40 PM
  2. Date Range Report with Multiple Date Fields
    By StevenCV in forum Reports
    Replies: 13
    Last Post: 02-29-2012, 08:29 AM
  3. Simple field calculation
    By stryder09 in forum Access
    Replies: 4
    Last Post: 02-11-2011, 11:48 AM
  4. Set date field on subform to date variable
    By laavista in forum Access
    Replies: 4
    Last Post: 06-30-2010, 06:32 PM
  5. set date field on form to date variable
    By laavista in forum Access
    Replies: 3
    Last Post: 06-28-2010, 03:03 AM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums