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

1. Competent Performer
Windows 7 64bit Access 2010 64bit
Join Date
Dec 2011
Posts
128

## 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. Till someone comes along, just check out if below gives some guidelines :
Code:
```SELECT
Sum(a.numItems) AS SumOfnumItems,
FROM
(
SELECT
myTable.DayDate,
myTable.numItems,
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. 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. 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. 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)

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!

6. Am not in to VBA.
We will have to wait for some one to come along.

Thanks

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

8. Till someone comes along, just check attachment.
Open the form & see how it goes.

9. 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. Originally Posted by Heatshiver
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 ?

Originally Posted by Heatshiver
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. 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. Sorry, my brain's gone blank.
Just post your latest TestDB & someone should be able to help you along.

Thanks

13. 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!

#### Posting Permissions

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