Solved

Format currency, percentage and dates using vba

Posted on 2009-05-13
5
649 Views
Last Modified: 2012-08-14
Hi,
Is there a way to format fields into currency, percentage and dates using vba?  I have a table that have 3 fields:
Cost, Percentage and Date of Purchase.
right now, those fields are showing just numbers but i would like to format them as mentioned above.  Table name is called MainTable.  thanks for the help.
0
Comment
Question by:karinos57
  • 3
  • 2
5 Comments
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 24376576
Sounds like you haven't designed your table with the correct data types for the fields.
Correct this, and the data will display correctly with the default format of your Windows.

/gustav
0
 

Author Comment

by:karinos57
ID: 24376644
gustav,
I am importing excel files into my database using vba.  so i lose the format i setup for the table everytime i import them.  this is big project i am working on now and i am trying to automate everthing.  thanks.
0
 
LVL 49

Expert Comment

by:Gustav Brock
ID: 24376765
How could we know? But that explains.

If so, create a simple select query where you use the imported table as source and wrap the fields with CDate, CCur and CDbl:

Select
  CDate([YourDateField]) AS YourDate,
  CCur([YourCurrencyField]) As YourAmount,
  <.. etc.>
From
  tblYourImportTable

Percentage is nothing more that a decimal number.

Then use this query as source for your further tasks.

/gustav
0
 

Author Comment

by:karinos57
ID: 24377150
How can i wrap the fields with Cdate and CCur etc?  i am getting error
0
 
LVL 49

Accepted Solution

by:
Gustav Brock earned 250 total points
ID: 24378543
Hard to tell without the data. But Null values (empty fields) could be the cause.
CVDate accepts Null. Or use Nz to replace Null with default values:

Select
  CVDate([YourDateField]) AS YourDate,
  CCur(Nz([YourCurrencyField], 0)) As YourAmount,
  <.. etc.>
From
  tblYourImportTable

/gustav
0

Featured Post

Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

When you are entering numbers in a speadsheet, and don't remember what 6×7 is, you just type “=6*7" instead. It works in every cell! This is not so in Access. To enter the elusive 42 in a text box, you have to find a calculator, and then copy the re…
Deploying a Microsoft Access application in a Citrix environment is not difficult but takes a few steps. However, Citrix system people are often of little help, as they typically know next to nothing about Access. The script provided here will take …
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

932 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now