Convert Text Column to Number

I have a datatable with a column of numeric data. The column was originally assigned a type "number" but was converted to "text" by a user.

I need to change back to "number" but I get an error message that says: Quote: Microsoft Access encountered errors while converting the data. The contents of field in 443 record(s) were deleted. Do you want to proceed anyway? I tried adding a new field and running an update query which assigns the value of the original column to the new column using the CInt, Cdbl and Val functions. I get the same result. Some of the values (443 exactly) are not convereted. As these were originally numbers, I do not understand what is happening.

Unfortunately, this is messing up a number of reports and queries which I need to be able to run. Can anyone help me correct this issue? Thanks.

Sponsored Links:

How to change text columns to numeric type.

I tried selecting the column and then

Format > Cells > Number > Number

and also this:

Format > Cells > Number > Currency

but not working. I'm guessing it won't show numeric totals until I get the column changed from text to numeric. There is a little green flag that affords a menu with "Convert to Number" but it only changes one row at a time. How do I change all the rows at once?

I need to convert my text data to a number but when I convert using the VALUE function or use "format cells" to the numbers category, I loose the leading zeros. I need to keep them for sorting purposes.

What formula do I use?

I have a table that is imported from a file using code I didn't write. The Table Structure uses text fields, but the values are numbers(Commodity prices). Is there a way that I can convert the fields to number or do some sort of make table query to put the values into number fields? I am trying to do a number of calculations and think that the reason for the errors is that the values in the fields are text and not numbers. Any help is greatly appreciated.

I have a text field I need to convert to numbers. There are both empty and non empty fields. I then need to show the numbers like this "1.234" and a "*" in the empty fields.

I have tried to convert the fields with Cint, but I get an #Error in the empty fields.

Is there any way to solve this??

Thanks in advance..

Hi all,
I have a problem with a table ive linked to / selected contents into a copy of said table. The field type of one of my columns changes to text (shows as text on the link dispite being a number). Ive tried converting said column to a number column using the following command but to no avail.

ObjConn.Execute "ALTER TABLE tblALLOC ALTER COLUMN PAL01 Number (Long Integer)"

Any suggestions as to what i need to do?
Many Thanks

I'm currently having trouble with the Cdate(expression). I don't quite understand how to apply it.

I have a table that contains a field titled LD with the following date format: "2011-01-12 11:00", but in text format.

Does anyone know How to eliminate the time and convert the text date into a number date so that I can apply conditional formatting to it?



I wondered if someone could help.

I am using the Val () Function to convert a text field to number within a query which still gives me a text output.

P.S I do not have permissions to change within table as using Access as a front end to SQL via link Tables.


I have a column of dates in text format (example: 4/15/1994) that I want to format to date numbers (34439 in the example given). Table name is "Issue" column name is "InterestDates".
Any help will be appreciated.

I have been working on this query the whole day and if my head was not shaved, I could be bold from pulling it by now. If anyone can kindly help me please find my query below;

CellNumber: IIf(CLng([dbo_AgreementDetail]![MSISDN])>100000000,CLng([dbo_AgreementDetail]![MSISDN]),IIf(CLng([dbo_Agreement]![OldMSISDN])="",CLng(1),CLng(Right(Replace(Trim(Left([dbo_Agreement]![OldMSISDN],10))," ",""),9))))

I am using MS Access 2007, we recently changed our Dbase structure. both fields dbo_Agreement.OldMSISDN and dbo_AgreementDetail.MSISDN are Text fields, so I'm trying to convert the results into long int.

Some of the values for the numbers are blank. that is why I want to replace them with the value (1) so that I could use it in subsequent queries. like put a criteria: Where CellNumber >1

at the moment, (with the above query) I can only get the values from the dbo_Agreement.OldMSISDN but the second part of the IIF returns (#Error) even where the values in dbo_AgreementDetail.MSISDN are blank or cellnumber exist.

Any help offered will be greatly appreciated.

Phil, Cape Town.

Im building a database for a company that has a Client # for each Client and a Job # to go with the client

Client# 200 - Job# 002 '2nd job for that client

200-002 '2nd job for that client
200-003 '3rd job for that client

When a new job comes in for that client(200) they will get a new Job# 003

Is there a way a Numeric field can handle the 0's in the front? or will I need to use Text format?

Right now I am trying to use text format
But I cant figure out a good way to convert the Text to Numeric, Increase the Number by 1, then convert it back to text with the 0's.

Hope my question makes sense

As the title says, i have an identifier field which combines two codes eg. AAAAAA/1234.

I am using a query to extract the number part, however when i try to link to another query it says 'type mismatch'.

I assume that this is because the main query is based on a table where [ShipID] is numeric and the extracted data is based upon a underlying table where [PackageID] is a text field.

Is there any way to get round this?

Many thanks

I have a field called "test scores" which is currently set as Text with entries like "89%"

How would I go about changing these to just the number so I can create field to manipulate the data?

I tried changing from text to number but it changed them all to 1.

There are several thousand entries so deleting the "%" manually would like to be avoided



I have two tables Atable and BTable and both have a field called AcctNo. In Atable, it is type of Number and in BTable, it is type of Text. When I do a join with these two tables

select * from Atable, BTable where Atable.AcctNo=Btable.AcctNo

I get error saying "type mismatch".

My question here is: How can I convert a type from number to string or string to number in my sql statement?



Date of Birth (DOB) field etc. in one program are text - how do I make another file with the same data into number fields for Date of Birth field etc? When I copy data to file that has number fields the 09252004 is changed to 9252004. Can I get reports with the correct Date of Birth in them by moving data from text file to number file?

There is data entered monthly in file and formula has been set up for January, February etc as ---quarter: Int(([month]-1)/3)+1. I would like formula for the fiscal year for April to be counted as month 1, May - month 2, June as month 3, July as month 4, August as month 5, Sept as month 6, October as month 7, Nov as month 8, Dec as month 9, Jan as month 10, Feb as month 11 and March as month 12.

Thank you

I am having trouble in converting text 02Jan2011 to date 02/01/2011.
Can anyone tell me how I can conveert a column in a table from text to date. please?

I am very new at Access and have a BIG problem. We use a access database to manage ID cards. The old company (we fired) created a access database with dates as actual "text" instead of "date" fields. Our new program uses access database but does not except the text string for a date. I need to convert the date as a text string to a date field. When I change the field in the table from text to date, it deletes all the entries. That is very bad as there are over 2,000 entries.

The format of the current text date string is 6 characters and displays as this: 020612

02 = Month
06 = Day
12 = Year

I need to convert this text to a date field with proper format of 02/02/2012. How do I do this in access without having to manually change every date before changing the field from "text" to "date" and erasing all current entries. I am very new at this and would really appreciate any help!

I imported a file to Access in text format. I now need to convert several fields to date format xx/xx/xxxx.

I'm not familiar with how to write the expression needed to use CDate in an Update Query. Any suggestions?

I am going absolutely awol trying to find the formula... I had it in a query, but the db corrupted before I saved the query (Access 2010--no surprise)... I am trying to convert a text field to a time value. I had used something like the following and it worked great! Now I can't recall how to write it.


I know that is wrong, but it looks somewhat similar. TimeValue, Format and Convert do not work for some reason.

Thank you...

Hi All - I would like the user to be able to import a month name (April for example) and then have access convert that name to a number (4) so that I can search for records that were opened before the month entered. I have been using:

MonthName(Month([CompletionDate])) = [Enter Month]