how to specify decimal on query

[Joe Au]

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.

[berk]

Not sure if I understand, but whenever you use decimals,
you should specify the type as double

>.
>

[John Vinson]

On Sat, 31 Jul 2004 22:28:44 -0700, "berk"
<anon...@discussions.microsoft.com> wrote:

>Not sure if I understand, but whenever you use decimals,
>you should specify the type as double

... or Single, or Currency, or (if you trust the rather buggy new
datatype) Decimal. Double is useful but it's not the only choice! In
fact, Currency fields are probably best for many uses: 4 decimal
places, range into the trillions, and no roundoff error.

John W. Vinson[MVP]
Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public

[Joe Au]

Thanks you.

"John Vinson" <jvinson@STOP_SPAM.WysardOfInfo.com> wrote in message
news:i13pg0142fic0vqtq...@4ax.com...

[Gary Walter]

 

"Joe Au" wrote

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

 

 

 

 

[Joe Au]

Thanks Gary for your valurable information.

Joe.

 

 

"Gary Walter" <garylwple...@wamego.net> wrote in message news:uEW3ow8d...@TK2MSFTNGP10.phx.gbl...