asked on

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.

Patrick Matthews

Use the Format function:

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

Open in new window

f19l

ASKER

I have added your code to my existing SQL code but it does not seem to work.

datAdrenaline

Give this a shot ...

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

f19l

ASKER

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

ASKER CERTIFIED SOLUTION

datAdrenaline

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial