Link to home
Start Free TrialLog in
Avatar of gracie1972
gracie1972Flag for United States of America

asked on

Quarters and Total days calculations not working correctly on a Form/Query

I have a table that I created from a Query out of SQL.

tblFiscalQuarters_2
The Data looks where my data like similar to this:
FStart       FEnd              Year              FYear      FQ      Days       Vacation       Weekends      Total
1/1/2012       2/29/2012      2012      FY2012      FQ3      60              2                 17                 41
3/1/2012       5/31/2012      2012      FY2012      FQ4      92              1                 26                 64
6/1/2012       8/31/2012      2012      FY2013      FQ1      92              1                 26                 64
9/1/2012      11/30/2012      2012      FY2013      FQ2      91              3                 26                 62
----------------------------------------------------------------------------------------------------------------------------------------
SECOND TABLE:
Copy Of tblProjects1
ProjectID
Project
FStartQ
FEndQ
Days
-----------------------------------------------------------------------------------------------------------------------------------------
On my form in Access I will select:
ProjectID
Project
FStartQ:
FYear (ComboBox pulling from tblFiscalQuarts), then FQ (populated by VB Code) , then Days (populated by VB Code).
FEndQ:
FYear (ComboBox pulling from tblFiscalQuarts), then FQ (populated by VB Code) , then Days (populated by VB Code).
of days(populated by VB Code).
Both Days fields total (VB Script) and are stored the main table.

DAYS:
Days need to automatically sum (FEndQ.Days + FStartQ.Days) and store in main table.
-------------------------------------------------------------------------------------------------------------------------
 
However if my users choose FY2013, FQ1 and FY2014 FQ1, it is not totaling all the days in the range.
Plus my VB Code is concatenating the numbers and not subtracting them.


I attached the DB to make it easier to understand what I am doing.
Database1.mdb
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

use this codes


Private Sub QEnd_AfterUpdate()
Me.QEndDays.Value = Me.QEnd.Column(2)
Me.CountDays.Value = CLng(Me.QStartDays.Value) + CLng(Me.QEndDays.Value)
End Sub
Avatar of gracie1972

ASKER

What if they want to do a range?  Your VB Code worked.  

However, if there are:

NikeQtr              DaysCount
FY2013 Q1      92
FY2013 Q2      91
FY2013 Q3      59
FY2013 Q4      92

And the user chooses FY2013 Q1  ending  FY2013 Q3  How does can I know to calculate the range of all ?  92+91+59 ?
ASKER CERTIFIED SOLUTION
Avatar of Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1)
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Perfect!  Thanks!