I have a Access database that is auto updated with a text in a date field.I need to run a query off of this data, but I need to change the field in a query and not the table itself. If I change it in the table, I will have to change it again anytime I want to run it. The table is updated everyday by another source which can't be changed.The field is a date in the text format, but I want to change it to a Date format through a query. When working in Cognos I would just use a Sting to date function, But Access doesn't have this function. What would be the best way to do this?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Need help changing a value from text to Date In Access
- Thread starter B555
- Start date
Hi
Access has such a function, see CDate() in help
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
Access has such a function, see CDate() in help
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
- Thread starter
- #4
- Thread starter
- #5
Hi
CDate() has been there since Access97, and maybe before
dDate = Cdate(Yourdateastext)
or in a query
dDate:Cdate(Youdateastext)
It may also be worth looking up the isDate() function and possibly Format()
Type Conversion Functions
Each function coerces an expression to a specific data type.
Syntax
CBool(expression)
CByte(expression)
CCur(expression)
CDate(expression)
CDbl(expression)
CDec(expression)
CInt(expression)
CLng(expression)
CSng(expression)
CStr(expression)
CVar(expression)
The required expression argument is any string expression or numeric expression.
Return Types
The function name determines the return type as shown in the following:
Function Return Type Range for expression argument
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 -1.79769313486231E308 to
-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values.
CDec Decimal +/-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.
CInt Integer -32,768 to 32,767; fractions are rounded.
CLng Long -2,147,483,648 to 2,147,483,647; fractions are rounded.
CSng Single -3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values.
CStr String Returns for CStr depend on the expression argument.
CVar Variant Same range as Double for numerics. Same range as String for non-numerics.
Remarks
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.
A 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.
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
CDate() has been there since Access97, and maybe before
dDate = Cdate(Yourdateastext)
or in a query
dDate:Cdate(Youdateastext)
It may also be worth looking up the isDate() function and possibly Format()
Type Conversion Functions
Each function coerces an expression to a specific data type.
Syntax
CBool(expression)
CByte(expression)
CCur(expression)
CDate(expression)
CDbl(expression)
CDec(expression)
CInt(expression)
CLng(expression)
CSng(expression)
CStr(expression)
CVar(expression)
The required expression argument is any string expression or numeric expression.
Return Types
The function name determines the return type as shown in the following:
Function Return Type Range for expression argument
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 -1.79769313486231E308 to
-4.94065645841247E-324 for negative values; 4.94065645841247E-324 to 1.79769313486232E308 for positive values.
CDec Decimal +/-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.
CInt Integer -32,768 to 32,767; fractions are rounded.
CLng Long -2,147,483,648 to 2,147,483,647; fractions are rounded.
CSng Single -3.402823E38 to -1.401298E-45 for negative values; 1.401298E-45 to 3.402823E38 for positive values.
CStr String Returns for CStr depend on the expression argument.
CVar Variant Same range as Double for numerics. Same range as String for non-numerics.
Remarks
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.
A 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.
Regards
Ken Reay
Freelance Solutions Developer
Boldon Information Systems Ltd
Website needs upgrading, but for now - UK
- Thread starter
- #7
This is how I finally got it to work. Thanks for everyones help. B555
This will convert text ("yyyymmdd") to a date field ("mm/dd/yyyy")
NEW FIELD NAME: DateValue(Mid([FIELD THAT HAS TEXT DATE],5,2) & "/" & Mid([FIELD THAT HAS TEXT DATE],7,2) & "/" & Mid([FIELD THAT HAS TEXT DATE],1,4))
This will convert text ("yyyymmdd") to a date field ("mm/dd/yyyy")
NEW FIELD NAME: DateValue(Mid([FIELD THAT HAS TEXT DATE],5,2) & "/" & Mid([FIELD THAT HAS TEXT DATE],7,2) & "/" & Mid([FIELD THAT HAS TEXT DATE],1,4))
- Status
Similar threads
- Locked
- Question
- Locked
- Question
- Locked
- Question