Make Table query

[Guest]

Hi All

Im running a make table query which works great except for the formatting of
a calculated field which appears as number when I want it to be currency.

The bit of SQL i use is

[CARDSHIP]*([COST PRICE]/[UNITS IN PACK]) AS [SHIPPING COST] so I guess I
need to use some form of formatting to make it show the data as currency.

Ive tried doing this manually once the query has run but Access gives me
error messages about not having enough memory etc - there are about 500,000
records.

Thanks

Reggiee

 

[cjg.groups]

I'm no expert, but I've read to leave formatting up to whatever program
is displaying the data. ie: the Access form or the mergefields in a
Word mail merge template.

There are the "Type Conversion Functions" (found in Help with that
string), but I don't think they'll help you here.

 

[John Spencer]

You can use CCur to force the currency type. If your calculation returns
null values then you will get an error, so if that is the case you should
test the calculation to make sure it will return a number.

CCUR( [CARDSHIP]*([COST PRICE]/[UNITS IN PACK]))

For Safety
IIF(IsNull ([CARDSHIP]*([COST PRICE]/[UNITS IN PACK])),null,CCUR(
[CARDSHIP]*([COST PRICE]/[UNITS IN PACK])))

A better solution is to build the table and use an append query to populate
the table. The next time you need the table, use a delete query to clear it
and then the append query to populate it. This way, you can set the field
types in advance and if you need any indexes you can also have them in
place.

Even better would be to avoid the use of the table and just use the query.
If that is not possible - due to performance issues - then you might
consider creating a temporary database and table to avoid the bloat that
adding and deleting data will cause.

 

[Guest]

Thanks John

Great advice

You can use CCur to force the currency type. If your calculation returns
null values then you will get an error, so if that is the case you should
test the calculation to make sure it will return a number.

CCUR( [CARDSHIP]*([COST PRICE]/[UNITS IN PACK]))

For Safety
IIF(IsNull ([CARDSHIP]*([COST PRICE]/[UNITS IN PACK])),null,CCUR(
[CARDSHIP]*([COST PRICE]/[UNITS IN PACK])))

A better solution is to build the table and use an append query to populate
the table. The next time you need the table, use a delete query to clear it
and then the append query to populate it. This way, you can set the field
types in advance and if you need any indexes you can also have them in
place.

Even better would be to avoid the use of the table and just use the query.
If that is not possible - due to performance issues - then you might
consider creating a temporary database and table to avoid the bloat that
adding and deleting data will cause.

Hi All

Im running a make table query which works great except for the formatting
of
a calculated field which appears as number when I want it to be currency.

The bit of SQL i use is

[CARDSHIP]*([COST PRICE]/[UNITS IN PACK]) AS [SHIPPING COST] so I guess I
need to use some form of formatting to make it show the data as currency.

Ive tried doing this manually once the query has run but Access gives me
error messages about not having enough memory etc - there are about
500,000
records.

Thanks

Reggiee