473,325 Members | 2,792 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,325 software developers and data experts.

Number format change to text

Hi,

I hope someone can give me some solutions to these problems I faced.

I imported some data to Excel. Then in Access, I table-linked to this file. I have two fields which in Excel is in number format but in Access, these fields property is text and the values in these fields become "#Num!". Others number format fields were okay, only these two fields (or particular columns?).

But if I linked directly to the datasource, it seems the properties remains as number format. However, i do not want to linked directly to the main datasource as I only want specific fields (about 20 out of 100 over fields). The Access file size become very big after refresh.

Another issue with direct linked to the main datasource is I can't see all the fields although I can see all fields in Excel when I do a database query.
Any ideas on this?

Thank you in advance for your help.
Jul 19 '07 #1
1 4564
JConsulting
603 Expert 512MB
Hi,

I hope someone can give me some solutions to these problems I faced.

I imported some data to Excel. Then in Access, I table-linked to this file. I have two fields which in Excel is in number format but in Access, these fields property is text and the values in these fields become "#Num!". Others number format fields were okay, only these two fields (or particular columns?).

But if I linked directly to the datasource, it seems the properties remains as number format. However, i do not want to linked directly to the main datasource as I only want specific fields (about 20 out of 100 over fields). The Access file size become very big after refresh.

Another issue with direct linked to the main datasource is I can't see all the fields although I can see all fields in Excel when I do a database query.
Any ideas on this?

Thank you in advance for your help.
Use this function to convert your .xls file to a .csv file prior to importing. Because it's now a comma delimited file, you can use TransferText to import the data while creating an import Specification. You can set the field types and names there, as well as saving the Specification as a name that you can use in code again and again.

Expand|Select|Wrap|Line Numbers
  1. Function ConvertXLS(strXLSFile As String) As String
  2. ' This function converts a Excel file to a CSV file
  3.     Dim xlApp As Object
  4.     Dim xlwb As Object
  5.     Dim strCSVFile As String
  6.     strCSVFile = Left(strXLSFile, Len(strXLSFile) - 3) & "csv"
  7.     Set xlApp = CreateObject("Excel.Application")
  8.     Set xlwb = xlApp.Workbooks.Open(strXLSFile, True)
  9.     xlApp.Visible = False
  10.     xlwb.SaveAs strCSVFile, xlCSV
  11.     xlwb.Close (False)
  12.     xlApp.Quit
  13.     Set xlApp = Nothing
  14.     ConvertXLS = strCSVFile
  15. End Function
  16.  
J
Aug 1 '07 #2

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

Similar topics

6
by: simina | last post by:
Hi... I have a form with 4 number fields: phone area, phone number, cell area, cell number. I did a function that checks the "number" issue for all 4 fields in the same time (because the code is...
2
by: John S | last post by:
I have created an event handler that is trying to format each number as it is typed. Example If the first char is 1 = output is $1.00 second char is 3 = output is $13.00 next is 5 = $135.00...
0
by: codeproblems | last post by:
============= XML content ================== <?xml version="1.0"?> <cdb> <spill> <ctx_volume>142300000</ctx_volume> <date>19670318</date> <shipname>TORREY CANYON</shipname> </spill>...
4
by: Orchid | last post by:
How can I change a Date datatype to a Number datatype? For example, I want a date 10/31/2006 to show 1031 as Number datatype. But I don't want it becomes 39021. What formula should I use? ...
10
by: Dixie | last post by:
I am appending some new fields to a table in vba and when I append a number field with is a byte, it does not inherit any format. I want it to be the General Number format, but it is blank. I...
7
by: buddyr | last post by:
Hello, I am in microsoft access form - I want a field called serial number to have number display with 3 zeros before it. the serial number is always 5 digits. I have tried putting "00000000" in...
1
by: Brian | last post by:
Hello, I am new to Microsoft access 2007 and I have created a new database. There is a dollar about that I have to change from text to currency format. Also, there is a zip code that I have to...
4
by: grego9 | last post by:
I have two tables that are linked by a common field called 'reference'. i am running a query and want the query to post a text of "EXCHANGE OV CHANGE" against each record where a field called "ORIG...
29
by: kstevens | last post by:
I have a form with 12 unbound comboboxes whose query is determined from the combobox before it. Example: No2 box rowsource is set to the query and then requeried on the afterupdate of box1. Now...
0
by: DolphinDB | last post by:
Tired of spending countless mintues downsampling your data? Look no further! In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...

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.