Hybrid – Cheque Amount in Words Example

David Meego - Click for blog homepageThis is a reposting of an article I originally wrote on my Developing for Dynamics GP blog.

One of the Report Writer functions added to v7.00 onwards (see Using the built-in Report Writer Functions) was the RW_ConvertToWordsAndNumbers() function to convert a currency amount into words.  After the code was added, it was realised that Report Writer calculated fields of return type string are limited to 80 characters.  This means that if your amount in words is longer than 80 characters, only the first 80 characters will be returned and the rest will be truncated and lost.

This example using the “Check with Stub on Top” report was created to provide a method of using Visual Basic for Applications (VBA) to overcome this 80 character limitation.  The code uses the Continuum Integration Library to execute Dexterity sanScript code and also uses the Dynamic User Object Store (DUOS) to allow the Dexterity code to return its results to VBA. The DUOS is used because the DUOS table (SY90000) is easily visible to Dexterity as the SY_User_Object_Store table and to VBA as the DUOS objects.

The VBA code creates a Collection and creates 3 blank DUOS records to store 3 string properties. Then the code uses pass through Dexterity to call the RW_ConvertToWordsAndNumbers() function and then uses the RW_ParseString() function to split the returned result into 3 lines of 50 characters which are then stored into the SY_User_Object_Store table.  When the code returns to VBA, it reads the DUOS records and displays the results to the report and finally removes the records in the DUOS.

NOTE: This customisation uses a method of executing Dexterity sanScript code from VBA which is unsupported by Microsoft.

Example code for v8.0, v9.0 & v10.0 is attached at the bottom of the article.

Please see the “Installation Instructions.txt” file in each version’s archive for more information.

Related Posts:

05-Jul-2010: Added Related Posts.

Cheque Amount in Words Example.zip

This article was originally posted on the Developing for Dynamics GP Blog and has been reposted on http://www.winthropdc.com/blog.

20 thoughts on “Hybrid – Cheque Amount in Words Example

  1. Recently, our customer complain the vendor check name is too long and not able to fix into the check.
    We have created 2 textbox at the check. We use VBA to measure the vendor check name length. If it is too long, we move the remaining name into 2nd text box.
    But, when user print the report directly to the printer without viewing. The VBA code is not execute. On the other hand, the VBA code will execute when user viewing the report then only print the report. We are using Check with Stub on Top.
    May i know is it the VBA only execute when viewing the report?


  2. hi,
    in what part of the vba code where I can add asterisks before and after the amount in words so that it would look like this "**One thousand dollars only**"


  3. The VBA code uses 3 lines to return the amount in words to the report.  If you want asterisks only at the beginning and end you will need to check if the second or third line is used before adding the asterisks:
    If Trim(PrintObject.Properties(“String 2”)) = “” Then   String1LASTF1 = “**” + CStr(PrintObject.Properties(“String 1”)) + “**”   String2LASTF1 = “”   String3LASTF1 = “”ElseIf Trim(PrintObject.Properties(“String 3”)) = “” Then   String1LASTF1 = “**” + CStr(PrintObject.Properties(“String 1”))   String2LASTF1 = CStr(PrintObject.Properties(“String 2”)) + “**”   String3LASTF1 = “”Else   String1LASTF1 = “**” + CStr(PrintObject.Properties(“String 1”))   String2LASTF1 = CStr(PrintObject.Properties(“String 2”))   String3LASTF1 = CStr(PrintObject.Properties(“String 3”)) + “**”End If
    If you want asterisks on each line, try this:
    String1LASTF1 = “**” + CStr(PrintObject.Properties(“String 1”)) + “**”String2LASTF1 = “**” + CStr(PrintObject.Properties(“String 2”)) + “**”String3LASTF1 = “**” + CStr(PrintObject.Properties(“String 3”)) + “**”


  4. i want to add "only" after the amount. for eg : six thousand dollars only…I used the cat operator . But seems it not working…can u help ..?


  5. Manoj
    If you are using VBA to get this working, you will need to add the "only" text from VBA.  The VBA code runs after the Report Writer has completed the section being printed. You cannot the results from VBA code in RW calculated fields.


  6. I had trouble applying to CheckWtihStubonTopandBottom.  Prints to screen great.  Prints only the header when sent directly to the printer.  The problem seems to related to a similar issue in Article ID : 884601.


  7. Hi Rick
    Without seeing your code I cannot say if it is related.  I would suggest that you move any code that does not need to be repeated over and over into the Report_Start() and Report_End() events.  Whether that be opening and closing an ADO connection or creating and destroying DUOS objects.
    Note: If running GP 2010, you can use the new RW function which will handle the 80 character limit.


  8. Modified report check with stub on top. But I cannot print more than two checks at a time. It is going one line up in every page.


  9. Hi Manoj
    Page size is normally controlled by the printer driver and the paper size described by the driver.
    You could also try adjusting your Header and Footer sizes to decrease the size per page. Also you can adjust the Records per Report body value on the additional header (if used).


  10. Hello everyone. The VBA script does work but my challenge is that it gives me run time debugg error and  it wont print mean while in some cases it will print properly.
    In the scale of 10 it will give run time debugg error 8 times.
    Please how do i resolve this issue.
    Thank you


Please post feedback or comments

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s

This site uses Akismet to reduce spam. Learn how your comment data is processed.