Calculating Old English Money

[gbjc105]

Hey, i am a masters student and part of my course involves doing an access project. My problem is that all of the currency is old english, pounds, shillings, pence etc.



For those not aware, there are 20 Shillings to a Pound and 12 pence to a shilling.

In my tables i have fields for Pounds, Shillings and Pence. I want to try and work out a way to add up their totals and dynamically move totals accross the fields so that when the shillings total gets to 20, it automatically creates adds an extra pound and likewise with the pence and shillings.

Is this possible?

Many thanks,

Grant.

[sesproul]

This is a very open ended question.

But...

If you are simply adding records where each individual record is below the "tipping point" into the next denominator of currency. i.e

12 lb, 4 Shillings, 10 Pence.

perform a SQL statement like this (qrySumEnglishMoney):

Select
sum(colPounds) as SumPounds,
Sum(colSchillings) as SumSchillings,
Sum(colPence) as SumPence

FROM
ENGLISHCURRENCYTABLE


THEN write a routine that retrives the recordset and moving from Pence column to Schilling column to pound column and Retrieves the value, captures it and calculates the overflow into the next denomination:

'---Requires the Microsoft ActiveX Data objects reference be selected

Public Function CalcMoney()

Dim oConn As New ADODB.Connection
Dim oRS As New ADODB.Recordset
oConn.ConnectionString = "Connection string to Local DB I don't Have code but someone will it may be project.localconnection"
oRS.Open "select * from qrySumEnglishMoney", oConn
Dim lpence As Long
Dim lSchillings As Long
Dim lPounds As Long
Dim lOverflow As Long

lpence = oRS("SumPence")
lSchillings = oRS("SumSchillings")
lPounds = oRS("SumPounds")

'---Check for more than 12 pence
lOverflow = 0
lOverflow = Int(lpence / 12)
If lOverflow > 0 Then
lpence = lpence Mod 12
End If
lSchillings = lSchillings + lOverflow

'---Check for more than 20 Schillings
lOverflow = 0
lOverflow = Int(lSchillings / 20)
If lOverflow > 0 Then
lSchillings = lSchillings Mod 20
End If
lPounds = lPounds + lOverflow

'---Now you have the values to insert into a database or return from this function etc...

End Function

[gbjc105]

Thanks a lot, where exactly should the code go? I have not yet looked into any visual basic. Also what would i need to do for the connection string bit?

[sesproul]

We may be figuring this out together

How are you capturing the Values? I can only assume that somewhere you plan to have a form to enter the data into the table, and then at some point you want to run the summation of the values.

At the point you could throw a button on a form that would have the code behind it. But I would need a little more info on your design.


Steve

[gbjc105]

Hey, its ok i sorted it out, i actually used SQl and a query to do it.