Formatting in access SQL 2

[SQLScholar]

Hey all,

thanks to some sage advice earlier, i nearly have the exact report i want.

Only difference is that currently all my values (i have used round(,2)) are only rounded to 2 DP. What i really would like is to format them so there £##.## or similar.

Hopefully then when the csv it outputs, is opened in excel, it will realise there £££££.

Thanks in advance

D

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[PHV]

I quite don't understand your question.
Perhaps a look at the Format function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLScholar]

Basically - in a query in access i have a number which isnt, and cannot be formatted in the database to currency. What i want to do is make it look like currency in a query.

D

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[PHV]

Have you tried the Format function ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLScholar]

I tried looking for that in access help but it doesnt tell me anything about how to do this via SQL?

D

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[BNPMike]

It looks like it's not a report (in Access terms). It's a query that you are dumping to Excel?

 

[PHV]

Something like:
SELECT Format([name of amount field], "Currency") As Amount
or in the QDV:
Amount: Format([name of amount field], "Currency")

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[BNPMike]

First look at Format in help. Then go into your query in design mode. Select the field and select properties. Insert your format in the format properties. Press the button. Job done.

 

[SQLScholar]

Sorry i am not querying directly in access but into an access DB via ODBC

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[BNPMike]

If you are in Access then Access generates the ODBC from Access SQL. Access will apply its view of life to the data returned by ODBC. Therefore you can use the QBE Query Dialogue.

 

[PHV]

i have used round(,2))
How do you do this ?

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLScholar]

well i have put

round(fieldname,2)as field


----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[PHV]

Have you tried this ?
Format(fieldname, "Currency") as field

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[SQLScholar]

Just before (sorry i forgot to write back) - got a too few parameters, expected 1 - error

code was:

format(`Insurer_Accounts`.Gross,"Currency") AS Gross,

Thanks mate.

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[KenReay]

Why have you got ' ' in

format(`Insurer_Accounts`.Gross,"Currency") AS Gross,

try

format(Insurer_Accounts.Gross,"Currency") AS Gross,

or

format([Insurer_Accounts].Gross,"Currency") AS Gross,

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[SQLScholar]

because thats the way that the program i use to interact with access via ODBC does it.... and that is definitely not the problem because the rest of the fields are defined exactly the same, and its causing no trouble.

I personally dont like the convension but it seems pointless changing what works!

Dan

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[KenReay]

Hi

"got a too few parameters, expected 1 - error"

implies it does not work. That error usually indicates a syntax error in the SQL eg miss-spelled column names or some such, I was merely pointing out something which looked to me like it may be the problem, since I had only seen that snippit of the SQL I had no way of knowing that all the tablenames are defined in this way.

Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[SQLScholar]

Oh i definantly agree.... its what it sounds like.... the way to fix it i dont know!

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!

 

[KenReay]

HI

I don't think you can use the same column name in the As so how about:

format(`Insurer_Accounts`.Gross,"Currency") AS GrossCurrency,


Regards

Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now -

http://www.kenneth.reay.btinternet.co.uk

UK

 

[SQLScholar]

Still not working

Tried

format(`Insurer_Accounts`.Gross,"Currency") AS CURRGross,

and i am getting a two few para still

Thanks anywayz

D

----------------------------------------
There are 2 types of computer, the prototype and the obsolete!!