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.
karinos57Asked:
Who is Participating?

Improve company productivity with a Business Account.Sign Up

x
Advertise Here
 
Gustav BrockConnect With a Mentor CIOCommented:
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.