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
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
375 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
  • 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

Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

Question has a verified solution.

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

In a multiple monitor setup, if you don't want to use AutoCenter to position your popup forms, you have a problem: where will they appear?  Sometimes you may have an additional problem: where the devil did they go?  If you last had a popup form open…
Overview: This article:       (a) explains one principle method to cross-reference invoice items in Quickbooks®       (b) explores the reasons one might need to cross-reference invoice items       (c) provides a sample process for creating a M…
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, learn how to “cascade” or have the displayed data of one combo control depend upon what’s entered in another. Base the dependent combo on a query for its row source: Add a reference to the first combo on the form as criteria i…

856 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