Help regarding Format and Convert

[MAB]

How do I use the format function to group digits. like 1129 should return
1,129.00

The help in Access 2000 didn't help much.

I have to use this function in a query.

Also is there a way/function to change the fieldsize of a text field ( like
from 255 to 15 ) that can be used from a query

Like in SQL Server, convert(fieldname,varchar(15)) will change the field
size to 15

 

[Brendan Reynolds]

Thx this works but whats the reason for the 0 before the decimal point?
shouldnt "#,###.00" also work ?
How about doing away with the 0's "#,###.##"
Maybe i'll try it sometime

? format$(1129,"#,###.##")
1,129.
? format$(.5,"#,###.##")
..5
? format$(.5,"#,##0.00")
0.50

It's been a while since I looked at the help topic for this subject, but it
should be explained there.

Actually I'm using a "select into...." query from vb code to create a new
table. Most of the fields in the select list are table columns but some are
string variables in my vb app. Now the columns that are created for these
variables in the target table are of 255 chars. It just doesnt look good
although its not really causing me any problem!). One variable does not
contain more then 2 chars and the other max 15. So I thought maybe there was
a way to tell access to create of specific field size.

There is, but not, as far as I know, in a make-table query. You'll need to
create the table first, using either DAO, ADO, or CREATE TABLE in a DDL
query, then use an append query to put the data into the table. Help on DDL
tends to be a bit difficult to find from the UI in recent versions of
Access, if you have trouble finding it, try opening the help file directly -
search your hard disk for JETSQL40.CHM. Here's an example ...

Public Sub CreateTable()

Dim strSQL As String

strSQL = "CREATE TABLE Table1 (Field1 TEXT (10), Field2 TEXT (15),
Field3 TEXT (20))"
CurrentDb.Execute strSQL

End Sub