Solved

How do I make the format of a text-type field match that of a number-type field in Access?

Posted on 2011-09-16
5
389 Views
Last Modified: 2012-05-12
Hello,

I have created a database where I load a spreadsheet full of information into a single table. One of the fields is called Dollar_Value and holds various amounts but the data type is text rather than number. When I run a query on this table the format of the Dollar_Value field is not like that of the number-type fields, "0,000". Other than changing the data type how else can I change the format of the Dollar_Value field so that it matches that of the number-type fields?

Thanks.
0
Comment
Question by:f19l
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
5 Comments
 
LVL 92

Expert Comment

by:Patrick Matthews
ID: 36549298
Use the Format function:

SELECT MyColumn, Format(MyColumn, "#,##0") AS Formatted
FROM MyTable

Open in new window

0
 

Author Comment

by:f19l
ID: 36549328
I have added your code to my existing SQL code but it does not seem to work.
0
 
LVL 11

Expert Comment

by:datAdrenaline
ID: 36549572
Give this a shot ...

SELECT MyColumn, Format(CCur(Nz(MyColumn,"0"), "#,##0")) AS Formatted
FROM MyTable
0
 

Author Comment

by:f19l
ID: 36549681
Perhaps it will be easier if you see the SQL code, which I have pasted below and changed. The field that I am interested I have called PRINCIPAL. Below are a few line of results so you have a better understanding of what I am seeing. To reiterate, the PRINCIPAL field is currently text-type and without changing that I want to show the field results as "0,000" (number-type).

COB_DATE      CURRENCY      PRINCIPAL       REGIONAL_CENTRE      SOURCE
31/08/2011      GBP      555787       GLOBAL                           FC MONET
31/08/2011      GBP      3675560.71       GLOBAL                           FC MONET
31/08/2011      GBP      -3359462.49       GLOBAL                           FC MONET
31/08/2011      GBP      500000000       GLOBAL                           FC MONET
31/08/2011      GBP      190000000       GLOBAL                           FC MONET




SELECT [Qry_Table].COB_DATE, [Qry_Table].CURRENCY,[Qry_Table].PRINCIPAL, [Qry_Table].REGIONAL_CENTRE, [Qry_Table].SOURCE
FROM [Qry_Table]

Open in new window

0
 
LVL 11

Accepted Solution

by:
datAdrenaline earned 500 total points
ID: 36549741
This will display as you wish:

SELECT [Qry_Table].COB_DATE, [Qry_Table].CURRENCY, Format(CCur(Nz([Qry_Table].PRINCIPAL,"0")), "#,##0") As fPrincipal, [Qry_Table].REGIONAL_CENTRE, [Qry_Table].SOURCE
FROM [Qry_Table]

But ... if you want the column of the recordset returned by the Query to be of a numeric datatype (Currency to be specific), then you can do something like this ...

SELECT [Qry_Table].COB_DATE, [Qry_Table].CURRENCY, CCur(Nz([Qry_Table].PRINCIPAL,"0")) As fPrincipal, [Qry_Table].REGIONAL_CENTRE, [Qry_Table].SOURCE
FROM [Qry_Table]

Then use the Format property of the field in the Query object to set the display format of the fPrincipal column to the number format you wish to display.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

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.

Question has a verified solution.

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

In earlier versions of Windows (XP and before), you could drag a database to the taskbar, where it would appear as a taskbar icon to open that database.  This article shows how to recreate this functionality in Windows 7 through 10.
This article describes a method of delivering Word templates for use in merging Access data to Word documents, that requires no computer knowledge on the part of the recipient -- the templates are saved in table fields, and are extracted and install…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…

718 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