Format Conversion

[Beagle1927]

Hello,

I need to convert access fields to S99999.99 format..The fiels are
currently in text format in a table and look like 1.5 or 0.40000000.

Any ideas?

 

[Marshall Barton]

I need to convert access fields to S99999.99 format..The fiels are
currently in text format in a table and look like 1.5 or 0.40000000.

You can display those numbers without converting the data by
using an expression like:
Format(CCur(field), "Currency")

If you need to change the field type from Text to Currency,
then you can add the new field and then use an UPDATE query
to convert the data from the text field to the new currency
field:

UPDATE table SET currencyfield = CCur(textfield)

After verifying that the new dield contains the correct
values, you can delete the text field.

 

[Beagle1927]

You can display those numbers without converting the data by
using an expression like:
Format(CCur(field), "Currency")

If you need to change the field type from Text to Currency,
then you can add the new field and then use an UPDATE query
to convert the data from the text field to the new currency
field:

UPDATE table SET currencyfield = CCur(textfield)

After verifying that the new dield contains the correct
values, you can delete the text field.

Thanks for the help, but.......I need to update the fileds so that all
the decimals add up with spaces on the left....Any ideas?

 

[Beagle1927]

Thanks for the help, but.......I need to update the fileds so that all
the decimals add up with spaces on the left....Any ideas?- Hide quoted text -

- Show quoted text -

Maybe I should be more specific:


I need the data like this in a ten character field:

75.26
102.85
9.48
11.10

 

[Marshall Barton]

Maybe I should be more specific:


I need the data like this in a ten character field:

75.26
102.85
9.48
11.10

Maybe you are using the word "field" loosely, but the way I
read your question, your objective would make sense only if
you are trying to format data for display in table sheet
view. Since that is so far out of bounds of good practices,
I would need to understand more about the form or report
that you are using to present the data to users. Especially
the form's Record Source and the text box's Control Source
and Format properties.

 

[Beagle1927]

Maybe I should be more specific:

I need the data like this in a ten character field:

75.26
102.85
9.48
11.10

Maybe you are using the word "field" loosely, but the way I
read your question, your objective would make sense only if
you are trying to format data for display in table sheet
view. Since that is so far out of bounds of good practices,
I would need to understand more about the form or report
that you are using to present the data to users. Especially
the form's Record Source and the text box's Control Source
and Format properties.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -

Fair enough.

I did some data analysis on a table through update queries. I am
being requested to export the data in S999999.99 format. The fields
are 10 characters long and I need it exported right justified so that
(from right to left) it has the data then blanks to the left/

 

[John Spencer]

Perhaps something like the following will work for you

Right( Space(10) & Format(75.6193,"#.00"), 10)


--
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
..

 

[Marshall Barton]

Fair enough.

I did some data analysis on a table through update queries. I am
being requested to export the data in S999999.99 format. The fields
are 10 characters long and I need it exported right justified so that
(from right to left) it has the data then blanks to the left/

Export a query instead of the table. This way you can use
calculated fields in the query to get the values most any
way you want. The calculated field John posted should do
what you are asking.