[Home]   [FAQ]   [Search]   [Memberlist]   [Usergroups]   [Register]

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:

...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
probe1
Moderator

Joined: 18 Aug 2004
Posts: 2551
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
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10081
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.
probe1
Moderator

Joined: 18 Aug 2004
Posts: 2551
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
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.
probe1
Moderator

Joined: 18 Aug 2004
Posts: 2551
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
Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10081
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.

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.
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).

Villeroy
Super User

Joined: 04 Oct 2004
Posts: 10081
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).
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.
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
 Display posts from previous: All Posts1 Day7 Days2 Weeks1 Month3 Months6 Months1 Year Oldest FirstNewest First
 All times are GMT - 8 Hours Page 1 of 1

 Jump to: Select a forum OpenOffice.org Forums----------------Setup and TroubleshootingOpenOffice.org WriterOpenOffice.org CalcOpenOffice.org ImpressOpenOffice.org DrawOpenOffice.org MathOpenOffice.org BaseOpenOffice.org Macros and APIOpenOffice.org Code Snippets Community Forums----------------General DiscussionSite Feedback
You cannot post new topics in this forum
You cannot reply to topics in this forum
You cannot edit your posts in this forum
You cannot delete your posts in this forum
You cannot vote in polls in this forum