Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 663
  • Last Modified:

Format currency, percentage and dates using vba

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
karinos57
Asked:
karinos57
  • 3
  • 2
1 Solution
 
Gustav BrockCIOCommented:
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
 
karinos57Author Commented:
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
 
Gustav BrockCIOCommented:
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
 
karinos57Author Commented:
How can i wrap the fields with Cdate and CCur etc?  i am getting error
0
 
Gustav BrockCIOCommented:
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

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 3
  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now