| View previous topic :: View next topic |
| Author |
Message |
Julianna
Power User
Joined: 15 Oct 2005
Posts: 77
|
 Posted: Tue Jan 01, 2008 11:12 am Post subject: Macro help with dates & calculations |
 |
|
Hi.
Drew Jensen had helped me create a macro for a database (http://www.oooforum.org/forum/viewtopic.phtml?t=28613&highlight=), but I'm having some difficulties with updating it.
I had originally updated the CalculateTotals sub to account for a tax change in 2006. The code I provided in the thread for that is actually incomplete, and should be:
| Code: |
sub CalculateTotals( oev as variant )
'
' Fill in the following fields in
' Orders table
' Subtotal = (Asking_Price + Asking_Price2 + Asking_Price3 + Asking_Price4 + Asking_Price5) -
' ((Asking_Price + Asking_Price2 + Asking_Price3 + Asking_Price4 + Asking_Price5) * Discount_Rate)
'
' Total = Subtotal + (Subtotal * GST) + (Subtotal * PST) + Shipping_Cost
dim RowSet as variant
dim Sale_Date as double
dim Asking as double
dim discount as double
dim GST as double
dim PST as double
dim shipping as double
dim subtotal as double
dim Total as double
If Sale_Date < 07/01/06 Then
RowSet = oEv.Source.Model.Parent
with RowSet.columns
Asking = .getByName("Asking_Price").getDouble
Asking2 = .getByName("Asking_Price2").getDouble
Asking3 = .getByName("Asking_Price3").getDouble
Asking4 = .getByName("Asking_Price4").getDouble
Asking5 = .getByName("Asking_Price5").getDouble
Discount = .getByName("Discount_Rate").getDouble
SubTotal = ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) - ( ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) * Discount )
GST = SubTotal * .07
PST = SubTotal * .08
shipping = .getByName("Shipping_Cost").getDouble
TOTAL = SubTotal + GST + PST + Shipping
.getByName("Subtotal").updateDouble( subtotal )
.getByName("GST").updateDouble( GST )
.getByName("PST").updateDouble( PST )
.getByName("Total_Cost").updateDouble( TOTAL )
end with
ElseIf Sale_Date > 07/01/06 Then
RowSet = oEv.Source.Model.Parent
with RowSet.columns
Asking = .getByName("Asking_Price").getDouble
Asking2 = .getByName("Asking_Price2").getDouble
Asking3 = .getByName("Asking_Price3").getDouble
Asking4 = .getByName("Asking_Price4").getDouble
Asking5 = .getByName("Asking_Price5").getDouble
Discount = .getByName("Discount_Rate").getDouble
SubTotal = ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) - ( ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) * Discount )
GST = SubTotal * .06
PST = SubTotal * .08
shipping = .getByName("Shipping_Cost").getDouble
TOTAL = SubTotal + GST + PST + Shipping
.getByName("Subtotal").updateDouble( subtotal )
.getByName("GST").updateDouble( GST )
.getByName("PST").updateDouble( PST )
.getByName("Total_Cost").updateDouble( TOTAL )
end with
Else
RowSet = oEv.Source.Model.Parent
with RowSet.columns
Asking = .getByName("Asking_Price").getDouble
Asking2 = .getByName("Asking_Price2").getDouble
Asking3 = .getByName("Asking_Price3").getDouble
Asking4 = .getByName("Asking_Price4").getDouble
Asking5 = .getByName("Asking_Price5").getDouble
Discount = .getByName("Discount_Rate").getDouble
SubTotal = ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) - ( ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) * Discount )
GST = SubTotal * .06
PST = SubTotal * .08
shipping = .getByName("Shipping_Cost").getDouble
TOTAL = SubTotal + GST + PST + Shipping
.getByName("Subtotal").updateDouble( subtotal )
.getByName("GST").updateDouble( GST )
.getByName("PST").updateDouble( PST )
.getByName("Total_Cost").updateDouble( TOTAL )
end with
end If
end sub
|
...or so I think. I was playing around with the code to get it to recognize another tax change, and now the above code doesn't seem to do anything, date-wise (i.e. the GST calculates at 7% regardless of the Sale_Date).
The database it's supposed to work on is:
https://sourceforge.net/project/showfiles.php?group_id=160483&package_id=180549
(ArtDatabaseTwoTaxesv1.1.1.odb.zip)
Anyone out there have time to help me figure out what the problem is?
Thanks.
Julianna |
|
| Back to top |
|
 |
probe1
Moderator
Joined: 18 Aug 2004
Posts: 2465
Location: Chonburi Thailand Asia
|
| Posted: Sun Jan 13, 2008 9:41 pm Post subject: |
|
|
Julianna,
SALE_DATE is defined as double, so that your IF/ELSE combination should ask for a double (you coded a multi division; 07/01 is 7, 7/06 is 1.17 - that's not your date)
I assume this is american date notation (m/d/y), so...
Try
| Code: | | If Sale_Date > 38899 Then |
38899 is the internal date representation for 1.July 2006 (date based on standard 1899-12-31, see Tools>Options>OpenOffice.org Calc>Calculations)
BTW: what if Sale_Date IS exactly 1. July?
HTH
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Mon Jan 14, 2008 3:23 am Post subject: |
|
|
Sorry, WInfried
Formatted doubles as date/times do not work with databases. Dates are a distinct type. If the underlying database engine provides a conversion from double to date/time, then it would use SQL standard 1900-01-01 as day zero rather than OOo standard 1899-12-31. Paste some values from Calc into Base. Dates are +2 days off unless the Calc document uses 1900-01-01 as day zero.
@Julianna,
| Code: |
...
dim Sale_Date as double
...
If Sale_Date < 07/01/06 Then
...
|
Apart from the syntax problem in '07/01/06', where does double Sale_Date come from? If it where a spreadsheet value, it would be a double indeed and you could calculate the way as Winfried suggested. But your database seems to be unrelated to spreadsheets. |
|
| Back to top |
|
|
probe1
Moderator
Joined: 18 Aug 2004
Posts: 2465
Location: Chonburi Thailand Asia
|
| Posted: Tue Jan 15, 2008 7:48 am Post subject: |
|
|
Villeroy,
of course you are right: I saw the comparison and jumped on that - forgetting it was about Base, not Calc.
I should stay at/in the pool....
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs |
|
| Back to top |
|
|
Julianna
Power User
Joined: 15 Oct 2005
Posts: 77
|
| Posted: Fri Jan 18, 2008 1:27 pm Post subject: |
|
|
| Villeroy wrote: | Sorry, WInfried
@Julianna,
| Code: |
...
dim Sale_Date as double
...
If Sale_Date < 07/01/06 Then
...
|
Apart from the syntax problem in '07/01/06', where does double Sale_Date come from? If it where a spreadsheet value, it would be a double indeed and you could calculate the way as Winfried suggested. But your database seems to be unrelated to spreadsheets. |
The Sale_Date comes from a table in the database, and that field is populated before the tax gets triggered.
I must admit I'm having some trouble following the syntax discussion. Winfried's comment I understood, but I'm not sure what to do with yours.
As for "BTW: what if Sale_Date IS exactly 1. July?", I figured I'd use a => or <= statement at some point, but I wanted to tackle the date issue first.
Thanks. |
|
| Back to top |
|
|
probe1
Moderator
Joined: 18 Aug 2004
Posts: 2465
Location: Chonburi Thailand Asia
|
| Posted: Fri Jan 18, 2008 7:02 pm Post subject: |
|
|
| Julianna wrote: | | The Sale_Date comes from a table in the database |
It's defined with dim Sale_Date as double at top of your posted code - and the variable is not assigned by any statement I see. So it should be zero at time of comparison?
Issue a print Date_Sale to see.
If it *is* set somewhere (i don't see), you can....
The internal HSQLDB stores date fields in form defined by java.sql.date, which is (since 1.4) a long, representing seconds since 1970-01-01.
So the value to compare with has to be in same form (long, containing seconds).
You can use the BASIC runtime function DATEDIFF to calculate the seconds, see F1 online help
Code should be
| Code: | If Sale_Date < DateDiff("s", "1/1/1970", "1/7/2006") Then
|
Hope I got it right, this time.
_________________
Cheers
Winfried
My Macros
DateTime2 extension: insert date, time or timestamp, formatted to your needs |
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sat Jan 19, 2008 3:09 am Post subject: |
|
|
Removed redundant code in the if...elseif...else structure. I assume the form's recordset includes a date field "Sale_Date". See comments.
| Code: |
sub CalculateTotals( oev as variant )
'
' Fill in the following fields in
' Orders table
' Subtotal = (Asking_Price + Asking_Price2 + Asking_Price3 + Asking_Price4 + Asking_Price5) -
' ((Asking_Price + Asking_Price2 + Asking_Price3 + Asking_Price4 + Asking_Price5) * Discount_Rate)
'
' Total = Subtotal + (Subtotal * GST) + (Subtotal * PST) + Shipping_Cost
dim RowSet as variant
REM dim Sale_Date as double 'No doubles, we compare dates
dim Sale_Date '< this will be the generic value from the database
dim bas_Sale_Date as date '< this will be a type of the Basic language
dim bas_CompareDate as date '< this will be a type of the Basic language
dim Asking as double
dim discount as double
dim GST as double
dim PST as double
dim shipping as double
dim subtotal as double
dim Total as double
REM get the form
RowSet = oEv.Source.Model.Parent
REM create a date value to compare with:
bas_CompareDate = Date(2006, 7, 1) '1st of July 2006
with RowSet.columns
REM get date value of form's current record's field named "Sale_Date":
Sale_Date = .getByName("Sale_Date").getDate()
REM Sale_Date is a structure containing 3 numbers year, month and day to be used in any language
REM We want to use this date in Basic. We use a Basic function to convert the structure into Basic's date type (see Basic help on function DateSerial)
bas_Sale_Date = DateSerial(Sale_Date.Year, Sale_Date.Month, Sale_Date.Day)
Asking = .getByName("Asking_Price").getDouble
Asking2 = .getByName("Asking_Price2").getDouble
Asking3 = .getByName("Asking_Price3").getDouble
Asking4 = .getByName("Asking_Price4").getDouble
Asking5 = .getByName("Asking_Price5").getDouble
Discount = .getByName("Discount_Rate").getDouble
SubTotal = ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) - ( ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) * Discount )
If bas_Sale_Date < bas_CompareDate Then
GST = SubTotal * .07
PST = SubTotal * .08
else
GST = SubTotal * .06
PST = SubTotal * .08
endif
shipping = .getByName("Shipping_Cost").getDouble
TOTAL = SubTotal + GST + PST + Shipping
.getByName("Subtotal").updateDouble( subtotal )
.getByName("GST").updateDouble( GST )
.getByName("PST").updateDouble( PST )
.getByName("Total_Cost").updateDouble( TOTAL )
end with
end sub
|
There are constants .08 and .06 from where you calculate your GST and PST. If those constants are something like changing tax rates in the real world, then you have to distribute a new version every time when the rates change. I would implement a table where changes can be entered and let a sub-form get the current rate at the time of Sales_Date. Possibly you could drop most of that crazy Basic code, show the calculated total in a sub form and use a macro to write the calculation result into the main form. |
|
| Back to top |
|
|
Julianna
Power User
Joined: 15 Oct 2005
Posts: 77
|
| Posted: Sat Jan 19, 2008 7:22 am Post subject: |
|
|
| Villeroy wrote: |
There are constants .08 and .06 from where you calculate your GST and PST. If those constants are something like changing tax rates in the real world, then you have to distribute a new version every time when the rates change. I would implement a table where changes can be entered and let a sub-form get the current rate at the time of Sales_Date. Possibly you could drop most of that crazy Basic code, show the calculated total in a sub form and use a macro to write the calculation result into the main form. |
When I originally created the db, taxes for Ontario hadn't changed in years. Then it changed twice in the past three years!
The idea of a table is great. I was actually playing around with adding a GST_Rate and PST_Rate to the table so it can be calculated for every sale. Not as efficient for high sales volumes, but it would also give the option of not charging tax to people who are exempt from the taxes.
I added GST_Rate and PST_Rate to the table, added and linked the fields to the form and modified the code, but the GST and PST aren't calculating at all now.
I checked the table, and the values I populated into the GST_Rate and PST_Rate fields from the form have been saved into the table (so I know the field was added to the form correctly).
| Code: |
sub CalculateTotals( oev as variant )
'
' Fill in the following fields in
' Orders table
' Subtotal = (Asking_Price + Asking_Price2 + Asking_Price3 + Asking_Price4 + Asking_Price5) -
' ((Asking_Price + Asking_Price2 + Asking_Price3 + Asking_Price4 + Asking_Price5) * Discount_Rate)
'
' Total = Subtotal + (Subtotal * GST) + (Subtotal * PST) + Shipping_Cost
dim RowSet as variant
dim Asking as double
dim discount as double
dim GST_Rate as double
dim PST_Rate as double
dim GST as double
dim PST as double
dim shipping as double
dim subtotal as double
dim Total as double
REM get the form
RowSet = oEv.Source.Model.Parent
with RowSet.columns
Asking = .getByName("Asking_Price").getDouble
Asking2 = .getByName("Asking_Price2").getDouble
Asking3 = .getByName("Asking_Price3").getDouble
Asking4 = .getByName("Asking_Price4").getDouble
Asking5 = .getByName("Asking_Price5").getDouble
Discount = .getByName("Discount_Rate").getDouble
SubTotal = ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) - ( ( Asking + Asking2 + Asking3 + Asking4 + Asking5 ) * Discount )
GST = ( SubTotal * GST_Rate )
PST = ( SubTotal * PST_Rate )
shipping = .getByName("Shipping_Cost").getDouble
TOTAL = SubTotal + GST + PST + Shipping
.getByName("Subtotal").updateDouble( subtotal )
.getByName("GST").updateDouble( GST )
.getByName("PST").updateDouble( PST )
.getByName("Total_Cost").updateDouble( TOTAL )
end with
end sub
|
|
|
| Back to top |
|
|
Villeroy
Super User
Joined: 04 Oct 2004
Posts: 10065
Location: Germany
|
| Posted: Sat Jan 19, 2008 7:38 am Post subject: |
|
|
| Mmmmh, why do you write calculated values into the table? I mean, you could derive the calculated TOTAL at any time from existing values (including tax rates). |
|
| Back to top |
|
|
Julianna
Power User
Joined: 15 Oct 2005
Posts: 77
|
| Posted: Sat Jan 19, 2008 12:26 pm Post subject: |
|
|
| The original intent was for the values to be stored and locked, but it seems the macro simply changes the values every time the form is launched. |
|
| Back to top |
|
|
Julianna
Power User
Joined: 15 Oct 2005
Posts: 77
|
| Posted: Sun Feb 03, 2008 6:37 am Post subject: |
|
|
I needed some time away from this so I could look at it properly. The following code uses additional "GST_Rate" and "PST_Rate" fields in the Orders table for the calculation.
By setting these fields to default with the current rates, this will allow for the tax rates to change however often they want and the rate only needs to be changed in the table settings. This also allows the user to override the tax rate for tax exemption situations.
| Code: | sub CalculateTotals( oev as variant )
'
' Fill in the following fields in
' Purchase_Order table
' SUBTOTAL = ASKING_PRICE - (ASKING_PRICE * DISCOUNT_RATE)
'
' TOTAL = SUBTOTAL + (SUBTOTAL * GST) + (SUBTOTAL * PST ) + SHIPPING_COST
dim RowSet as variant
dim Sale_Date as double
dim Asking as double
dim discount as double
dim GSTRate as double
dim PSTRate as double
dim GST as double
dim PST as double
dim shipping as double
dim subtotal as double
dim Total as double
RowSet = oEv.Source.Model.Parent
with RowSet.columns
Asking = .getByName("Asking_Price").getDouble
Discount = .getByName("Discount_Rate").getDouble
GSTRate = .getByName("GST_Rate").getDouble
PSTRate = .getByName("PST_Rate").getDouble
SubTotal = Asking - ( Asking * Discount )
GST = SubTotal * GSTRate
PST = SubTotal * PSTRate
shipping = .getByName("Shipping_Cost").getDouble
TOTAL = SubTotal + GST + PST + Shipping
.getByName("Subtotal").updateDouble( subtotal )
.getByName("GST").updateDouble( GST )
.getByName("PST").updateDouble( PST )
.getByName("Total_Cost").updateDouble( TOTAL )
end with
end sub
|
|
|
| Back to top |
|
|
|
