ms access convert text field to number - ApplicationEssaywritercsClub

ms access convert text field to number

Stack Overflow



  1. Log In
    Sign Up

  2. current community


    • Stack Overflow

      help
      chat

    • Meta Stack Overflow

    your communities

    Sign up or log in to customize your list.

    more stack exchange communities

    company blog

    • Tour

      Start here for a quick overview of the site

    • Help Center

      Detailed answers to any questions you might have

    • Meta

      Discuss the workings and policies of this site

    • About Us

      Learn more about Stack Overflow the company

    • Business

      Learn more about hiring developers or posting ads with us

This site uses cookies to deliver our services and to show you relevant ads and job listings.
By using our site, you acknowledge that you have read and understand our Cookie Policy , Privacy Policy , and our Terms of Service .
Your use of Stack Overflow’s Products and Services, including the Stack Overflow Network, is subject to these policies and terms.

Join us in building a kind, collaborative learning community via our updated
Code of Conduct .

Join Stack Overflow to learn, share knowledge, and build your career.

Email Sign Up
Sign Up

or sign in with


Google

Facebook

convert text to number in ms access 2010 select statement

Ask Question


up vote
0
down vote

favorite

1

I need to run a query on a massive (650 MB) data table in MS Access 2010. All the data types for the fields are text, but some fields contain all numbers, even though the data type is text. I therefore have to convert from text to long int in order to select, for example, all the records who have a value greater than 10 in a specified field. But whenever I do this, I get a type mismatch error, presumably due to the comparison of a numerical value of a text field. Here is the SQL for the query which should select from fields that are all of the text data type.

SELECT [Organization legal name], [Number of Group Practice members], City, State
FROM massivetable
WHERE Clng([Number of Group Practice members])>10
AND State='CT'; 

I have tried to convert the datatype of the field to long integer in design view, but the operation fails with a message saying there is not enough memory to perform the operation.

Can anyone show me how to fix the code above so that it selects all the records while values greater than 10, without throwing a type conversion error?

sql ms-access
share | improve this question

asked Nov 8 ’13 at 2:36

CodeMed

3,32535142237

  • You may have to export all the data, get the database setup properly (either edit the existing database after removing all the previously exported data OR Create a NEW copy of the database) then import the data into the database to get everything back in place. I had to do it once before on a project and it saved the day. size of your database is larger than what I was working with though. good luck.
    –  tamak
    Nov 8 ’13 at 2:56

  • @tamak Do you have any solution to my next question? I am trying to shrink the table with a WHERE clause and INSERT INTO. Here is the link to the question: stackoverflow.com/questions/19850911/…
    –  CodeMed
    Nov 8 ’13 at 3:09

add a comment  | 

1 Answer
1

active

oldest

votes


up vote
3
down vote

accepted

Use IsNumeric function:

SELECT [Organization legal name], [Number of Group Practice members], City, State
FROM massivetable
WHERE Iif(IsNumeric([Number of Group Practice members]), Clng([Number of Group Practice members]), 0) >10
AND State='CT'; 

share | improve this answer

answered Nov 8 ’13 at 2:54

cha

9,47311225

  • +1 and credit for the answer. thank you.
    –  CodeMed
    Nov 8 ’13 at 2:58

  • Do you have any solution to my next question? You can read it at this link: stackoverflow.com/questions/19850911/…
    –  CodeMed
    Nov 8 ’13 at 3:08

add a comment  | 

Not the answer you’re looking for? Browse other questions tagged sql ms-access or ask your own question .

asked

4 years, 9 months ago

viewed

18,715 times

active

4 years, 9 months ago

Linked

0

insert into query without listing destination fields explicitly

Related

15

A select query selecting a select statement

590

‘IF’ in ‘SELECT’ statement – choose output value based on column values

6

Convert String to Date in MS Access Query

0

Access 2010 SELECT string

1

Creating a Temporary Incrementing Number in an MS Access Query

1

Access SQL – Convert Text to Number

3

Why does search condition fail on MS Access number field that is indexed?

0

Select a record with the largest sum of fields (Access 2010)

0

How do I fix this SQL SELECT statement for MS Access

2

Access: Combining multiple rows into one, but show text

Hot Network Questions

  • Is this meme about the former prime minister of India true?

  • How can I explain why my mechs don’t sink into the ground?

  • Standard behavior for direct initialization of unsigned short

  • Is a pig-mounted cavalry possible?

  • Why does Samsung include useless capacitors?

  • How can I adjust playing two keys an octave apart to something that I can reach with a spread of 8 keys?

  • How to Write an Eldritch Abomination?

  • Why didn’t the Spacecraft used for the Apollo 11 mission melt in the Earth’s Atmosphere?

  • Can two universities have the same name?

  • Why do ex-government employees have security clearances?

  • Kid throwing ice cream cone back to the vendor

  • How do I draw a box with holes?

  • Can I receive a package in Zurich airport while on transit?

  • Codegolf Rainbow : Sorting Colors with Reflection

  • What is the ubuntu-minimal package and do I need it?

  • Why is peer review so random?

  • Completion of measure spaces – uniqueness

  • TCP gender-changer

  • Why does there seem to be a lack of conservative comedy and comedy-news compared to liberal?

  • Who was the first female ruler to be given the suffix, "the great"?

  • S and P 500 Prediction

  • Who’s not welcome here?

  • Can I attack with the same weapon for normal attack and Two-Weapon Fighting in the same turn?

  • Is preparing for a lawsuit a valid reason to defer an exam?

more hot questions


question feed

lang-sql

Stack Overflow works best with JavaScript enabled

  • Overview  
    • What is Microsoft Access
  • Object Tips  
    • Tables
    • Queries
    • Forms
    • Reports
    • Macros
  • Quick Tips  
    • Numbers
    • Queries
  • Version Tips  
    • Access 97
    • Access 2007-2010
  • Resources  
    • Tips via email
    • Share your secrets
    • Learn VBA
    • Access to SQL
    • Whats New!

How to Convert Numbers to Text in Microsoft Access

by Hollie Miller

(UK)


Microsoft Access is a database management tool.

The data within a database must be stored in appropriate fields depending on the data type i.e. numerical values, alphabetic characters or Boolean values. There are restrictions on how certain data types can be modified and manipulated so sometimes it might be more useful to have numbers stored in a text field than a number field For example if a program you want to export the data to requires this format, or perhaps you need to make some changes to the data that aren’t permitted in a number field, such as adding non numerical characters.

This guide will cover the method to convert number fields to text fields in Microsoft Access by using an update query. To do this, the Cstr function is used. This converts a value to a string.

This is a 2 step process:

Note: As you will be dealing with an update query that will permanently change the results in your table, you may make to make a copy of your table(s) just in case you make a mistake.

Step 1 – Select the records to change

Open the database that contains the records you want to change.

Go to Design > Macros & Code > Query Design

This should open the query designer and the Show Table window

Go to the tab titled Tables

Select any tables that contain the records you want to change. Click Add then Close.

A window will appear showing all the fields in each table you selected. Double click on the fields you want to update, in this case the number fields you want to change to text.

The selected fields are shown in the Field row in the design grid.

Go to the Design tab and click on Run.

Check the results are the fields you want to change and remove any unwanted results by selecting them and pressing the delete key.
If you need to add any fields, you can drag additional fields onto the design grid.

Step 2 – Update the records.

Go to the tab titled Design under the group called Query Type click on Update

This has changed a select query to an update query. A new row titled Update To is now shown in the design grid.

Now, to change a numeric value to a text string, you can enter:

TextField:Cstr([NumberField]) in the Update To box.

Here, TextField represents a name you wish to give the text field, and NumberField is the name of the numeric field that you wish to convert to text. An example would be: a numeric field is called Quantity, in this case the command to convert the numbers representing a Quantity, to text would be:

QuantityText:Cstr([Quantity])

Go to the Design tab and in the Results group click on Run.

A warning message will appear, to run the query and update the results click Yes.

Update queries can be very useful in converting between data types and there are many different update functions that can be used in them, it’s not limited to just converting from numeric values to text. There are no restrictions on converting numbers to text, however there are some restrictions on some data type conversions and you should check this before using other update functions.

Bio – Hollie Miller
Hollie provides Microsoft Access Training for Acuity Training. In her spare time she loves to go to the gym and dress making.

Comments for How to Convert Numbers to Text in Microsoft Access

Average Rating starstarstarstarstar

Click here to add your own comments

Aug 05, 2017
Rating
starstarstarstarstar
Need help about Access

by: Sultan


Hi,

I am a student of Access. Trying to do one project but don’t understand how to do this can you please help me.

Question:

My Database Accounts

Table 1:

TblBsAcs

[BsAc] number (2 digits)

[MainAcTitle] short txt

Table 2:

TblAcsChart

[BsAc] having many to one relationship with TblBsAcs

[Acode] need here automaticall generates Accounts e.i.: BsAc = 10 then Acode will be 10001

[AcTitle] short txt

if you please help then I will be grateful to you.

You can send an email :

[email protected]



Click here to add your own comments

Join in and write your own page! It’s easy to do. How? Simply click here to return to Microsoft Access Tips to Share.



SHARE

Tweet

POPULAR PAGES

privacy policy




Quantcast

Tags: , , , ,