Converting Figures To Words

Jul 6, 2006

Dear all,

I am creating a database which has an invoice printing form. In that I would like to have a column for the total amount in words. I have got a sample module from northwind database. According to that 100,000 is "one hundred thousand" but in my country that is pronounced as " 1 Lakh" and for one million it is 10 lakh, for 10 million it is 1 crore like that. Is that possible to change the code in that module to display the words according to our standards? I am attaching the code with this thread. If anybody can show how to do that...I will be thankful to them..


Thanks


Function ConvertCurrencyToEnglish(ByVal MyNumber)
Dim Temp
Dim Dollars, Cents
Dim DecimalPlace, count

ReDim Place(9) As String
Place(2) = " Thousand "
Place(3) = " Million "
Place(4) = " Billion "
Place(5) = " Trillion "

' Convert MyNumber to a string, trimming extra spaces.
MyNumber = Trim(Str(MyNumber))

' Find decimal place.
DecimalPlace = InStr(MyNumber, ".")

' If we find decimal place...
If DecimalPlace > 0 Then
' Convert cents
Temp = Left(Mid(MyNumber, DecimalPlace + 1) & "00", 2)
Cents = ConvertTens(Temp)

' Strip off cents from remainder to convert.
MyNumber = Trim(Left(MyNumber, DecimalPlace - 1))
End If

count = 1
Do While MyNumber <> ""
' Convert last 3 digits of MyNumber to English dollars.
Temp = ConvertHundreds(Right(MyNumber, 3))
If Temp <> "" Then Dollars = Temp & Place(count) & Dollars
If Len(MyNumber) > 3 Then
' Remove last 3 converted digits from MyNumber.
MyNumber = Left(MyNumber, Len(MyNumber) - 3)
Else
MyNumber = ""
End If
count = count + 1
Loop

' Clean up dollars.
Select Case Dollars
Case ""
Dollars = "No Dollars"
Case "One"
Dollars = "One Dollar"
Case Else
Dollars = Dollars & " Dollars"
End Select

' Clean up cents.
Select Case Cents
Case ""
Cents = " And No Cents"
Case "One"
Cents = " And One Cent"
Case Else
Cents = " And " & Cents & " Cents"
End Select

ConvertCurrencyToEnglish = Dollars & Cents
End Function


Private Function ConvertDigit(ByVal MyDigit)
Select Case Val(MyDigit)
Case 1: ConvertDigit = "One"
Case 2: ConvertDigit = "Two"
Case 3: ConvertDigit = "Three"
Case 4: ConvertDigit = "Four"
Case 5: ConvertDigit = "Five"
Case 6: ConvertDigit = "Six"
Case 7: ConvertDigit = "Seven"
Case 8: ConvertDigit = "Eight"
Case 9: ConvertDigit = "Nine"
Case Else: ConvertDigit = ""
End Select

End Function

Private Function ConvertHundreds(ByVal MyNumber)
Dim result As String

' Exit if there is nothing to convert.
If Val(MyNumber) = 0 Then Exit Function

' Append leading zeros to number.
MyNumber = Right("000" & MyNumber, 3)

' Do we have a hundreds place digit to convert?
If Left(MyNumber, 1) <> "0" Then
result = ConvertDigit(Left(MyNumber, 1)) & " Hundred "
End If

' Do we have a tens place digit to convert?
If Mid(MyNumber, 2, 1) <> "0" Then
result = result & ConvertTens(Mid(MyNumber, 2))
Else
' If not, then convert the ones place digit.
result = result & ConvertDigit(Mid(MyNumber, 3))
End If

ConvertHundreds = Trim(result)
End Function


Private Function ConvertTens(ByVal MyTens)
Dim result As String

' Is value between 10 and 19?
If Val(Left(MyTens, 1)) = 1 Then
Select Case Val(MyTens)
Case 10: result = "Ten"
Case 11: result = "Eleven"
Case 12: result = "Twelve"
Case 13: result = "Thirteen"
Case 14: result = "Fourteen"
Case 15: result = "Fifteen"
Case 16: result = "Sixteen"
Case 17: result = "Seventeen"
Case 18: result = "Eighteen"
Case 19: result = "Nineteen"
Case Else
End Select
Else
' .. otherwise it's between 20 and 99.
Select Case Val(Left(MyTens, 1))
Case 2: result = "Twenty "
Case 3: result = "Thirty "
Case 4: result = "Forty "
Case 5: result = "Fifty "
Case 6: result = "Sixty "
Case 7: result = "Seventy "
Case 8: result = "Eighty "
Case 9: result = "Ninety "
Case Else
End Select

' Convert ones place digit.
result = result & ConvertDigit(Right(MyTens, 1))
End If

ConvertTens = result
End Function

View Replies


ADVERTISEMENT

General :: Toggle Option - Converting Values To Words

Oct 7, 2013

I have form using a toggle option with four buttons. The values are 1,2,3 & 4. How can I convert the values to read (in the table) as words instead of values.

Example
1=dog
2=cat
3=bird
4=hamster

View 4 Replies View Related

How To Split Out All Words In Fields With Multiple Words

Aug 8, 2006

Hi Folks,

I am attempting to split out a descriptive field so that each words which may exist in that particular field will be split out into seperate fields ... for example, if the violation field is populated with a four worded description of 'Assualt on Police Officer' I would like to somehow strip out the words so that 'Assualt' and 'on' and 'Police' and 'Officer' are all in seperate fields. I am not sure if this can be done using a simple query in access ... if anyone can offer any suggestions, I would greatly appreciate it.

Thank you,

Joe

View 1 Replies View Related

Calculating Finance Figures

Mar 1, 2007

Hi
I would be glad of a little help.
I am creating a c ontracts database and need to keep trak of individual contractors figures.

I set up a contractor and allow the system to only issue orders to a contractor if there monthly balance doe not exceed 1/3 of their total monthly turn over.

My problem is in keeping track of the relevant figures and how I go about this?

Thanks in advance

View 6 Replies View Related

'Sum' Ignoring Minus Figures

Jan 22, 2007

I've been using the following query:

SELECT
[qry_1].Month,
B_Division_Group.Grouping_Name,
Sum([qry_1].Month_Client_Count_from_B) AS Monthly_Count,
Sum([qry_1].Month_Assets_from_B) AS Monthly_Total,
Sum([qry_1].YTD_Client_Count_from_B) AS Yearly_Count,
Sum([qry_1].YTD_Assets_from_B) AS Yearly_Total
FROM
([qry_1] INNER JOIN tbl_branch
ON [qry_1].BranchCode = tbl_branch.BranchID)
INNER JOIN B_Division_Group
ON tbl_branch.BranchName = BDivision_Group.N_Br
GROUP BY
[qry_1].Month,
B_Division_Group.Grouping_Name;

While all monthly and yearly values were positive, the query produced exactly the results expected (i.e. one row of data for each Grouping Name/Month combination, containing the overall totals for each field).

Now, however, some Grouping Names have minus values and the query is showing an extra row (one for positive values, one for negative). It's as if having one or more minus values is being treated as a new Grouping Name/Month combination.

Is Sum() the correct method to use, when dealing with negative values, or should I be using some other function?

View 3 Replies View Related

Top 5 Speed Figures Per Entrant

Apr 23, 2007

I have a query which returns each entrant with the speed figures in a descending order for previous races, I wish my query to return the top 5 speed figures per entrant or if the entrant has less than 5 previous runs it needs to return all available data.

I am not VBA literate, so as simple as possible please, thanks.

Freddy67

View 5 Replies View Related

Setting Up A Table For Sales Figures

May 6, 2005

Dear All,
My boss wants me to create a budget report as follows:

Rep Cust ActM$ BudgetM$ VarM$ ActMProfit BudgetMProfit VarMProfit

ActYTD$ BudgetYTD$ VarYTD$ ActYTDProfit BudgetYTDProfit VarYTDProfit


I have a table with the following headings:

Date State Product Prod Code Customer Cust Code Category Jan Feb Mar etc

State has the domain Vic, NSW, Qld
Category has the domain Sales GP

Question 1:

To the field Category, should I either

A: add to the domain actual sales and actual profit? I can past these into the table at the end of each month.

OR

B: set up a separate table for the actual sales and actual profit for the month?

I think A.

Question 2:

Instead of having a separate column for each month, should I either:

A: just have a heading Month and put the figures in that column

OR

B: Have the sales figures in separate columns for each month?

I think A

Question 3:

When I have set up my table correctly, and assuming the answers to my questions above are all "A", I am now unsure how to create a query which will give me the data for the report my boss wants.


Can someone please give me some help?

Thanks
Bon

View 4 Replies View Related

Lookup Multiple Figures Within A Column?

Feb 6, 2007

Hi

Is there a way you could look up multiple number in a query were the column will be a series of numbers/calculations?

E.g.

I would want to look for 13001,13002,13003, in a column where there would be calculation such

13001 * 246
13001 * 269
13002 849
etc
etc

Any help would be much appreciated?

Thanks

Andrew

View 1 Replies View Related

How Do I Create A Table That Contains Figures Calculated From Other Tables?

Feb 18, 2008

Hi,

I have two tables:

Table A provides total sales volume of the UK shoe market from 2000-2007.

Table B provides the sales volume of different shoe manufacturers from 2000-07.

I want a third table created, called Table C. This should look exactly like Table B but instead of sales volume it shows percentage sales that are calculated by using the figures in Table A and B (i.e. [sales volume from Table B/total sales volume from Table A] * 100).

Could someone point me in the right direction please (assuming that such a table can be created, based on a calculation of figures in other existing tables).

Thank you.

View 3 Replies View Related

How Many Words?

Mar 10, 2008

Hello

I have a database with some texts.
i)How can i compute how many word i have in each text?
ii) How can i find the frequency of some specifically words?

View 14 Replies View Related

Reserved Words

May 11, 2005

Hi all,

Does anyone have a definitive list of reserved words in Access? I understand certan words are reserved because they cause a problem when using vba. Is this correct?

It would make sense because I used to have a field called "Name" and my controls never worked correctly. I have since changed this and all is ok.

I have searched Access help files and this forum, but am unable to find a complete list other than the odd one like name, date, group etc.

Any help would be great. Many thanks.

View 3 Replies View Related

How To Use Search ON Two Words?

Jul 5, 2006

I am having trouble finding more targeted responses to my searches here on this forum- is there a way to search on two or maybe three grouped words?

I just get the results from each individual word, rather than the words together.

Thanks,
Dave

View 4 Replies View Related

Numbers To Words

Jul 7, 2006

Dear all,


If some have the code for converting amount to words in rupees, please help me...

Thanks in advance

Thanks:(

View 1 Replies View Related

Numbers To Words

Jul 7, 2006

Dear all,


If some have the code for converting amount to words in rupees, please help me...

Thanks in advance

Thanks:(

View 4 Replies View Related

Query First Two Words

Jun 29, 2007

Hi.
I have a list of 120000 entries, of companies in the UK. I need a query that will grab all the ones which have relevent similar names to others, like a duplicate query, but a bit more specific. For instance, First Choice UK, and First choice PLC are the same company, but have two entries. but I dont want to just scroll it would take ages. Is there a way to get a query to grab anything with matching words to other entries. Not necessarily the first two words, as my subject suggests, because the company name may not be two words, but whether there is any similarity in the name at all.

Thanks
alex

View 5 Replies View Related

Strange Words ???

Jan 31, 2005

Hello To All,

Has anyone seen the message in the attached file? I got this when I was trying to update the row source on the field after I had a problem with data in a report coming up inaccurate. I've been developing in MS Access for about 7 years now and have never seen this. Any explination for this would be greatly appreciated.

I am using MS Access 2000 on a Windows 98SE machine. :eek:

- Charles Williams

View 1 Replies View Related

Key Words In Query

May 20, 2005

I have a table and in there is the names of sandwiches, their prices and also extra info which includes whether the sandwich contains nuts etc. The actual text is "MAY CONTAIN NUTS" . When searching through the query i have made, there is the text - Extra info? for the query criteria or question and then i have to type in what i want to search or query for. Is it possible that i can just use the words 'nuts' when i search and then it will bring up sandwiches which contain the text "MAY CONTAIN NUTS" in the extra info field so that when querying it is much faster to find the sandwiches which contain nuts? If you can do this then please help.
Thanks, jon.

View 14 Replies View Related

History Words

Jan 9, 2006

Hi,

In MS Access, form

I want make like this:

1st time:
Company Name: Automobile
2nd time:
Company Name: AuID

If I type A, then it will show out the word, but the cursor is till After A, because if we type another character which will change another word
3rd time:
Company Name: Automobile

4th time:
Company Name: Au[I]ID[I]

What is the keyword to search about it in the forum?
Anyway, does anybody know how to solve it?
Please let me know, thanks.

View 1 Replies View Related

Numbers Instead Of Words!

Jan 14, 2006

I'm trying to pull fields from a table into a form - and have had other problems with this.....

What is textual information in one table. ie. the table I am trying to acces from a drop down will only appear as numbers instead of the text entry in the original - I have triend the bound colums but these are correct?

I've had this problem at a couple of points - it seems to be formatting the forms as numbers and pulling in the record numbers rather than the textual information. The bound colums are pointed at the textual entries but I can not change the formatting in the forms etc to text rather than number... In the drop down it shows the text entries but will not allow them to be saved when updated?

View 3 Replies View Related

Amount In Words

Feb 3, 2005

I have a script file of FoxPro 2.6 which is use to read amount field and write it in words.
Can any body convert in VBA Script.
I manualy write amount in words.
Thankyou.
Saira

View 3 Replies View Related

VB To Real Words

Jul 20, 2006

does anyone know where i can find a dictionary of what VB words mean in plain english so i can write and adjust them.

View 4 Replies View Related

List Of Reserve Words

Jul 19, 2005

Hello:

I searched to forum but came up empty.

Does anyone have a list of reserve words or know where I can get one?

Thanks,

Dwight

View 3 Replies View Related

Query To Group Like Words

Aug 7, 2006

Hello
I have a text box in my database that i would like to be about to search and group the text field if is had similar. for Example if I had a record like" called in puking" and "Up all night puking", I'd like the query to recognize "puking" (without me specifically telling it to look for "puking") and group them.

is this possible?

View 1 Replies View Related

Data Entry With More Than 255 Words

Feb 10, 2005

Hi, if i have to input a entry that have more than 255 words, but my table could only handle 255 words, so how do i go about it... pls advise, thanks...

View 3 Replies View Related

How To Translate Numbers To Words

May 29, 2005

Good day everyone!

I'm designing a system in which I have two fields
Amount in Numbers and Amount in Words

I want to be able to type in the amount in numbers and the amount in words should appear automatically.

I need the code and in wich event should I put it.

This is an urgent request. any help will be very much appreciated.

Thanks alot!

View 7 Replies View Related

[Trick] Amount In Words

Mar 3, 2005

Make Table Name: tblnum




Fields:

1.num (for number)(Feed 1 to 99)

2.inword (for In words)(Feed One to Ninty Nine)

Make Module



Public Function inwords(amount As Variant) As String




Dim intlac As Integer

Dim intTh As Integer

Dim intHun As Integer

Dim intNum As Integer

Dim strLac As String

Dim strTh As String

Dim strHun As String

Dim strNum As String

Dim intlen As Integer

intlen = Len(amount)

Select Case intlen

Case 7

intlac = Left(amount, 2)

intTh = Mid(amount, 3, 2)

intHun = Mid(amount, 5, 1)

intNum = Right(amount, 2)

strLac = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intlac) & " Lac"

If intTh = 0 Then

strTh = ""

Else

strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"

End If

If intHun = 0 Then

strHun = ""

Else

strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"

End If

If intNum = 0 Then

strNum = ""

Else

strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)

End If

Case 6

intlac = Left(amount, 1)

intTh = Mid(amount, 2, 2)

intHun = Mid(amount, 4, 1)

intNum = Right(amount, 2)

strLac = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intlac) & " Lac"

If intTh = 0 Then

strTh = ""

Else

strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"

End If

If intHun = 0 Then

strHun = ""

Else

strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"

End If

If intNum = 0 Then

strNum = ""

Else

strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)

End If

Case 5

intTh = CInt(Left(amount, 2))

intHun = CInt(Mid(amount, 3, 1))

intNum = CInt(Right(amount, 2))

strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"

If intHun = 0 Then

strHun = ""

Else

strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"

End If

If intNum = 0 Then

strNum = ""

Else

strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)

End If

Case 4

intTh = CInt(Left(amount, 1))

intHun = CInt(Mid(amount, 2, 1))

intNum = CInt(Right(amount, 2))

strTh = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intTh) & " Thousand"

If intHun = 0 Then

strHun = ""

Else

strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"

End If

If intNum = 0 Then

strNum = ""

Else

strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)

End If

Case 3

intHun = CInt(Left(amount, 1))

intNum = CInt(Right(amount, 2))

strHun = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intHun) & " Hundred"

If intNum = 0 Then

strNum = ""

Else

strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)

End If

Case 2

intNum = CInt(Right(amount, 2))

strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)

Case 1

intNum = CInt(Right(amount, 2))

strNum = DLookup("[inword]", "tblNum", "[tblNum]![num]=" & intNum)

End Select

inwords = strLac & " " & strTh & " " & strHun & " " & strNum & " Only"

End Function
Result = inwords(Number TextBox)

View 2 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved