how to specify decimal on query

I create an append query on Access as follow:

Parameters p_field1 decimal;
insert into table1 (field1) values ([p_field1]);

The field1 is a decimal field with 2 decimal points. How do I specify its
type?
Thanks.

Joe.

"Joe Au" wrote
I create an append query on Access as follow:

Parameters p_field1 decimal;
insert into table1 (field1) values ([p_field1]);

The field1 is a decimal field with 2 decimal points. How do I specify its
type?

Hi Joe,

From Access 2000 Help:

PARAMETERS declaration DATA TYPES:

Data Type SQL PARAMETER string
========= ============================
Yes/No PARAMETERS [enter yes/no] Bit;
Byte PARAMETERS [enter 0-255] Byte;
Integer PARAMETERS [enter num] Short;
Long Integer PARAMETERS [enter num] Long;
Currency PARAMETERS [enter money] Currency;
Single PARAMETERS [enter num] IEEESingle;
Double PARAMETERS [enter num] IEEEDouble;
Date/Time PARAMETERS [enter startdate] DateTime;
Binary PARAMETERS [enter bin] Binary;
Text PARAMETERS [enter text] Text(255);
OLE Object PARAMETERS Forms!frmOLE!txtOLE LongBinary;
Memo PARAMETERS [enter memo] Text;
Replication ID PARAMETERS [enter RepID] Guid;
Value PARAMETERS Forms!frm1!txt1 Value;

The Microsoft Jet database engine SQL data types consist of 13 primary data types defined by the Microsoft Jet database engine and several valid synonyms recognized for these data types.
The following table lists the primary data types. The synonyms are identified in Microsoft Jet Database Engine SQL Reserved Words.

Data type Storage size Description

BINARY 1 byte per character Any type of data may be stored in a field of this type. No translation of the data
(for example, to text) is made. How the data is input in a binary field dictates how it
will appear as output.
BIT 1 byte Yes and No values and fields that contain only one of two values.
BYTE 1 byte An integer value between 0 and 255.
COUNTER 4 bytes A number automatically incremented by the Microsoft Jet database engine whenever
a new record is added to a table. In the Microsoft Jet database engine, the data type
for this value is Long.
CURRENCY 8 bytes A scaled integer between - 922,337,203,685,477.5808 and 922,337,203,685,477.5807.
DATETIME
(See DOUBLE) 8 bytes A date or time value between the years 100 and 9999.
GUID 128 bits A unique identification number used with remote procedure calls.
SINGLE 4 bytes A single-precision floating-point value with a range of - 3.402823E38 to - 1.401298E-45 for
negative values, 1.401298E-45 to 3.402823E38 for positive values, and 0.
DOUBLE 8 bytes A double-precision floating-point value with a range of
- 1.79769313486232E308 to - 4.94065645841247E-324 for negative values,
4.94065645841247E-324 to 1.79769313486232E308 for positive values, and 0.
SHORT 2 bytes A short integer between - 32,768 and 32,767.
LONG 4 bytes A long integer between - 2,147,483,648 and 2,147,483,647.
LONGTEXT 1 byte per character Zero to a maximum of 1.2 gigabytes.
LONGBINARY As required Zero to a maximum of 1.2 gigabytes. Used for OLE objects.
TEXT 1 byte per character Zero to 255 characters.

Note You can also use the VALUE reserved word in SQL statements.

//////////////////////////////////////////////////////////////////////////////
Here might be one way:

If using Access 2000, save the following in a module:

Public Function CDec2000(pNum)
CDec2000 = CDec(Nz(pNum))
End Function

To test, in Immediate Window, I created simple table:

currentproject.Connection.Execute ("CREATE TABLE tblDecimal (ID COUNTER,fDec DECIMAL(10,2))")

Then in QBE, I ran the following query:

PARAMETERS p_field1 CURRENCY;
INSERT INTO tblDecimal (fDec) VALUES (CDec2000([p_field1]));

I also tested the following query:

PARAMETERS p_field1 SINGLE;
INSERT INTO tblDecimal (fDec) VALUES (CDec2000([p_field1]));

These were "simple, limited" tests. How many ways can
this fail? I don't know.

You might change CDec2000 function code
to test for valid "number" (maybe you do not
want to insert 0 when no number is entered,
for example). Or maybe you want to round *any*
number to 2 decimals the way *you* want rounding
to work. You might also want to add error checking
code.

Or maybe you might want to abandon using parameter
in query, but instead, use a form that asks for p_field1,
then in command button code do all the checking,
and when verified, build the SQL using users number
and then execute it.

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter

Thanks Gary for your valurable information.
Joe.


"Gary Walter" wrote in message ...

"Joe Au" wrote
I create an append query on Access as follow:

Parameters p_field1 decimal;
insert into table1 (field1) values ([p_field1]);

The field1 is a decimal field with 2 decimal points. How do I specify its
type?

Hi Joe,

From Access 2000 Help:

PARAMETERS declaration DATA TYPES:

Data Type SQL PARAMETER string
========= ============================
Yes/No PARAMETERS [enter yes/no] Bit;
Byte PARAMETERS [enter 0-255] Byte;
Integer PARAMETERS [enter num] Short;
Long Integer PARAMETERS [enter num] Long;
Currency PARAMETERS [enter money] Currency;
Single PARAMETERS [enter num] IEEESingle;
Double PARAMETERS [enter num] IEEEDouble;
Date/Time PARAMETERS [enter startdate] DateTime;
Binary PARAMETERS [enter bin] Binary;
Text PARAMETERS [enter text] Text(255);
OLE Object PARAMETERS Forms!frmOLE!txtOLE LongBinary;
Memo PARAMETERS [enter memo] Text;
Replication ID PARAMETERS [enter RepID] Guid;
Value PARAMETERS Forms!frm1!txt1 Value;

The Microsoft Jet database engine SQL data types consist of 13 primary data types defined by the Microsoft Jet database engine and several valid synonyms recognized for these data types.
The following table lists the primary data types. The synonyms are identified in Microsoft Jet Database Engine SQL Reserved Words.

Data type Storage size Description

BINARY 1 byte per character Any type of data may be stored in a field of this type. No translation of the data
(for example, to text) is made. How the data is input in a binary field dictates how it
will appear as output.
BIT 1 byte Yes and No values and fields that contain only one of two values.
BYTE 1 byte An integer value between 0 and 255.
COUNTER 4 bytes A number automatically incremented by the Microsoft Jet database engine whenever
a new record is added to a table. In the Microsoft Jet database engine, the data type
for this value is Long.
CURRENCY 8 bytes A scaled integer between - 922,337,203,685,477.5808 and 922,337,203,685,477.5807.
DATETIME
(See DOUBLE) 8 bytes A date or time value between the years 100 and 9999.
GUID 128 bits A unique identification number used with remote procedure calls.
SINGLE 4 bytes A single-precision floating-point value with a range of - 3.402823E38 to - 1.401298E-45 for
negative values, 1.401298E-45 to 3.402823E38 for positive values, and 0.
DOUBLE 8 bytes A double-precision floating-point value with a range of
- 1.79769313486232E308 to - 4.94065645841247E-324 for negative values,
4.94065645841247E-324 to 1.79769313486232E308 for positive values, and 0.
SHORT 2 bytes A short integer between - 32,768 and 32,767.
LONG 4 bytes A long integer between - 2,147,483,648 and 2,147,483,647.
LONGTEXT 1 byte per character Zero to a maximum of 1.2 gigabytes.
LONGBINARY As required Zero to a maximum of 1.2 gigabytes. Used for OLE objects.
TEXT 1 byte per character Zero to 255 characters.

Note You can also use the VALUE reserved word in SQL statements.

//////////////////////////////////////////////////////////////////////////////
Here might be one way:

If using Access 2000, save the following in a module:

Public Function CDec2000(pNum)
CDec2000 = CDec(Nz(pNum))
End Function

To test, in Immediate Window, I created simple table:

currentproject.Connection.Execute ("CREATE TABLE tblDecimal (ID COUNTER,fDec DECIMAL(10,2))")

Then in QBE, I ran the following query:

PARAMETERS p_field1 CURRENCY;
INSERT INTO tblDecimal (fDec) VALUES (CDec2000([p_field1]));

I also tested the following query:

PARAMETERS p_field1 SINGLE;
INSERT INTO tblDecimal (fDec) VALUES (CDec2000([p_field1]));

These were "simple, limited" tests. How many ways can
this fail? I don't know.

You might change CDec2000 function code
to test for valid "number" (maybe you do not
want to insert 0 when no number is entered,
for example). Or maybe you want to round *any*
number to 2 decimals the way *you* want rounding
to work. You might also want to add error checking
code.

Or maybe you might want to abandon using parameter
in query, but instead, use a form that asks for p_field1,
then in command button code do all the checking,
and when verified, build the SQL using users number
and then execute it.

Please respond back if I have misunderstood
or was not clear about something.

Good luck,

Gary Walter