How to convert text to number in a query?

[Guest]

Hi,

What do I need to do to this string so that when it runs as part of an SQL
(feeding a query) it returns the query data in this format €5,000 (as a
number - right aligned)??

Here's the string:

rev_calc = "(sum([tbl_Union_Sales].[Final_Revenue]) AS [Revenue €],"

If I use the format function it treats it as text and left aligns but I
still want it right aligned. I've also tried putting '+1-1' (old Excel trick
to convert to a number) after the format function and this converts it back
to a left aligned number but loses the formatting!?!?

NOTE - I'm trying to change the formatting of a query field, not a form field.

Surely I just need to convert it to a currency - something like "€ #,##0"
but this seems easier said than done!!?

Any help would be greatly appreciated!!

Many thanks.

 

[Allen Browne]

Ben, you cannot assign a string to a variable inside a SQL statement.

If you are working directly with the SQL statement in a Totals query, you
might be able to include this as one of the fields in the SELECT clause:
CCur(Nz(Sum([tbl_Union_Sales].[Final_Revenue]),0)) AS rev_calc

 

[Amy Blankenship]

If you select the field in the query builder and open the properties window,
you can set the properties there.

HTH;

Amy

 

[Guest]

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

 

[Allen Browne]

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.

 

[Guest]

Thanks for the advice. I guess you're right - a query is simply the engine
to bring back the results so the formatting is limited. The reason I'm not
using a form is because the select statement can change based on several list
box selections, therefore I'd have to make a pretty clever form to be able to
work out what to do - And more importantly, most of the time the user will
simply want to export straight to Excel anyway.

As I'm limited, I'll cheat and just put it in as a number. I can do this by
using the 'standard' format within the query although this gives two decimal
places that I would prefer not to have.....of course if I then use the format
function it will convert it to text!! If you can think of away round it then
great, but I can live with this and really appreciate your advice!!

Many thanks!!

 

[John Spencer]

If you want to chop off the decimals, you could use the Int function, the
Round function, or CLng function to do so. These functions can remove the
decimal portion and don't change the data type to text.

Read up on how they work in the help. For instance, CLng and Round will
round the results using banker's rounding, while Int will truncate the
number.
Int(333.9) --> 333
CLng(333.9) -->334
Round(333.9,0) --> 334