Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Access 2010 - convert memo field to number

Posted on 2011-10-19
12
Medium Priority
?
1,210 Views
Last Modified: 2012-05-12
I need to convert a memo field to a number.  I want to be able to sort a list by length which should have been a number field to begin with.  If the field is called length what would be the syntax in a query to convert all the values to number?

Many thanks
0
Comment
Question by:JohnMac328
  • 5
  • 4
  • 2
  • +1
12 Comments
 
LVL 9

Expert Comment

by:sshah254
ID: 36995841
Did you try cint, clng, cstr ... any of these?

SS
0
 

Author Comment

by:JohnMac328
ID: 36995874
I read something about the val function but did not know how to run it in a query.  I am not familiar with the examples you stated
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 36995903
Val
0
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 16

Expert Comment

by:Sheils
ID: 36995932
If all the values are numeric just change the format of the field to numbers. If you need deecimals make the datatype single or double. Otherwise long integer will do. I don't see any need for query or code
0
 

Author Comment

by:JohnMac328
ID: 36995944
Ok - it is a memo field and when I try to change it to number it destroys the data - that is why I need something to convert them to numbers
0
 
LVL 26

Expert Comment

by:jerryb30
ID: 36996058
Oh,  did not refresh.
In a query, just use val([Length]) to change the field to regard it as a number.
But you mention its length. Can you givve an example of the conversion you want? val() will only work uop to the first non-numeric value.
0
 

Author Comment

by:JohnMac328
ID: 36996075
These are lengths of Videos so 105 is about the biggest it gets
0
 
LVL 16

Expert Comment

by:Sheils
ID: 36996089
Try this:

-Right click on the table in the navigation pane and select copy.
-The right click in a blank space in the pane and select paste.
-In the dialog box that opens select structure only
-Open the nex table in design view and change the memo field to text

Create a new query. Don't select any table. Open it in sql view and insert the following sql:

INSERT INTO [Copy Of TableName] SELECT tblEntity.*FROM TableName;
0
 

Author Comment

by:JohnMac328
ID: 36996125
Ok Copy Of TableName is the new table - tablename is the origianl table - what is tblEntity?
0
 
LVL 16

Expert Comment

by:Sheils
ID: 36996127
I just tried converting a field from memo to numbers and did not loose any data. In my case all the values were numeric. Are there non numeric values in your field. If so what are they.
0
 
LVL 16

Accepted Solution

by:
Sheils earned 2000 total points
ID: 36996136
Sorry should be:

INSERT INTO [Copy Of TableName] SELECT *FROM TableName;
0
 

Author Closing Comment

by:JohnMac328
ID: 36996159
Great thanks - just numbers and it looks good.
0

Featured Post

VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

Question has a verified solution.

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

We live in a world of interfaces like the one in the title picture. VBA also allows to use interfaces which offers a lot of possibilities. This article describes how to use interfaces in VBA and how to work around their bugs.
Instead of error trapping or hard-coding for non-updateable fields when using QODBC, let VBA automatically disable them when forms open. This way, users can view but not change the data. Part 1 explained how to use schema tables to do this. Part 2 h…
What’s inside an Access Desktop Database. Will look at the basic interface, Navigation Pane (Database Container), Tables, Queries, Forms, Report, Macro’s, and VBA code.
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

564 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