How to sum a text column containing numbers?

  • Thread starter Thread starter Terry
  • Start date Start date
T

Terry

I have access 2003 running against a SQL database and I
created a view that returns the last few characters from
a text column (with an AS statement)

Now I want to group by a date column and add the values
of the returned characters. The problem is that I cannot
figure out how to get access to convert text to number. I
tried VAL, but it doesnt work. to_num is what I think
Oracle and some others use, but no luck there either.

Any help out there?
 
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Don't know how a text column's values can be converted to numbers using
the AS predicate. AS is used to indicate an alias.

Anywho, how about:

SELECT date_column , SUM(Val(text_column)) As TextTotal
FROM table_name
WHERE < criteria >
GROUP BY date_column

Val() only works when the number part of the text field is first. E.g.:

Val("123abc") will return 123.

Val("abc123") will return 0.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQTZdNYechKqOuFEgEQLogACeK7C5B1iyx40elEm9f6Hc0hxX6qAAoLdq
Gs4iUblV3SOnMStiundSWuW6
=+FNq
-----END PGP SIGNATURE-----
 
Val should work to convert a string into a number.

When you say it "doesn't work" can you be a bit more specific? Do you get an
error message? What is it? Do you get no results, the wrong result...?
 
Back
Top