[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

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

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

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
f19l
Asked:
f19l
  • 2
  • 2
1 Solution
 
Patrick MatthewsCommented:
Use the Format function:

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

Open in new window

0
 
f19lAuthor Commented:
I have added your code to my existing SQL code but it does not seem to work.
0
 
datAdrenalineCommented:
Give this a shot ...

SELECT MyColumn, Format(CCur(Nz(MyColumn,"0"), "#,##0")) AS Formatted
FROM MyTable
0
 
f19lAuthor Commented:
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
 
datAdrenalineCommented:
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: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

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.

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