Common Access types and query syntax [access]

Data Type

The Microsoft Access database engine SQL data types include 13 major data types defined by the Microsoft Jet Database Engine, and several valid synonyms that can be recognized as these data types.

The following table lists the main data types. Synonyms are identified in Microsoft Access database engine SQL reserved words.

Data Type	Storage space size	Description
Binary	Each character occupies 1 byte	Any type of data can be stored in this type of field. No data conversion (for example, converting to text ). How data is input to binary fields and displayed as output,
Bit	1 byte	The values are Yes and no, and this field can only take one of these two values.
Tinyint	1 byte	An integer between 0 and 255.
Money	8 bytes	The adjusted integer between-922,337,203,685,477.5808 and 922,337,203,685,477.5807.
Datetime (see double)	8 bytes	The date or time value between 100 and 9999.
Uniqueidentifier	128 bytes	The unique identifier number used for Remote Procedure Calls.
Real	4 bytes	Single-precision floating point value, negative value from-3.402823e38 to-1.401298e-45, positive value from 1.401298e-45 to 3.402823e38, including zero.
Float	8 bytes	Double precision floating point value. The negative value ranges from-1.79769313486232e308 to-4.94065645841247e-324. The positive value ranges from 4.94065645841247e-324 to 1.79769313486232e308 and.
Smallint	2 bytes	A short integer between-32,768 and 32,767. (See "comment ")
Integer	4 bytes	A long integer between-2,147,483,648 and 2,147,483,647. (See "comment ")
Decimal	17 bytes	The exact numeric data type. The value range is from 1028-1 to-1028-1. You can define the precision (1-28) and size (0-definition precision ). The default precision and size are 18 and 0, respectively.
Text	Each character occupies 2 bytes (see "comment ")	The value ranges from 0 to 2.14 gibytes.
Image	As needed	The value ranges from 0 to 2.14 gibytes. Used for OLE objects.
Character	Each character occupies 2 bytes (see "comment ")	From 0 to 255 characters.

Query

The stored procedure in the ACCESS database is stored as "query". Open your Access database, select "query", select "new", and create a simple query first. Select the table, required fields, and required parameters. Then, select the SQL view in the toolbar. You can see the following statement:

 PARAMETERS [PersonID] Short;
　　DELETE *
　　FROM PersonInfo
　　WHERE [PersonInfo].[PersonID]=[PersonID];

For more parameters, see:

 PARAMETERS [ID] Short

　　[ManageDepart] Text ( 255 )

　　, [SetUpTime] DateTime

Note: When you assign values to parameters in a program, the priority must be one-to-one correspondence with the parameter declaration position in the access SQL statement. Otherwise, the program will encounter a Data Type Mismatch Error in the standard expression!

Finally, save and run your code. In addition, there is no SQL Server queryer in access, so it is more difficult to test your stored procedure in access: Right-click your stored procedure and select "open, but do not enter an error in the parameter.

Type conversion functions

The function name determines the return type, as shown below:

Function		Return type		ExpressionParameter range
Cbool		Boolean		Any valid string or numeric expression.
Cbyte		Byte		0 to 255.
Ccur		Currency		-922,337,203,685,477.5808 to 922,337,203,685,477.5807.
Cdate		Date		Any valid date expression.
Cdbl		Double		The negative number ranges from-1.79769313486231e308 to-4.94065645841247e-324. The positive number ranges from 4.94065645841247e-324 to 1.79769313486232e308.
Cdec		Decimal		Zero-to-zero ratio, that is, no decimal point + //-79,228,162,514,264,337,593,543,950,335. For the 28-digit decimal value, the range is +/-7.9228162514264337593543950335; the minimum possible non-zero value may be 0.0000000000000000000000000001.
CINT		Integer		-The decimal part is rounded to 32,768 to 32,767.
Clng		Long		-The decimal part is rounded to 2,147,483,648 to 2,147,483,647.
Csng		Single		The negative number is-3.402823e38 to-1.401298e-45, and the positive number is 1.401298e-45 to 3.402823e38.
CSTR		String		BasisExpressionThe return value is CSTR.
CVaR		Variant		If it is a numerical value, the range isDoubleSame; if not a value, the range isStringSame.

Description

IfExpressionIf the data type exceeds the range of the target data type, an error occurs.

UseIsdateFunction, which can be judgedDateWhether it can be converted to date or time.