question regarding string/numeric

[webicky]

As part of Select - then make table query I am reading in the
following from a .csv file

SELECT [mytable].[Score]

Score SHOULD be number with a decimal 305.2. but has the value of *
in several places so Access converts to strings. I want to retain the
decimals and the stars should be blank.

What is the easiest way to do this?

 

[Jeff Boyce]

A common approach to dealing with ... dirty ... data is to import (or link
to) as is, then use queries to parse the data into your well-normalized,
more permanent Access table structure.

After all, if this is coming in via a .csv file, odds are fairly good the
data is NOT well-normalized. Don't just 'dump' it into Access. Take the
time to build a table structure that Access can really use.

Regards

Jeff Boyce
Microsoft Access MVP

--
Disclaimer: This author may have received products and services mentioned
in this post. Mention and/or description of a product or service herein
does not constitute endorsement thereof.

Any code or psuedocode included in this post is offered "as is", with no
guarantee as to suitability.

You can thank the FTC of the USA for making this disclaimer
possible/necessary.

 

[John Spencer]

Use an expression

Select IIF(IsNumeric([mytable].[Score]),CDbl([mytable].[Score]),Null) as Score
FROM SomeTable
....

Using Score as the alias for the calculation MIGHT cause an error. If it does
then change Score to MyScore or TheScore or ...

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

 

[webicky]

John: Thanks.

SELECT CDbl([mytable].[Score]) AS [Score] works though #Error appears
in the cells with *. This is trivial because the next query just
makes those blank so that is perfect.

And Jeff - I am linking and then building the access table strucutre
from the link. I find that process makes the data much more
manageable - and with large .csv files its absolutely necessary in
Access 03 due to size - with the added bonus of giving you the
permanent table at the end.

Use an expression

Select IIF(IsNumeric([mytable].[Score]),CDbl([mytable].[Score]),Null) as Score
FROM SomeTable
...

Using Score as the alias for the calculation MIGHT cause an error.  If it does
then change Score to MyScore or TheScore or ...

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

As part of Select - then make table query I am reading in the
following from a .csv file

SELECT [mytable].[Score]

Score SHOULD be number with adecimal305.2.  but has the value of *
in several places so Access converts to strings.  I want to retain the
decimals and the stars should be blank.

What is the easiest way to do this?- Hide quoted text -

- Show quoted text -

 

[John Spencer]

Well, I would rather not have #Error appear at all. It can cause problems
especially if your are nesting queries.

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

John: Thanks.

SELECT CDbl([mytable].[Score]) AS [Score] works though #Error appears
in the cells with *. This is trivial because the next query just
makes those blank so that is perfect.

And Jeff - I am linking and then building the access table strucutre
from the link. I find that process makes the data much more
manageable - and with large .csv files its absolutely necessary in
Access 03 due to size - with the added bonus of giving you the
permanent table at the end.

Use an expression

Select IIF(IsNumeric([mytable].[Score]),CDbl([mytable].[Score]),Null) as Score
FROM SomeTable
...

Using Score as the alias for the calculation MIGHT cause an error. If it does
then change Score to MyScore or TheScore or ...

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

As part of Select - then make table query I am reading in the
following from a .csv file
SELECT [mytable].[Score]
Score SHOULD be number with adecimal305.2. but has the value of *
in several places so Access converts to strings. I want to retain the
decimals and the stars should be blank.
What is the easiest way to do this?- Hide quoted text -

- Show quoted text -