Amount in word in MS. Access 2000 (VBA)

Hi Experts,

I have code which is the result will display e.g amount = 100 then it will covert to word "One Hundred" without the "Only". How am i going to display to "One Hundred Only". Thanks.


Option Compare Database
'Public crApplication As CRAXDRT.Application
'Public pbCR As CRAXDRT.Report
'Public crReport As CRAXDRT.Report
'Public crReportObject As CRAXDRT.ReportObjects
'Public crDBFieldDef As CRAXDRT.DatabaseFieldDefinition
'Public crDBFieldDefs As CRAXDRT.DatabaseFieldDefinitions
'Public crSortField As CRAXDRT.SortField
'Public crSortFields As CRAXDRT.SortFields
'Public crSQL, crFromDate, crToDate As String
'Public X As CRAXDRT.CRLegendPosition
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)
MyNumber = ""
End If
Count = Count + 1

Select Case Dollars
Case ""
Dollars = "" & " ONLY"
Case "One"
Dollars = "ONE" & " ONLY"
Case Else
Dollars = Dollars & " "
End Select

'Clean up cents.
Select Case Cents
Case ""
Cents = ""
Case "One"
Cents = "CENT ONE" & " ONLY"
Case Else
Cents = " CENTS " & Cents & " ONLY"
End Select

ConvertCurrencyToEnglish = Dollars & Cents
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))
' 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
' .. 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
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

Post your answer or comment

comments powered by Disqus
I am using Ms Access 2000, and have created a Query that uses the value held in a forms object as the criteria. This query when run from a button returns the results fine in a datasheet. However, when I try to use this query in VBA DAO to return a recordset that I can manipulate, I get prompted for the criteria! even though it is available on the form. Does anyone know how to fix this?

Many thanks


So far I have added a column in my table by ASP and vbscript which breaks down the sentences of the previous column and analyzes each letter ( that I've previously assigned a number to each letter a=1,b=2...). SO this extra column takes each word in the sentence of the previous column and shows the sum of the letters.
Quote: I walked the dog I=9
walked=100 ( a phony number )
the=50 ( a phony number )
dog=80 ( a phony number )

My question is how can I search a specific ( like 134 ) number and let the computer find every word in the fieldname I search and bring up the combination of letter or the different words that sum up to the value of the number ( like 134 )?

I think one important step is to somehow enter the values in an extra field in my MS Access 2000 database table, right? If that's the easiest way, does the Access have special functions for me to quickly go through this?

For example I have this result in ASP/vbscript:

	 fieldname: text_data
 bravyt bra alhy~ at hvmy~ wat har#`
Number of words: 7

Is there a way to insert the fieldname:?? of every single record ( one shot ) by using ASP into the database?

I have been asked if I can provide support to a select group who need to use MS Office 2003 for mission critical collaboration with external partners.

Unfortunately I have never seen MS Access 2003 and the IT group that supports the business I work for refuses to provide any support to anything beyond basic use of MS Access 2000. Before management fights to get MS Access 2003 installs some questions must be resolved:

(1) Are there any benefits to choosing MS Access 2003 over MS Access 2000?

(2) How well does MS Access 2003 work with MS Access 2000 files?

(3) Will MS Access 2000 files need to be converted before MS Access 2003 software can open them?

(4) Will MS Access 2003 allow for files to be formatted in an MS Access 2000 format?

(5) Have any MS Access 2000 features been depricated?

(6) Have any MS Access 2000 functionality been discontinued?

(7) Does the interface look the same, but with prettier colors and rounded edges?

(8) What is the data object model? (Has ADODB been replaced with something else?) (Is DAO Still supported?)

I remember having to edit a lot of MS Access '97 modules when MS Access 2000 was adopted because the object-subobject period delimiters [Forms].[frmMainMenu].property was discontinued and a strict use of exclamation delimiters [Forms]![frmMainMenu].property was implemented in VBA code modules.

Any help would be appreciated.


Does anyone know how to compact a ms access 2000 db in vba code. I want the database to compact evertime it is opened up.


I made a webhelp project with RoboHelp HTML X5.0.1. I want to integrate these helpfile in a MS Access project 2000 application.
These are my goals:
- if users click the help in the helpmenu they see the application helpfile
- users can use the "what's this" function
- in a field, users press F1 and see the context-sensitive helpfile

How to do this ?

Any help (links, VBA code, suggestions) would be much appreciated !


I would like to know if there is a way to control different properties of a chart in MS ACCESS from VBA such as the scale of the axis, Title, Legend, variables...




Anyone knows how to get the row number in Microsoft Access 2000?

In ORACLE, we can do like this:

SELECT ROWNUM, Field1, Field2, Field3 FROM Table1;

How do I write SQL statement in MS Access 2000? Thanks.



I've spent hours trying to find a solution without any luck!

*** Situation ***
I have a number of ODBC linked tables in an MS Access 2003 application. I want to be able to switch between source databases; i.e. between production and development databases, easily.

*** What works ***
Open Linked Table Manager;
Select the numerous linked tables;
Select Always Prompt For a New Location;
Click OK;
Provide the new DSN....

This works fine, but is rather manual, and does not provide for hands-free deployment. I'd like to be able to do this from code. All I need to do is change the Connect property on the tabledef, how ever, this property is read-only on existing tables! **** Microsoft!

The only method that turned up through hours of research is to drop the old table, then to connect the new table. This of course would be a nightmare, because then I would have to rebuild all of the security settings on the table, all the captions on each field, and all of the combo box lookups on some of the fields! Not gonna happen!

Anyone who can solve this riddle is a true guru!

Thank you,

Daniel Odulo

I can't use the Add in Manager in Access 2000. Anytime I click the install button I get a message like " missing USysRegInfo table". As a result I can't run any add in in Access.

The register button on the ActiveX Control dialogue box doesn't seem to work properly. I tried registering Microsoft Treeview Control after clicking comctl32.ocx but nothing appeared in the list of available controls. Does anybody know what is wrong and how I can solve this?

Hi all,

having some serious troubles with a MS Access 2000 project running on a Windows 2003 Server environment.

If I compile the project it doesn't produce a compiling-error. Everything seems to work fine. Creating a .mde file and running it on a Windows Server 2003 produces some serious errors. The project seems to be starting correctly but opening a form or simply trying to close the application causes MS Access to crash!

I tried the project on 2 different Windows Server 2003 environments, 1 having MS Access 2000 installed and 1 having only MS Access 2000 Runtime installed. Both are experiencing the same problems.

The following references are used in the project:
- Visual Basic For Applications
- Microsoft Access 9.0 Object Library
- Microsoft ActiveX Data Objects 2.6 Library
- Microsoft DAO 3.6 Object Library
- Microsoft Jet and Replication Objects 2.6 Library
- OLE Automation
- Microsoft Office 9.0 Object Library
- Microsoft ADO Ext. 2.8 for DLL and Security

I tried the following solutions to solve the problem:
* Removing MS Access completely and reinstalling it.
* Compiling and creating a .mde on the Windows 2003 Server which has the full version of MS Access 2000 installed.
* Using JetComp to repair the database.
* Creating a new MS Access 2000 application and copy all Forms/Modules into it.

Nothing helped so far..

The same project is running correctly on Windows 98, ME, 2000, 2000 Server and XP Pro/Home. Both Windows 2003 Servers are fully updated, including updates regarding MS Access.


I've been having some problems with sharing an MS Access 2000 database across a network.

The database is setup to open in shared mode and has been tried both with and without record locking, with the same issue occurring.

The main users are:
User 1 (Administrator) - MS Access 2000 Full Version (WinXP Professional)
User 2 - Access 2000 Runtime + Access 97 (Win XP Professional)
User 3 - Access 2000 Runtime + Access 97 (Win XP Professional)

The problem is occurring when User 3 opens the database. For some reason it opens in exclusive mode and locks out Users 1 & 2. This issue only happens with User 3, and does not happen with User 2 who has the same software setup.

The database was previously an Access 97 database (no problems) and has been converted to Access 2000, this share issue has been happening ever since the conversion.

Any ideas as to how this shared issue could be resolved?

BTW - I am familiar with FE/BE setups. This database is accessed by all from a network drive and not setup as FE/BE. This was never an issue, as shared access worked fine when the database was in Access 97 - the problem has only been since converting to Access 2000. I do not have access to the PC of User 2, and User 2 does not have any PC knowledge to install a FE, which means I would need to create an install package (which I'd rather not do). Are there any ways to resolve the shared access issue without changing to FE/BE?

I encountered a problem in MS Access 2000. When I needed to replace '#' with letter 'c' there was a problem in first the 'find' and then the 'replace'. Is there any other way to do this?

Hi can anyone help me. I am running MS Access 2000 (part of office 2000 proffessional). When i select the form tab to view forms, the program closes with no error messages. This also happens from the first main page in the program when I select an item from a drop down list that is linked to other forms. I have tried copying the database to a blank one, but again when trying to export it, as soon as i hit the form tab the program closes. Any suggestions ?

Hi there..

Yesterday I imported a form created under MS Access 2002(OS WinXP) to a mdb-project(*.mdb) that runs on an external network under MS Access 2000.
When I open the form in MS Access 2000 I get error 2427 "No data...".
The strange thing is that I just tested it again in both versions of access and only v2000 gives the mentioned error.

Here's the code. The line that causes the error I pointed out with ">>":

	Public Sub CostField_OnChange()
Dim Cost1, Cost2, Cost3, Cost4, Cost5

>> Cost1 = Me!Text39
Cost2 = Me!Text44
Cost3 = Me!Text47
Cost4 = Me!Text50
Cost5 = Me!Text53

Me!Tekst70 = Cost1 + Cost2 + Cost3 + Cost4 + Cost5
Me!Tekst71 = (Me!Tekst70 / 100) * Me!Text54
Me!Tekst72 = Me!Tekst70 + Me!Tekst71

End Sub

Private Sub Text39_Exit(Cancel As Integer)
End Sub

Private Sub Text54_LostFocus()

Me!Tekst71 = (Me!Text54 / 100) * Me!Tekst70
Me!Tekst72 = Me!Tekst70 + Me!Tekst71

End Sub

I've also tried to replace the "!" by "." but that didn't help either.
Who knows how to solve this strange problem? Tnx.

I have the (.frm) code for the forms that were created with VB5. How do I use this code to create forms in MS Access 2000?

Any ideas how I can convert an MS Access 2000 Report to a reasonably professional looking web page. I've tried using the Export as HTML facility however all the report formatting was lost.

Thanks in advance,


I have a text box in the report footer which displays the sum total of the amount in figures. I have also created another text box in the report footer to display the amount in words like 'three hundred', 'six hundred seventy' etc. My attempts failed to achieve the result for the word display. Is it possible in any way?

I have created a report which is having a calculated field(amount) and a text box.

I want to set "spellout Amount" (Amount in words)on the text box based on calculated field.

how can I create/set like above? please anybody give me some suggestions.....

Thank you in advance

I am controlling an MS Excel spreadsheet from MS Access using VBA. I want to change a column from text format to Date format. To change the format is simple, but it does not activate the fields until they are double clicked on. When I try to programme this it does not work. I have tried PasteSpecial and TextToColumns, but neither are working.
Please can someone help?

Hi Guys,

I'm using MS Access 2000 Premium (SR-1) as front-end and MySQL v 3.23.54 as backend. I created a form that will list existing record from the db, a user can choose a record and open that record on a specific form i'd used docmd.openform on this one. But everytime the code executes a run-time error code 13 pops up. What could i be doing wrong? the field and parameter are of the same data type (integer). Thanks

hi Dear
Please help me
how can i open a PDF Document in my MS-Access Program?

by regard

Hi all,

How do I capture an error message from SQL server in an MS Access form?

I have a trigger on a table in sql server 2005, the user is only allowed to update columns if the locked column has not been set to true.

I'm getting an error message back from Sql server when I try to update the form, but it's a bit to cryptic, so i need to capture the error and write my own handler.

I've tried Before and After update events, but they don't seem to capture the sql server odbc error being returned.

Anyone know how to handle this?


Hi all,

I need Accounting program with ms access with VBA code (open source)
prepare (journals, trial balance,balance sheet,Cash flow)


Anyone knows how to export data structures from MS Access 2000? I want to export field name, field type, field size, remarks from the structure. No records are required! Does it possible? Please help!


Is it possible to scan documents directly in to MS Access and store them in a field in a table please??

If so how??

Thanks for your help

Not finding an answer? Try a Google search.