Solved

Format currency, percentage and dates using vba

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

Why You Should Analyze Threat Actor TTPs

After years of analyzing threat actor behavior, it’s become clear that at any given time there are specific tactics, techniques, and procedures (TTPs) that are particularly prevalent. By analyzing and understanding these TTPs, you can dramatically enhance your security program.

Join & Write a Comment

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
Not long ago I saw a question in the VB Script forum that I thought would not take much time. You can read that question (Question ID  (http://www.experts-exchange.com/Programming/Languages/Visual_Basic/VB_Script/Q_28455246.html)28455246) Here (http…
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…
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

743 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

11 Experts available now in Live!

Get 1:1 Help Now