473,387 Members | 1,540 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,387 software developers and data experts.

Convert text to number data type in Access 2007

Hi Guys,

I'm working on converting a large table with a field that has numeric values in text data type. When I do the conversion in Access automatically some records don't get converted. The reason why is because the field that holds values in the table has the following data ( 9.22337E+18 ) now this happened because the data was imported from Excel file. The challenge now is to convert that type of number to a whole number such as: 9223370000000000000
Is there any function that takes care of this type of scenario?

Thanks!

JoeKid09
Nov 3 '09 #1

✓ answered by Delerna

a number colmn in excel in being imported into access as text?

I believe that when you import from excel into access, the datatype is calulated from the values in the first row that is not selected as a field names row.

so

If you ensure that, before doing the import, the first 'data' row in excel contains data that can only be interpreted by access with the datatype you intend then that should work.

At least, it has for me in the past when I have suffered simmilar issues importing from excel

2 9475
Delerna
1,134 Expert 1GB
a number colmn in excel in being imported into access as text?

I believe that when you import from excel into access, the datatype is calulated from the values in the first row that is not selected as a field names row.

so

If you ensure that, before doing the import, the first 'data' row in excel contains data that can only be interpreted by access with the datatype you intend then that should work.

At least, it has for me in the past when I have suffered simmilar issues importing from excel
Nov 3 '09 #2
NeoPa
32,556 Expert Mod 16PB
Alternatively, using the same method outlined by Delerna, you can use the Text data type to import it, as all other data types can quite happily be held as text. Convert it back to whatever you need from within Access.
Nov 3 '09 #3

Sign in to post your reply or Sign up for a free account.

Similar topics

4
by: aevans1108 | last post by:
expanding this message to microsoft.public.dotnet.xml Greetings Please direct me to the right group if this is an inappropriate place to post this question. Thanks. I want to format a...
3
by: ET | last post by:
I don't know whats the problem, but after I added functions to first verify, then relink linked tables if not found, now I can't convert that database to MDE format. I can split the database, but...
1
by: Jamad | last post by:
I want to extract some information in a text file that is not tab, comma and space separated. The format of the text file is actually unknown. The problem now is, i want to extract like three lines...
16
by: Neil | last post by:
I posted a few days ago that it seems to me that the Access 2007 rich text feature does not support: a) full text justification; b) programmatic manipulation. I was hoping that someone might...
2
by: Ch Pravin | last post by:
Hi All: I am having the following xml which i need to convert to excel using xslt. Please help me out. Afghanistan.xml <?xml version="1.0" encoding="utf-16"?> <Languages...
5
by: sweeet_addiction16 | last post by:
im coding in c....i need to accept an integer value(decimal) and then after converting it into hexadecimal value i need to write it into a file.i do not need to print it..so using fprintf along...
2
budigila
by: budigila | last post by:
Hiya peeps, Okies, I have been trying to work this out for a while now to no avail... I am a beginner to this whole coding thing but have made great strides in my project. Basically what I am...
2
by: FAQ server | last post by:
----------------------------------------------------------------------- FAQ Topic - Why does 1+1 equal 11? or How do I convert a string to a number?...
9
by: sillyr | last post by:
Hi - I use Access 2007. I have a two data fields that I would like to add together in a query and then have the query sum the data for the entire table. The problem is that one of the data fields...
0
by: taylorcarr | last post by:
A Canon printer is a smart device known for being advanced, efficient, and reliable. It is designed for home, office, and hybrid workspace use and can also be used for a variety of purposes. However,...
0
by: Charles Arthur | last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
0
by: aa123db | last post by:
Variable and constants Use var or let for variables and const fror constants. Var foo ='bar'; Let foo ='bar';const baz ='bar'; Functions function $name$ ($parameters$) { } ...
0
by: ryjfgjl | last post by:
In our work, we often receive Excel tables with data in the same format. If we want to analyze these data, it can be difficult to analyze them because the data is spread across multiple Excel files...
1
by: nemocccc | last post by:
hello, everyone, I want to develop a software for my android phone for daily needs, any suggestions?
0
by: Hystou | last post by:
There are some requirements for setting up RAID: 1. The motherboard and BIOS support RAID configuration. 2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
0
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
0
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can...
0
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers,...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.