"Numeric->Text" Type conversion in "Make Table" Query

[TDN]

Hi,

been looking hours for a sloution but without success.
maybe anyone can help:

task:
a "Make Table" query shall create a table with mostly text
fields, but the original table contains several numeric
fields (that access 97 sometimes imports as text fields!).

the "make table" query uses the command
SELECT Str(COUNTRY) as COUNTRY;

problem:
the type conversion works fine if COUNTRY is a numeric
field. but unfortunately, access sometimes sets the field
type to "text" during import with the result that the Str
()-fuction produces an error and ignores al data.

QUESTION:
is there a way to create a new field of the type "Text" in
a make table query no matter what the source field type is?


been looking on the web for 3 hours now, without success.
it cant be that noone ever encountered this issue...

any comments really appreciated.

thanks

TDN

 

[Jeff Boyce]

Another approach would be to create the table structure as you know it is,
and run Append queries rather than Make Table queries. You would use the
CLng() (or CCur(), CDbl(), etc.) function to force a field in the query into
the data type you know it is.

Note -- if your imported "numeric" field contained any non-numeric
characters, the above-mentioned "Convert" functions would fail. You may
also wish to look into the Val() function.

 

[TDN]

thanks jeff,

but the problem is that i don't know whether the source
fields are numeric or text type. access seems to be
indecisive during import. I need "text" but one time it
uses text, next month the field's numeric. if the field
was always numeric, then SELECT Str("old") AS "new"; would
be the solution. But it ain't... *sigh*


i want to give my users one button to click on that's
smoothing out this issue before processing the newly
imported table.

any other ideas?

TDN

 

[Jeff Boyce]

Not sure I understand. Are you saying that you have a data field that you
wish to import, but you don't know what data type it (will be) is? In that
case, one approach would be to always import it as text. But that sounds
like more "scrubbing" is needed. Can you describe the types of data you are
getting?

 

[TDN]

Hi,

When i import a Table i need a field "SiteCode" as "text"
type for lookup queries but access sometimes imports
as "numeric(double)".
(in these cases, the lookup queries don't work (data type
mismatch)


brief explanation:
I have an Excel97 table with several columns. I import
this table manually into Access97. during import i CANNOT
influencee field types - that option is greyed out.

for example, one time Access97 has imported the
field "SiteCode" as "Numeric(Double)", another time the
same field is imported as "Text(255)"

There is a 1:n-join applied on the field "SiteCode". the
joined field containing the lookup values for "SiteCode"
is of "text" type.

=> I need "SiteCode" of type "Text" after every import.
Otherwise the Lookup query won't work.


how can i tell Access97 to automatically adjust the
doubtful field to "text(6)"? (using a new query, using a
vba-script, anything?)

alternatively: How do i make sure that the field is ALWAYS
imported as "text"


is there anything i can do except adjusting the doubtful
fields manually?


(note: in SQL i'd use "ALTER TABLE ... CHANGE ..." but
that's not working in Access97. i've tried "ALTER
TABLE ... ADD/DROP ..." but it's no acceptable solution
because i have to check/change nine fields. for the
largest table this takes upt to ten minutes to execute!)

 

[Jeff Boyce]

Don't use a Make Table -- Access gets to decide on field types.

If you use an import table, consider that only a "staging area". Write
queries that force a conversion to the proper data types, and append the
data into your set of "final" tables.

 

[John Spencer (MVP)]

You might try the CStr conversion function. You will get an error on that if
Country is a null, but that can be handled. If country can be null then I would
try IIF(Country is null, Null, CStr(Country)). If Country is never null, then
CStr(Country) should work.

SELECT IIF(Country is null, Null, CStr(Country)), OtherFields
FROM ...