Solved

how to format a field in a query

Posted on 2016-10-20
4
44 Views
Last Modified: 2016-10-21
I have a column that is created that has 3 characters long

As an example the query could return

001
010
123

I want to format the field so that only the digits appear, no leading 0's so my desire is to show:

1
10
123
0
Comment
Question by:seamus9909
  • 2
4 Comments
 
LVL 120

Expert Comment

by:Rey Obrero (Capricorn1)
ID: 41853180
try this

select val([nameOfField]) from tableX
0
 
LVL 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 500 total points
ID: 41853181
you can also use

select cint([nameOfField]) from tableX
0
 
LVL 19
ID: 41853182
turn on properties -- press Alt-Enter if it is not showing
then click in the column you want to format on the grid

in the Format property, type your format code -- or edit what is already there.

However, it could be that text is stored, not a number.  In that case, you can use a conversion function to turn it into a number. For instance:
MyNumber: CInt([MyTextFieldname])

Open in new window

Perhaps what you really want is to change the data type to a number?
0
 

Author Closing Comment

by:seamus9909
ID: 41853891
Thank you so Much
0

Featured Post

Ransomware: The New Cyber Threat & How to Stop It

This infographic explains ransomware, type of malware that blocks access to your files or your systems and holds them hostage until a ransom is paid. It also examines the different types of ransomware and explains what you can do to thwart this sinister online threat.  

Question has a verified solution.

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

Suggested Solutions

Experts-Exchange is a great place to come for help with solutions for your database issues, and many problems are resolved within minutes of being posted.  Others take a little more time and effort and often providing a sample database is very helpf…
A simple tool to export all objects of two Access files as text and compare it with Meld, a free diff tool.
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…
Using Microsoft Access, learn some simple rules for how to construct tables in a relational database. Split up all multi-value fields into single values: Split up fields that belong to other things into separate tables: Make sure that all record…

820 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