More limitations on data types can be found in Data Type Limitations. Microsoft Access is a Database Management System offered by Microsoft. This value rounds up to the integer 0, and since the zero day maps to, this is the date used.Ī pipe character (|) cannot be used in a date value, even if enclosed in back quotes.ĭata type limited to Microsoft Access 4.0. Otherwise, Microsoft Access will treat the value as an arithmetic expression and will not raise a warning or error.įor example, the date "March 5, 1996" must be represented as or # otherwise, if only is submitted, Microsoft Access will evaluate this as 3 divided by 5 divided by 1996. Predefined Formats Yes, True, and On are equivalent, as are No, False, and Off. Therefore, these formats apply only to data that is displayed in a text box control. Predefined and custom formats are ignored when a check box control is used. Author of The Ten Commandments Of VBA For Microsoft Access Newbies. Access uses a check boxcontrol as the default control for the Yes/No data type. A Variant containing an array requires 12 bytes more than the array alone. Set intrinsic data types The following table shows the supported data types, including storage sizes and ranges. Procedures should be used to pass character data when using any special character in a character data type column.ĭate values must be either delimited according to the ODBC canonical date format or delimited by the datetime delimiter ("#"). You might consider creating a new table with the proper data types using SQL: CREATE TABLE and INSERT statements are what to study, and then import the data into the properly typed table. Data types include those in the following table as well as user-defined types and specific types of objects. Use two consecutive single quotation marks ('') to represent one single quotation mark ('). Data typeĬreating a BINARY, VARBINARY, or VARCHAR column of zero or unspecified length actually returns a 510-byte column.Įven though a Microsoft Access NUMBER field with a FieldSize equal to BYTE is unsigned, a negative number can be inserted into the field when using the Microsoft Access driver.Ī character string literal can contain any ANSI character (1-255 decimal). The following table shows limitations on Microsoft Access data types. All conversions in Appendix D of the ODBC Programmer's Reference are supported for the SQL data types listed in the previous table. It will not return all Microsoft Access data types if more than one Microsoft Access type is mapped to the same ODBC SQL data type. Unicode and Access 4.0 applications only. The following table shows the Microsoft Access data types, data types used to create tables, and ODBC SQL data types.