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.

Post your answer or comment

comments powered by Disqus
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]



My table has the field defined as number, and in form i input a value. I have an OnChange subroutine that tries to look at this field, and if the value > 0 to turn on a check box. however it does not work.
'if family leave hours are present turn on chkFamLeaveFlag
Private Sub txtFamLeaveHrs_Change()
Dim dblValue As Double
dblValue = 0
dblValue = Me.txtFamLeaveHrs.Value

If dblValue > 0 Then
Me.chkFamLeave = True
Me.txtTestValue = dblValue
End If
Me.lblTestInSub.Visible = True
End Sub
is there something about the table def being numeric, and the text box always being character and having to convert the value of the text box to numbers before doing a compare...i don't get an error this way so not sure why the test result of dblValue is always zero even in the text box shows an input of 5 for example.

Dim ClientIDNum As Variant

ClientIDNum = Me.ClientID
ClientIDNum = ClientIDNum + 1
MsgBox "ClientIDNum is" & ClientIDNum

I'm getting a "Datatype mismatch" error. I know there is something I need to with the me.clientid, but I'm not for sure what. Can anyone help me out?

Could I change the table data type automatically using macro . for example I convert text data to number data by using (Mid) f682515 => 682515 but the data type still text.

Please help .
thank you

When I export my MS Access 2003 Report to excel, using the Analyze with Excel, the UPC text column i.e. 0023456789 00 88 converts to a number field and has the zeros truncated and some are even in scientific notation.

How do I retain the text format and not allow for the number conversion?



I got some numbers stored as text in a query. I need to do some simple math on those number. How can I convert text to number automatically, without going into the table design?

Thank you

Not finding an answer? Try a Google search.