Defining Datatypes in a Make Table Query

[ftrujill0]

is it possible to define the datatype in a make table query? If so, how?

I would like to have a number (0.00) value field, but access seems to saves
as a text datatype each time. I tried the formatnumber function (fieldname:
formatnumber(0,0) but it still saves as a text.

Thanks in advance!

 

[Jeff Boyce]

You could learn SQL data definition language and specify the fields and data
types, ...

.... or you could define the table into which you wish to place data, then
use an append query instead of a make-table query.

By the way, if you describe the situation that you feel requires a
make-table query to solve, the 'group's readers may be able to offer
alternatives.

Regards

Jeff Boyce
<Office/Access MVP>

 

[Allen Browne]

Make Table queries are mostly useless.

Create the table the way you want it, and use an Append query instead.

If you are regularly dropping the table and running the Make Table again,
just kill the records instead of the table and run the append query again.
You can dump all records from Table1 with RunSQL and this statements:
DELETE FROM Table1;

Any attempt to use the Format() function results in a string, so if you do
this in a Make Table query you will end up with a Text field. You could
typecast the expression to a Double, e.g.:
CDbl(Nz([Length] * [Width],0))
which will give you a Number field, but it will not have the specific
granularity or display characteristics you asked for.

You are aware that fractional values in Access (and other computer programs)
cannot be stored precisely in a set number of places, and so the stored
value is not always exactly 2 places, even if you do display it that way? If
storing to exactly 2 places (or up to 4 places) is important, you can do it
with the Currency type.