unwanted concatenation when adding text data fields with numbers

  • Thread starter Thread starter Gregorio
  • Start date Start date
G

Gregorio

Access 2000, XP, 2003
Win XP Pro

I have a variety of data in a single Text field of a table, some numbers,
some dates. I get undesired concatenation when adding the entries in the
field. Can this be corrected through some variation of the Format function
at report display-time? In past I've appended to temp tables with a number
Data Type in order to add but it seems unnecessarily complicated.

Thanks in advance - G

Details:
With the Data Type of the source table set to text, I can store both numbers
and dates in that field, however when it's time to display entries added
together they concatenate, rather than add.

With the Data Type of the source table set to Number, I can't store the
dates but the math works.
 
A field should store one type of data. Text can't be "added".

Sounds like your table structure is wrong if you are storing dates and
number sin the same field.
 
Hi Rick

In SQL Server data stores multiple data types in a tes field is common,
though maybe bad practice. Queries then use the SQL 92 Convert function
while delivering result sets. Access prior to ver. 2003 supports SQL-89 from
what I know, and so doesn't support the Convert function.

I enjoy the simplicity of Access (my clients benefit) but we may need to
move on to SQL Server. Does anyone know of a quick display-time fix to this
problem?

G
 
I'm not sure what the underlying logic is here, by which I mean I'm not sure
what you expect the result of adding numbers and dates to be - it rather
depends on what the numbers represent, I suppose. Just by way of
illustration, you can do something like this in a query ...

SELECT Table3.TestText,
IIf(IsDate([TestText]),CDbl(CDate([TestText])),CDbl([TestText])) AS TestExp
FROM Table3;

With test values of "10", "52", and "1 Jan 2005", the result of this query
is 10, 52, and 38353. If I was to sum these three values, I would then get
38415, which is the numeric value of the date 4 March 2005. In other words,
the query, when summed, is the equivalent of adding 10 + 52 days to 1 Jan
2005. This might make sense if the numbers do, in fact, represent days, or
complete nonsense if they represent something else.
 
Brendan
Worked instantly. Um, you're my new best friend. My only question is: why
have I never seen this one before?
- Greg


Brendan Reynolds said:
I'm not sure what the underlying logic is here, by which I mean I'm not
sure what you expect the result of adding numbers and dates to be - it
rather depends on what the numbers represent, I suppose. Just by way of
illustration, you can do something like this in a query ...

SELECT Table3.TestText,
IIf(IsDate([TestText]),CDbl(CDate([TestText])),CDbl([TestText])) AS
TestExp
FROM Table3;

With test values of "10", "52", and "1 Jan 2005", the result of this query
is 10, 52, and 38353. If I was to sum these three values, I would then get
38415, which is the numeric value of the date 4 March 2005. In other
words, the query, when summed, is the equivalent of adding 10 + 52 days to
1 Jan 2005. This might make sense if the numbers do, in fact, represent
days, or complete nonsense if they represent something else.

--
Brendan Reynolds (MVP)

Gregorio said:
Hi Rick

In SQL Server data stores multiple data types in a tes field is common,
though maybe bad practice. Queries then use the SQL 92 Convert function
while delivering result sets. Access prior to ver. 2003 supports SQL-89
from what I know, and so doesn't support the Convert function.

I enjoy the simplicity of Access (my clients benefit) but we may need to
move on to SQL Server. Does anyone know of a quick display-time fix to
this problem?

G
Click to expand...
Click to expand...
 
I'm not surprised you haven't seen it before, as I would imagine that
situations in which it would make sense to add a column containing dates and
numbers are quite rare. It is also likely to be slow if there are large
numbers of records, as at least two function calls (possibly three) will
need to be evaluated for each record, and the database will be unable to use
any index that may exist on the field. But if you're stuck with the existing
database design and this solves your problem, then I'm happy to have helped.

--
Brendan Reynolds (MVP)

Gregorio said:
Brendan
Worked instantly. Um, you're my new best friend. My only question is: why
have I never seen this one before?
- Greg


Brendan Reynolds said:
I'm not sure what the underlying logic is here, by which I mean I'm not
sure what you expect the result of adding numbers and dates to be - it
rather depends on what the numbers represent, I suppose. Just by way of
illustration, you can do something like this in a query ...

SELECT Table3.TestText,
IIf(IsDate([TestText]),CDbl(CDate([TestText])),CDbl([TestText])) AS
TestExp
FROM Table3;

With test values of "10", "52", and "1 Jan 2005", the result of this
query is 10, 52, and 38353. If I was to sum these three values, I would
then get 38415, which is the numeric value of the date 4 March 2005. In
other words, the query, when summed, is the equivalent of adding 10 + 52
days to 1 Jan 2005. This might make sense if the numbers do, in fact,
represent days, or complete nonsense if they represent something else.

--
Brendan Reynolds (MVP)

Gregorio said:
Hi Rick

In SQL Server data stores multiple data types in a tes field is common,
though maybe bad practice. Queries then use the SQL 92 Convert function
while delivering result sets. Access prior to ver. 2003 supports SQL-89
from what I know, and so doesn't support the Convert function.

I enjoy the simplicity of Access (my clients benefit) but we may need to
move on to SQL Server. Does anyone know of a quick display-time fix to
this problem?

G

"Rick B" <Anonymous> wrote in message
A field should store one type of data. Text can't be "added".

Sounds like your table structure is wrong if you are storing dates and
number sin the same field.

--
Rick B



Access 2000, XP, 2003
Win XP Pro

I have a variety of data in a single Text field of a table, some
numbers,
some dates. I get undesired concatenation when adding the entries in
the
field. Can this be corrected through some variation of the Format
function
at report display-time? In past I've appended to temp tables with a
number
Data Type in order to add but it seems unnecessarily complicated.

Thanks in advance - G

Details:
With the Data Type of the source table set to text, I can store both
numbers
and dates in that field, however when it's time to display entries
added
together they concatenate, rather than add.

With the Data Type of the source table set to Number, I can't store
the
dates but the math works.
Click to expand...
Click to expand...
Click to expand...
 
Back
Top