Linked Excel Field formatting Problem

[Karen]

I am using Access2003 and am trying to link an excel spreadsheet for use in a
drop down selector. I have one column (acct#) which contains a lot of
numbers, but needs to be a text field (some entries say "TBD" or have
hyphenated numbers). Access insists on keeping the column as a numeric field
and displays the non-numeric fields as "#Num!".

I have tried: formatting the cells as text before linking; putting dummy
text data in the first line (abc and 'abc ) before linking; and tried using
conversions in a query (CStr, Str, Str$ & Format).

I'm out of ideas, can anyone suggest a solution?

 

[Jeanette Cunningham]

Karen,
Use a query based on the excel spreadsheet as the row source for the combo.
Use a calculated field for the combo
Expr1: CStr([TheField])
See if that gives the format you want.

Jeanette Cunningham

 

[Karen]

I tried that, I think. The query I did was based on the linked spreadsheet
with a calculated field as you suggested. But all the data that showed in
the linked 'table' as "#Num!" still showed as "#Num!" in the query results,
rather than being the string data from the spreadsheet.

I almost found another workaround. I made all the acct numbers have a
non-numeric character in the spreadsheet (a preceeding space in most cases);
linked the spreadsheet thusly so that the field would be defined as Text in
Access; and then I removed all the added non-numeric characters from the
spreadsheet. It worked until I overwrote the spreadsheet with a copy (which
will be a daily occurance since the source file is restricted). When I
reopened the database, the field was redefined as a number again and I'm back
to the original issue.

So I'm still at a loss.

-Karen

Karen,
Use a query based on the excel spreadsheet as the row source for the combo.
Use a calculated field for the combo
Expr1: CStr([TheField])
See if that gives the format you want.

Jeanette Cunningham

I am using Access2003 and am trying to link an excel spreadsheet for use in
a
drop down selector. I have one column (acct#) which contains a lot of
numbers, but needs to be a text field (some entries say "TBD" or have
hyphenated numbers). Access insists on keeping the column as a numeric
field
and displays the non-numeric fields as "#Num!".

I have tried: formatting the cells as text before linking; putting dummy
text data in the first line (abc and 'abc ) before linking; and tried
using
conversions in a query (CStr, Str, Str$ & Format).

I'm out of ideas, can anyone suggest a solution?

 

[Jeanette Cunningham]

Karen,
In my tests with an excel spreadsheet
I created a column with a general format.
Some of the entries were numbers as stored as text
Other entries were numbers stored in general format

When linking to the spreadsheet, all the numbers stored in general format
showed #Num! regardless of everything I tried.
Only the numbers stored as text were usable in access.

What did work for me, was to import that column into an access table into a
text type field.
It looks as though an import is the only way to go.
Each day before you run the import routine, you could empty the table that
receives the import.

Jeanette Cunningham

I tried that, I think. The query I did was based on the linked
spreadsheet
with a calculated field as you suggested. But all the data that showed in
the linked 'table' as "#Num!" still showed as "#Num!" in the query
results,
rather than being the string data from the spreadsheet.

I almost found another workaround. I made all the acct numbers have a
non-numeric character in the spreadsheet (a preceeding space in most
cases);
linked the spreadsheet thusly so that the field would be defined as Text
in
Access; and then I removed all the added non-numeric characters from the
spreadsheet. It worked until I overwrote the spreadsheet with a copy
(which
will be a daily occurance since the source file is restricted). When I
reopened the database, the field was redefined as a number again and I'm
back
to the original issue.

So I'm still at a loss.

-Karen

Karen,
Use a query based on the excel spreadsheet as the row source for the
combo.
Use a calculated field for the combo
Expr1: CStr([TheField])
See if that gives the format you want.

Jeanette Cunningham

I am using Access2003 and am trying to link an excel spreadsheet for use
in
a
drop down selector. I have one column (acct#) which contains a lot of
numbers, but needs to be a text field (some entries say "TBD" or have
hyphenated numbers). Access insists on keeping the column as a numeric
field
and displays the non-numeric fields as "#Num!".

I have tried: formatting the cells as text before linking; putting
dummy
text data in the first line (abc and 'abc ) before linking; and tried
using
conversions in a query (CStr, Str, Str$ & Format).

I'm out of ideas, can anyone suggest a solution?

 

[Karen]

I have had a similar result. I've even had it switch which items said
"#Num!" when I got the field to switch to being Text (numbers stored as
General then showed up that way).

Unfortunately, we just don't have someone available to do a daily import,
nor a login that it can be linked to as an trigger for an automated routine.

What I think will work is adding a column in the spreadsheet that
concatenates some string character with the account#, thereby insuring that
the incoming column is always defined as Text field. I can then strip out
the known, added text via a query. And the column can always be hidden for
the spreadsheet users.

I was just hoping that there was a less kludgy way of doing it.

Thanks anyway for keeping me thinking on it! And certainly if you come up
with another way, let me know.

-Karen

Karen,
In my tests with an excel spreadsheet
I created a column with a general format.
Some of the entries were numbers as stored as text
Other entries were numbers stored in general format

When linking to the spreadsheet, all the numbers stored in general format
showed #Num! regardless of everything I tried.
Only the numbers stored as text were usable in access.

What did work for me, was to import that column into an access table into a
text type field.
It looks as though an import is the only way to go.
Each day before you run the import routine, you could empty the table that
receives the import.

Jeanette Cunningham

I tried that, I think. The query I did was based on the linked
spreadsheet
with a calculated field as you suggested. But all the data that showed in
the linked 'table' as "#Num!" still showed as "#Num!" in the query
results,
rather than being the string data from the spreadsheet.

I almost found another workaround. I made all the acct numbers have a
non-numeric character in the spreadsheet (a preceeding space in most
cases);
linked the spreadsheet thusly so that the field would be defined as Text
in
Access; and then I removed all the added non-numeric characters from the
spreadsheet. It worked until I overwrote the spreadsheet with a copy
(which
will be a daily occurance since the source file is restricted). When I
reopened the database, the field was redefined as a number again and I'm
back
to the original issue.

So I'm still at a loss.

-Karen

Karen,
Use a query based on the excel spreadsheet as the row source for the
combo.
Use a calculated field for the combo
Expr1: CStr([TheField])
See if that gives the format you want.

Jeanette Cunningham


I am using Access2003 and am trying to link an excel spreadsheet for use
in
a
drop down selector. I have one column (acct#) which contains a lot of
numbers, but needs to be a text field (some entries say "TBD" or have
hyphenated numbers). Access insists on keeping the column as a numeric
field
and displays the non-numeric fields as "#Num!".

I have tried: formatting the cells as text before linking; putting
dummy
text data in the first line (abc and 'abc ) before linking; and tried
using
conversions in a query (CStr, Str, Str$ & Format).

I'm out of ideas, can anyone suggest a solution?

 

[Matt]

Have you tried switching the cell format in Excel to "Text" and not General?
I have been working extensively on the same type of Excel to Access import
that you're talking about and have had great luck. Access doesn't recognize
a field as either or, so before you import it or link it change the cell
format to Text. If you are working with dates in this manner, then the
"DateValue" function works well for switching between the two programs.

I have had a similar result. I've even had it switch which items said
"#Num!" when I got the field to switch to being Text (numbers stored as
General then showed up that way).

Unfortunately, we just don't have someone available to do a daily import,
nor a login that it can be linked to as an trigger for an automated routine.

What I think will work is adding a column in the spreadsheet that
concatenates some string character with the account#, thereby insuring that
the incoming column is always defined as Text field. I can then strip out
the known, added text via a query. And the column can always be hidden for
the spreadsheet users.

I was just hoping that there was a less kludgy way of doing it.

Thanks anyway for keeping me thinking on it! And certainly if you come up
with another way, let me know.

-Karen

Karen,
In my tests with an excel spreadsheet
I created a column with a general format.
Some of the entries were numbers as stored as text
Other entries were numbers stored in general format

When linking to the spreadsheet, all the numbers stored in general format
showed #Num! regardless of everything I tried.
Only the numbers stored as text were usable in access.

What did work for me, was to import that column into an access table into a
text type field.
It looks as though an import is the only way to go.
Each day before you run the import routine, you could empty the table that
receives the import.

Jeanette Cunningham

I tried that, I think. The query I did was based on the linked
spreadsheet
with a calculated field as you suggested. But all the data that showed in
the linked 'table' as "#Num!" still showed as "#Num!" in the query
results,
rather than being the string data from the spreadsheet.

I almost found another workaround. I made all the acct numbers have a
non-numeric character in the spreadsheet (a preceeding space in most
cases);
linked the spreadsheet thusly so that the field would be defined as Text
in
Access; and then I removed all the added non-numeric characters from the
spreadsheet. It worked until I overwrote the spreadsheet with a copy
(which
will be a daily occurance since the source file is restricted). When I
reopened the database, the field was redefined as a number again and I'm
back
to the original issue.

So I'm still at a loss.

-Karen

:

Karen,
Use a query based on the excel spreadsheet as the row source for the
combo.
Use a calculated field for the combo
Expr1: CStr([TheField])
See if that gives the format you want.

Jeanette Cunningham


I am using Access2003 and am trying to link an excel spreadsheet for use
in
a
drop down selector. I have one column (acct#) which contains a lot of
numbers, but needs to be a text field (some entries say "TBD" or have
hyphenated numbers). Access insists on keeping the column as a numeric
field
and displays the non-numeric fields as "#Num!".

I have tried: formatting the cells as text before linking; putting
dummy
text data in the first line (abc and 'abc ) before linking; and tried
using
conversions in a query (CStr, Str, Str$ & Format).

I'm out of ideas, can anyone suggest a solution?

 

[Karen]

Yes, that was the first thing I tried. You must get it to the point that
the "number stored as text" error happens. For whatever reason, after
redefining the cell as text, I still had to go in and put a space/character
of somesort in each cell before it would do this. Thankfully, they stayed as
text after I removed the extra character.

There's also the trick is that Access seems to redefine the field depending
on what it sees in most of the cells in that column. Which potentially makes
it unreliable, especially considering that the folks responsible for the
spreadsheet aren't very interested in dealing with DBs and are likely to
forget these little 'quirks' that don't impact their use of the spreadsheet.

Thanks for the input.

-Karen

Have you tried switching the cell format in Excel to "Text" and not General?
I have been working extensively on the same type of Excel to Access import
that you're talking about and have had great luck. Access doesn't recognize
a field as either or, so before you import it or link it change the cell
format to Text. If you are working with dates in this manner, then the
"DateValue" function works well for switching between the two programs.

I have had a similar result. I've even had it switch which items said
"#Num!" when I got the field to switch to being Text (numbers stored as
General then showed up that way).

Unfortunately, we just don't have someone available to do a daily import,
nor a login that it can be linked to as an trigger for an automated routine.

What I think will work is adding a column in the spreadsheet that
concatenates some string character with the account#, thereby insuring that
the incoming column is always defined as Text field. I can then strip out
the known, added text via a query. And the column can always be hidden for
the spreadsheet users.

I was just hoping that there was a less kludgy way of doing it.

Thanks anyway for keeping me thinking on it! And certainly if you come up
with another way, let me know.

-Karen

Karen,
In my tests with an excel spreadsheet
I created a column with a general format.
Some of the entries were numbers as stored as text
Other entries were numbers stored in general format

When linking to the spreadsheet, all the numbers stored in general format
showed #Num! regardless of everything I tried.
Only the numbers stored as text were usable in access.

What did work for me, was to import that column into an access table into a
text type field.
It looks as though an import is the only way to go.
Each day before you run the import routine, you could empty the table that
receives the import.

Jeanette Cunningham



I tried that, I think. The query I did was based on the linked
spreadsheet
with a calculated field as you suggested. But all the data that showed in
the linked 'table' as "#Num!" still showed as "#Num!" in the query
results,
rather than being the string data from the spreadsheet.

I almost found another workaround. I made all the acct numbers have a
non-numeric character in the spreadsheet (a preceeding space in most
cases);
linked the spreadsheet thusly so that the field would be defined as Text
in
Access; and then I removed all the added non-numeric characters from the
spreadsheet. It worked until I overwrote the spreadsheet with a copy
(which
will be a daily occurance since the source file is restricted). When I
reopened the database, the field was redefined as a number again and I'm
back
to the original issue.

So I'm still at a loss.

-Karen

:

Karen,
Use a query based on the excel spreadsheet as the row source for the
combo.
Use a calculated field for the combo
Expr1: CStr([TheField])
See if that gives the format you want.

Jeanette Cunningham


I am using Access2003 and am trying to link an excel spreadsheet for use
in
a
drop down selector. I have one column (acct#) which contains a lot of
numbers, but needs to be a text field (some entries say "TBD" or have
hyphenated numbers). Access insists on keeping the column as a numeric
field
and displays the non-numeric fields as "#Num!".

I have tried: formatting the cells as text before linking; putting
dummy
text data in the first line (abc and 'abc ) before linking; and tried
using
conversions in a query (CStr, Str, Str$ & Format).

I'm out of ideas, can anyone suggest a solution?