Re: How to convert text to number in a query?

Tech-Archive recommends: Repair Windows Errors & Optimize Windows Performance

CCur() converts the value into Currency type data. The display of Currency
type defaults to whatever you defined in the Regional Settings of the
Control Panel in Windows.

Queries are reallly just the engine, and not really a display interface. You
normally use a form or report to display the data to the user. The text box
on your form/report has a Format property. You can set that property to
specify the currency format.

The fields in a query do have a Format property too. You can set the
property by opening the properties box (View menu in query design view),
clicking on the field, and setting the property. This works for a saved
query, but obviously does not apply if you are writing SQL strings.

You can use the Format() function to specify a format for a field in a
query. That's suitable for exporting the value, but is not suitable for
working on it within Access, because the results will behave as text. They
left-align. 2 is greater than 100. They sort wrong. The criteria works
wrong. Numeric operations (such as summing) fail.

--
Allen Browne - Microsoft MVP. Perth, Western Australia.
Tips for Access users - http://allenbrowne.com/tips.html
Reply to group, rather than allenbrowne at mvps dot org.

"Ben W" <BenW@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:0C141E82-B89D-4804-8797-EBDDEFF0A3D7@xxxxxxxxxxxxxxxx
Hi,

Thanks for the quick response!! I'm not trying to assign the string as
part
of the SQL, I'm building this part of the string seperately, and then
adding
it into the SQL which is built further down the script. Anyway....this
works
great and does exactly what I want....well nearly, it's actually in £
instead
of Euro - Looking at my original post it replaced the Euro symbol with a
"?".
Bizarre!?!?

So, this piece of code below works great for sterling. I also need it
replicated for Euro and US Dollar. Would you be kind enough to show me
what
the command for this is. Here's the working code for sterling:

rev_calc = "CCur(Nz(Sum([tbl_Union_Sales].[Final_Revenue]),0)) AS [Revenue
£],"

Thanks very much!!


.

Relevant Pages

  • Re: table formatting not carrying to calculated field in query
    ... Format in table design only controls display. ... But why does the query see the formatting in the field "State" ... And because the concantated field ...
    (microsoft.public.access.queries)
  • Re: Very Weird Date Format Problem
    ... Many, many, many thanks John. ... the date fields set format to d All but L1 display the day. ... The query uses a stored field which is the first of the month. ...
    (microsoft.public.access.reports)
  • Re: table formatting not carrying to calculated field in query
    ... Format in table design only controls display. ... But why does the query see the formatting in the field "State" ... And because the concantated field ...
    (microsoft.public.access.queries)
  • Re: Access 2003 Query: Dsum function Help
    ... But basically I need the running total to restart when the organization changes. ... But the second set of data (that goes with the second query) is continuing the running total from the beginning. ... I took off the Format() function - it isn't necessary if the "Cost" ... column is already a Currency data type in the table. ...
    (microsoft.public.access.queries)
  • Re: number to appear as currency in list box
    ... "John W. Vinson" wrote: ... that has a format of Currency in the underlying query. ...
    (microsoft.public.access.forms)