Change text to number

[Donna]

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]

In your query you can create a new field to sort on:

MyNewFieldNameHere:CCur(Nz([YourOriginalFieldHere],0))
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

 

[boblarson]

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 http://www.btabdevelopment.com

__________________________________

 

[John W. Vinson]

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))

 

[黎先生]

无法安装messenger,ä¸èƒ½æ‰“开键什么的æžä¸æ‡‚.

 

[Susan May]

Bob - what is you don't want the decimal and last 2 digits in this string.
How can I revise it to look like $50,000,000. My field is stored as text due
to being imported from excel... it doesn't keep the original formatting.

Many thanks.

Susan

In your query you can create a new field to sort on:

MyNewFieldNameHere:CCur(Nz([YourOriginalFieldHere],0))
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

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?

 

[SANDRA D]

Bob - what is you don't want the decimal and last 2 digits in this string.
How can I revise it to look like $50,000,000. My field is stored as text
due
to being imported from excel... it doesn't keep the original formatting.

Many thanks.

Susan

In your query you can create a new field to sort on:

MyNewFieldNameHere:CCur(Nz([YourOriginalFieldHere],0))
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________

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?

 

[Susan May]

Sandra - I don't understand your response.

Bob - what is you don't want the decimal and last 2 digits in this string.
How can I revise it to look like $50,000,000. My field is stored as text
due
to being imported from excel... it doesn't keep the original formatting.

Many thanks.

Susan

In your query you can create a new field to sort on:

MyNewFieldNameHere:CCur(Nz([YourOriginalFieldHere],0))
--
Bob Larson

Free Tutorials and Samples at http://www.btabdevelopment.com

__________________________________


:

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?