Type Conversion Functions

Applies to: Access for Microsoft 365, Access 2019, Access 2016, Access 2013, Access 2010, Access 2007.

Each function forces an expression to a distinct data type.


CBool( expression )

CByte( expression )

CCur( expression )

CDate( expression )

CDbl( expression )

CDec( expression )

CInt( expression )

CLng( expression )

CSng( expression )

CStr( expression )

CVar( expression )

The required expressionargument is any string expression or numeric expression.

Return Types

The function name determines the return type as shown in the following:

FunctionReturn TypeRange for expression argument
CBoolBooleanAny valid string or numeric expression.
CByteByte0 to 255.
CCurCurrency-922,337,203,685,477.5808 to 922,337,203,685,477.5807.
CDateDateAny valid date expression.
CDblDouble-1.79769313486231E308 to
-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values.
CDecDecimal+/-79,228,162,514,264,337,593,543,950,335 for zero-scaled numbers, that is, numbers with no decimal places. For numbers with 28 decimal places, the range is
+/-7.9228162514264337593543950335. The smallest possible non-zero number is 0.0000000000000000000000000001.
CIntInteger-32,768 to 32,767; fractions are rounded.
CLngLong-2,147,483,648 to 2,147,483,647; fractions are rounded.
CSngSingle-3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values.
CStrStringReturns for CStr depend on the expression argument.
CVarVariantSame range as Double for numerics. Same range as String for non-numerics.


If the expression passed to the function is outside the range of the data type being converted to, an error occurs.

In general, you can document your code using the data-type conversion functions to show that the result of some operation should be expressed as a particular data type rather than the default data type. For example, use CCur to force currency arithmetic in cases where single-precision, double-precision, or integer arithmetic normally would occur.

You should use the data-type conversion functions instead of Val to provide internationally aware conversions from one data type to another. For example, when you use CCur, different decimal separators, different thousand separators, and various currency options are properly recognized depending on the locale setting of your computer.

When the fractional part is exactly 0.5, CInt and CLng always round it to the nearest even number. For example, 0.5 rounds to 0, and 1.5 rounds to 2. CInt and CLng differ from the Fix and Int functions, which truncate, rather than round, the fractional part of a number. Also, Fix and Int always return a value of the same type as is passed in.

Use the IsDate function to determine if date can be converted to a date or time. CDate recognizes date literals and time literals as well as some numbers that fall within the range of acceptable dates. When converting a number to a date, the whole number portion is converted to a date. Any fractional part of the number is converted to a time of day, starting at midnight.

CDate recognizes date formats according to the locale setting of your system. The correct order of day, month, and year may not be determined if it is provided in a format other than one of the recognized date settings. In addition, a long date format is not recognized if it also contains the day-of-the-week string.

CVDate function is also provided for compatibility with previous versions of Visual Basic. The syntax of the CVDate function is identical to the CDate function, however, CVDate returns a Variant whose subtype is Date instead of an actual Date type. Since there is now an intrinsic Date type, there is no further need for CVDate. The same effect can be achieved by converting an expression to a Date, and then assigning it to a Variant. This technique is consistent with the conversion of all other intrinsic types to their equivalent Variant subtypes.

Note:  The CDec function does not return a discrete data type; instead, it always returns a Variant whose value has been converted to a Decimal subtype.

Query examples

SELECT SalePrice,FinalPrice,CBool(SalePrice>FinalPrice) AS Expr1 FROM productSales;Returns “SalePrice”, “FinalPrice” and evaluates if SalePrice is greater than Final Price. Returns “-1” if true and “0” if false.
SELECT ProductID, CByte(Quantity) AS Expr1 FROM productSales;Returns “ProductID”,converts the values in “Quantity” field to bytes format and displays in column Expr1 Returns “ProductID”,converts the values in “Quantity” field to Currency format and displays in column Expr1.
SELECT ProductID, CDate(DateofSale) AS Expr1 FROM productSales;Returns “ProductID”,converts the values in “DateofSale” field into Date format and displays in column Expr1.
SELECT ProductID, CDbl(Discount) AS Expr1 FROM productSales;Returns “ProductID”,converts the values in “Discount” field into Double format and displays in column Expr1.
SELECT ProductID, CInt(Discount) AS Expr1 FROM productSales;Returns “ProductID”,converts the values in “Discount” field into Integer format and displays in column Expr1.
SELECT ProductID, CLng(Discount) AS Expr1 FROM productSales;Returns “ProductID”,converts the values in “Discount” field into Long format and displays in column Expr1.
SELECT ProductID, CSng(Discount) AS Expr1 FROM productSales;Returns “ProductID”,converts the values in “Discount” field into Single format and displays in column Expr1.
SELECT ProductID, CStr(Discount) AS Expr1 FROM productSales;Returns “ProductID”,converts the values in “Discount” field into String format and displays in column Expr1.
SELECT ProductID, CVar(Discount) AS Expr1 FROM productSales;Returns “ProductID”,converts the values in “Discount” field into Double for numeric values and String for Non-numeric values.

VBA examples

Note: Examples that follow demonstrate the use of this function in a Visual Basic for Applications (VBA) module. For more information about working with VBA, select Developer Reference in the drop-down list next to Search and enter one or more terms in the search box.

CBool Function

Tip:  The Expression Builder with IntelliSense beginning in Access 2010 helps with getting your expressions right.

This example uses the CBool function to convert an expression to a Boolean. If the expression evaluates to a nonzero value, CBool returns True; otherwise, it returns False.Dim A, B, Check
A = 5: B = 5 ' Initialize variables.
Check = CBool(A = B) ' Check contains True.
A = 0 ' Define variable.
Check = CBool(A) ' Check contains False.

CByte Function

This example uses the CByte function to convert an expression to a Byte.Dim MyDouble, MyByte
MyDouble = 125.5678 ' MyDouble is a Double.
MyByte = CByte(MyDouble) ' MyByte contains 126.

CCur Function

This example uses the CCur function to convert an expression to a Currency.Dim MyDouble, MyCurr
MyDouble = 543.214588 ' MyDouble is a Double.
MyCurr = CCur(MyDouble * 2)
' Convert result of MyDouble * 2 (1086.429176) to a
' Currency (1086.4292).

CDate Function

This example uses the CDate function to convert a string to a Date. In general, hard-coding dates and times as strings (as shown in this example) is not recommended. Use date literals and time literals, such as #2/12/1969# and #4:45:23 PM#, instead.Dim MyDate, MyShortDate, MyTime, MyShortTime
MyDate = "February 12, 1969"
' Convert to Date data type.
MyShortDate = CDate(MyDate)
MyTime = "4:35:47 PM"
' Convert to Date data type.
MyShortTime = CDate(MyTime)

CDbl Function

This example uses the CDbl function to convert an expression to a Double.Dim MyCurr, MyDouble
MyCurr = CCur(234.456784)
' Convert result to a Double.
MyDouble = CDbl(MyCurr * 8.2 * 0.01)

CDec Function

This example uses the CDec function to convert a numeric value to a Decimal.Dim MyDecimal, MyCurr
MyCurr = 10000000.0587 ' MyCurr is a Currency.
MyDecimal = CDec(MyCurr) ' MyDecimal is a Decimal.

CInt Function

This example uses the CInt function to convert a value to an Integer.Dim MyDouble, MyInt
MyDouble = 2345.5678 ' MyDouble is a Double.
MyInt = CInt(MyDouble) ' MyInt contains 2346.

CLng Function

This example uses the CLng function to convert a value to a Long.Dim MyVal1, MyVal2, MyLong1, MyLong2
MyVal1 = 25427.45
MyVal2 = 25427.55 ' MyVal1, MyVal2 are Doubles.
MyLong1 = CLng(MyVal1)
' MyLong1 contains 25427.
MyLong2 = CLng(MyVal2)
' MyLong2 contains 25428.

CSng Function

This example uses the CSng function to convert a value to a Single.Dim MyDouble1, MyDouble2, MySingle1, MySingle2
' MyDouble1, MyDouble2 are Doubles.
MyDouble1 = 75.3421115: MyDouble2 = 75.3421555
MySingle1 = CSng(MyDouble1)
' MySingle1 contains 75.34211.
MySingle2 = CSng(MyDouble2)
' MySingle2 contains 75.34216.

CStr Function

This example uses the CStr function to convert a numeric value to a String.Dim MyDouble, MyString
MyDouble = 437.324 ' MyDouble is a Double.
MyString = CStr(MyDouble)
' MyString contains "437.324".

CVar Function

This example uses the CVar function to convert an expression to a Variant.Dim MyInt, MyVar
MyInt = 4534 ' MyInt is an Integer.
MyVar = CVar(MyInt & "000")
' MyVar contains the string 4534000.

This information was compiled using information courtesy of © Microsoft 2020. All rights reserved.

Leave a Reply

%d bloggers like this: