If this is your first visit, be sure to check out the FAQ by clicking the link above. You may have to register before you can post: click the register link above to proceed. To start viewing messages, select the forum that you want to visit from the selection below. |
|
|
Thread Tools | Display Modes |
#1
|
|||
|
|||
Field Data Type for Linked Tables
I have linked a MS Excel worksheet as a table in my Access database. I have
a field that contains numbers and letters. I have formatted the cells in Excel as text, but when it is linked in Access its data type is number, which causes the fields with numbers and letters to be displayed as #Num! in my Access table and form. I can't change the data type in Access. When I attempt to I get the message "Microsoft Office Access can't save property changes for linked tables." Is there any way to get around this issue? Thanks for your help! |
#2
|
|||
|
|||
Field Data Type for Linked Tables
One way is to put a dummy record as first row so that there are letters where
there are a mixture in the column. -- KARL DEWEY Build a little - Test a little "Brian" wrote: I have linked a MS Excel worksheet as a table in my Access database. I have a field that contains numbers and letters. I have formatted the cells in Excel as text, but when it is linked in Access its data type is number, which causes the fields with numbers and letters to be displayed as #Num! in my Access table and form. I can't change the data type in Access. When I attempt to I get the message "Microsoft Office Access can't save property changes for linked tables." Is there any way to get around this issue? Thanks for your help! |
#3
|
|||
|
|||
Field Data Type for Linked Tables
Karl,
I inserted a dummy record with letters only and the data type still appears as "Number". "KARL DEWEY" wrote: One way is to put a dummy record as first row so that there are letters where there are a mixture in the column. -- KARL DEWEY Build a little - Test a little "Brian" wrote: I have linked a MS Excel worksheet as a table in my Access database. I have a field that contains numbers and letters. I have formatted the cells in Excel as text, but when it is linked in Access its data type is number, which causes the fields with numbers and letters to be displayed as #Num! in my Access table and form. I can't change the data type in Access. When I attempt to I get the message "Microsoft Office Access can't save property changes for linked tables." Is there any way to get around this issue? Thanks for your help! |
#4
|
|||
|
|||
Field Data Type for Linked Tables
You have to get rid of the link and re-establish it.
-- KARL DEWEY Build a little - Test a little "Brian" wrote: Karl, I inserted a dummy record with letters only and the data type still appears as "Number". "KARL DEWEY" wrote: One way is to put a dummy record as first row so that there are letters where there are a mixture in the column. -- KARL DEWEY Build a little - Test a little "Brian" wrote: I have linked a MS Excel worksheet as a table in my Access database. I have a field that contains numbers and letters. I have formatted the cells in Excel as text, but when it is linked in Access its data type is number, which causes the fields with numbers and letters to be displayed as #Num! in my Access table and form. I can't change the data type in Access. When I attempt to I get the message "Microsoft Office Access can't save property changes for linked tables." Is there any way to get around this issue? Thanks for your help! |
#5
|
|||
|
|||
Field Data Type for Linked Tables
I had deleted the link and re-created it, but it wasn't working, so I added
multiple records with text and numbers. When I link it now it shows the data type as Text and the dummy records appear correctly, but the records with only numbers in that field appear as #Num!. "KARL DEWEY" wrote: You have to get rid of the link and re-establish it. -- KARL DEWEY Build a little - Test a little "Brian" wrote: Karl, I inserted a dummy record with letters only and the data type still appears as "Number". "KARL DEWEY" wrote: One way is to put a dummy record as first row so that there are letters where there are a mixture in the column. -- KARL DEWEY Build a little - Test a little "Brian" wrote: I have linked a MS Excel worksheet as a table in my Access database. I have a field that contains numbers and letters. I have formatted the cells in Excel as text, but when it is linked in Access its data type is number, which causes the fields with numbers and letters to be displayed as #Num! in my Access table and form. I can't change the data type in Access. When I attempt to I get the message "Microsoft Office Access can't save property changes for linked tables." Is there any way to get around this issue? Thanks for your help! |
#6
|
|||
|
|||
Field Data Type for Linked Tables
Post sample data.
-- KARL DEWEY Build a little - Test a little "Brian" wrote: I had deleted the link and re-created it, but it wasn't working, so I added multiple records with text and numbers. When I link it now it shows the data type as Text and the dummy records appear correctly, but the records with only numbers in that field appear as #Num!. "KARL DEWEY" wrote: You have to get rid of the link and re-establish it. -- KARL DEWEY Build a little - Test a little "Brian" wrote: Karl, I inserted a dummy record with letters only and the data type still appears as "Number". "KARL DEWEY" wrote: One way is to put a dummy record as first row so that there are letters where there are a mixture in the column. -- KARL DEWEY Build a little - Test a little "Brian" wrote: I have linked a MS Excel worksheet as a table in my Access database. I have a field that contains numbers and letters. I have formatted the cells in Excel as text, but when it is linked in Access its data type is number, which causes the fields with numbers and letters to be displayed as #Num! in my Access table and form. I can't change the data type in Access. When I attempt to I get the message "Microsoft Office Access can't save property changes for linked tables." Is there any way to get around this issue? Thanks for your help! |
#7
|
|||
|
|||
Field Data Type for Linked Tables
Here is what is in the Excel spreadsheet
10 Total10A 11 Total10A 12 Total10A 13 Total10A 14 Total10A 15 Total10A 16 Total10A 17 Total10A 18 Total10A 19 Total10A 20 Total10A 21 Total10A 22 Total10A 23 Total10A 24 Total10A 25 Total10A 26 Total10A 27 Total10A 28 Total10A 29 Total10A 30 Total10A 31 Total10A 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 This is what it shows up as in Access 10 Total10A 11 Total10A 12 Total10A 13 Total10A 14 Total10A 15 Total10A 16 Total10A 17 Total10A 18 Total10A 19 Total10A 20 Total10A 21 Total10A 22 Total10A 23 Total10A 24 Total10A 25 Total10A 26 Total10A 27 Total10A 28 Total10A 29 Total10A 30 Total10A 31 Total10A #Num! #Num! #Num! #Num! #Num! #Num! #Num! #Num! #Num! #Num! "KARL DEWEY" wrote: Post sample data. -- KARL DEWEY Build a little - Test a little "Brian" wrote: I had deleted the link and re-created it, but it wasn't working, so I added multiple records with text and numbers. When I link it now it shows the data type as Text and the dummy records appear correctly, but the records with only numbers in that field appear as #Num!. "KARL DEWEY" wrote: You have to get rid of the link and re-establish it. -- KARL DEWEY Build a little - Test a little "Brian" wrote: Karl, I inserted a dummy record with letters only and the data type still appears as "Number". "KARL DEWEY" wrote: One way is to put a dummy record as first row so that there are letters where there are a mixture in the column. -- KARL DEWEY Build a little - Test a little "Brian" wrote: I have linked a MS Excel worksheet as a table in my Access database. I have a field that contains numbers and letters. I have formatted the cells in Excel as text, but when it is linked in Access its data type is number, which causes the fields with numbers and letters to be displayed as #Num! in my Access table and form. I can't change the data type in Access. When I attempt to I get the message "Microsoft Office Access can't save property changes for linked tables." Is there any way to get around this issue? Thanks for your help! |
#8
|
|||
|
|||
Field Data Type for Linked Tables
I do not get the error.
One way would be to create the table, open in datasheet view, copy the Excel data, and paste append. -- KARL DEWEY Build a little - Test a little "Brian" wrote: Here is what is in the Excel spreadsheet 10 Total10A 11 Total10A 12 Total10A 13 Total10A 14 Total10A 15 Total10A 16 Total10A 17 Total10A 18 Total10A 19 Total10A 20 Total10A 21 Total10A 22 Total10A 23 Total10A 24 Total10A 25 Total10A 26 Total10A 27 Total10A 28 Total10A 29 Total10A 30 Total10A 31 Total10A 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 This is what it shows up as in Access 10 Total10A 11 Total10A 12 Total10A 13 Total10A 14 Total10A 15 Total10A 16 Total10A 17 Total10A 18 Total10A 19 Total10A 20 Total10A 21 Total10A 22 Total10A 23 Total10A 24 Total10A 25 Total10A 26 Total10A 27 Total10A 28 Total10A 29 Total10A 30 Total10A 31 Total10A #Num! #Num! #Num! #Num! #Num! #Num! #Num! #Num! #Num! #Num! "KARL DEWEY" wrote: Post sample data. -- KARL DEWEY Build a little - Test a little "Brian" wrote: I had deleted the link and re-created it, but it wasn't working, so I added multiple records with text and numbers. When I link it now it shows the data type as Text and the dummy records appear correctly, but the records with only numbers in that field appear as #Num!. "KARL DEWEY" wrote: You have to get rid of the link and re-establish it. -- KARL DEWEY Build a little - Test a little "Brian" wrote: Karl, I inserted a dummy record with letters only and the data type still appears as "Number". "KARL DEWEY" wrote: One way is to put a dummy record as first row so that there are letters where there are a mixture in the column. -- KARL DEWEY Build a little - Test a little "Brian" wrote: I have linked a MS Excel worksheet as a table in my Access database. I have a field that contains numbers and letters. I have formatted the cells in Excel as text, but when it is linked in Access its data type is number, which causes the fields with numbers and letters to be displayed as #Num! in my Access table and form. I can't change the data type in Access. When I attempt to I get the message "Microsoft Office Access can't save property changes for linked tables." Is there any way to get around this issue? Thanks for your help! |
#9
|
|||
|
|||
Field Data Type for Linked Tables
Got it
Thanks Karl "KARL DEWEY" wrote: I do not get the error. One way would be to create the table, open in datasheet view, copy the Excel data, and paste append. -- KARL DEWEY Build a little - Test a little "Brian" wrote: Here is what is in the Excel spreadsheet 10 Total10A 11 Total10A 12 Total10A 13 Total10A 14 Total10A 15 Total10A 16 Total10A 17 Total10A 18 Total10A 19 Total10A 20 Total10A 21 Total10A 22 Total10A 23 Total10A 24 Total10A 25 Total10A 26 Total10A 27 Total10A 28 Total10A 29 Total10A 30 Total10A 31 Total10A 0 0 0 0 0 0 0 0 0 0 0 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 1 This is what it shows up as in Access 10 Total10A 11 Total10A 12 Total10A 13 Total10A 14 Total10A 15 Total10A 16 Total10A 17 Total10A 18 Total10A 19 Total10A 20 Total10A 21 Total10A 22 Total10A 23 Total10A 24 Total10A 25 Total10A 26 Total10A 27 Total10A 28 Total10A 29 Total10A 30 Total10A 31 Total10A #Num! #Num! #Num! #Num! #Num! #Num! #Num! #Num! #Num! #Num! "KARL DEWEY" wrote: Post sample data. -- KARL DEWEY Build a little - Test a little "Brian" wrote: I had deleted the link and re-created it, but it wasn't working, so I added multiple records with text and numbers. When I link it now it shows the data type as Text and the dummy records appear correctly, but the records with only numbers in that field appear as #Num!. "KARL DEWEY" wrote: You have to get rid of the link and re-establish it. -- KARL DEWEY Build a little - Test a little "Brian" wrote: Karl, I inserted a dummy record with letters only and the data type still appears as "Number". "KARL DEWEY" wrote: One way is to put a dummy record as first row so that there are letters where there are a mixture in the column. -- KARL DEWEY Build a little - Test a little "Brian" wrote: I have linked a MS Excel worksheet as a table in my Access database. I have a field that contains numbers and letters. I have formatted the cells in Excel as text, but when it is linked in Access its data type is number, which causes the fields with numbers and letters to be displayed as #Num! in my Access table and form. I can't change the data type in Access. When I attempt to I get the message "Microsoft Office Access can't save property changes for linked tables." Is there any way to get around this issue? Thanks for your help! |
Thread Tools | |
Display Modes | |
|
|