Solved

Format currency, percentage and dates using vba

Posted on 2009-05-13
5
651 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

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
When you see single cell contains number and text, and you have to get any date out of it seems like cracking our heads.
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…
In Microsoft Access, learn how to use Dlookup and other domain aggregate functions and one method of specifying a string value within a string. Specify the first argument, which is the expression to be returned: Specify the second argument, which …

773 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