|  | Change text to number | | |
| | | Donna |  |
|  Posted: Tue Sep 23, 2008 2:13 am Post subject: Change text to number |  |
I have inherited a database that has a field storing currency but its
formatted as text. These are tables on a server that I link to via an odbc
connection. I can't change the table formats. Is there any way I can change
the text to a number or currency in my queries and or reports so that it will
sort by amount properly? |
| |
| | | boblarson | |
| | Posted: Tue Sep 23, 2008 2:38 am Post subject: RE: Change text to number | |
In your query you can create a new field to sort on:
MyNewFieldNameHere:CCur(Nz([YourOriginalFieldHere],0))
--
Bob Larson
Free Tutorials and Samples at LINK
__________________________________
"Donna" wrote:
| Quote: | I have inherited a database that has a field storing currency but its
formatted as text. These are tables on a server that I link to via an odbc
connection. I can't change the table formats. Is there any way I can change
the text to a number or currency in my queries and or reports so that it will
sort by amount properly? |
|
| |
| | | boblarson | |
| | Posted: Tue Sep 23, 2008 2:39 am Post subject: RE: Change text to number | |
Oh, and the reason I included the NZ function was to handle nulls so it
didn't return errors for null values.
--
Bob Larson
Free Tutorials and Samples at LINK
__________________________________
"Donna" wrote:
| Quote: | I have inherited a database that has a field storing currency but its
formatted as text. These are tables on a server that I link to via an odbc
connection. I can't change the table formats. Is there any way I can change
the text to a number or currency in my queries and or reports so that it will
sort by amount properly? |
|
| |
| | | John W. Vinson | |
| | Posted: Tue Sep 23, 2008 4:14 am Post subject: Re: Change text to number | |
On Mon, 22 Sep 2008 21:13:00 -0700, Donna <Donna@discussions.microsoft.com>
wrote:
| Quote: | I have inherited a database that has a field storing currency but its
formatted as text. These are tables on a server that I link to via an odbc
connection. I can't change the table formats. Is there any way I can change
the text to a number or currency in my queries and or reports so that it will
sort by amount properly?
|
What's stored in the table: "415.00" or "$415.00" or "Four hundred fifteen
dollars"? or what?
If the field is numeric, you can use the CCur() function to convert text to
currency. You may need to space over the dollar sign:
CCur(Mid([fieldname], 2))
--
John W. Vinson [MVP] |
| |
| | | 黎先生 | |
| | Posted: Tue Sep 23, 2008 7:59 am Post subject: Re: Change text to number | |
无法安装messenger,不能打开键什么的搞不懂.
"Donna" <Donna@discussions.microsoft.com> 写入消息
news:75D43870-981F-461C-BE73-F75F6CA983BC@microsoft.com...
| Quote: | I have inherited a database that has a field storing currency but its
formatted as text. These are tables on a server that I link to via an
odbc
connection. I can't change the table formats. Is there any way I can
change
the text to a number or currency in my queries and or reports so that it
will
sort by amount properly? |
|
| |
|
|
