 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
MS Access Forum / Modules / DAO / VBA / October 2004comparing date and string values
Since Access apparently has no way to convert strings to dates (or am I mistaken?), what's the best way to compare values between the two data types? The string value is in a date format. To convert a string representation of a date into a date/time value, use CDate(). If the comparison involves literal dates in a SQL string, be sure to format mm/dd/yyyy, and include the # delimiters.  SignatureAllen Browne - Microsoft MVP. Perth, Western Australia. Tips for Access users - http://allenbrowne.com/tips.html Reply to group, rather than allenbrowne at mvps dot org. > Since Access apparently has no way to convert strings to > dates (or am I mistaken?), what's the best way to compare > values between the two data types? The string value is in a > date format. D'OH !!! Thanks much for steering me in the right direction! >-----Original Message----- >To convert a string representation of a date into a date/time value, use [quoted text clipped - 9 lines] > >. I have tried to use this function to convert my date from a string to a date and am having trouble. I keep receiving a conversion error when I run my function. I am relatively new to Access programming, so I am sure I have just missed something in my code, so if anyone is willing to help me out it would be great. I am trying to convert a string in one table that is in yymmdd format to a date in another table using dd/mm/yy format. The following is the function I have come up with so far. Public Function ConvertDate() On Error GoTo ConvertDate_Err Dim datein As String Dim date1 As String Dim date2 As String Dim date3 As String Dim dateout As String datein = DLookup("[date]", "[tbleraw]") date1 = Left("datein", 2) date2 = Mid("datein", 2, 2) date3 = Mid("datein", 4, 2) dateout = date3 & date2 & date1 DLookup("[date]", "[tblenldb]") = CDate(dateout) ConvertDate_Exit: Exit Function ConvertDate_Err: MsgBox "Error " & Err & " : " & Err.Description GoTo ConvertDate_Exit End Function If anyone can tell me what I am doing wrong, I would appreciate it. > To convert a string representation of a date into a date/time value, use > CDate(). [quoted text clipped - 6 lines] > > values between the two data types? The string value is in a > > date format. Kevin, Break this job into a couple smaller pieces. First write a function that converts any 'yymmdd' string into a date. Public Function ConvertYYMMDDStringToDate(strDateIn As String) As Date On Error GoTo ConvertYYMMDDStringToDate_Err Dim strYear As String Dim strMonth As String Dim strDay As String strYear = Left(strDateIn, 2) strMonth = Mid(strDateIn, 3, 2) strDay = Mid(strDateIn, 5, 2) 'DateSerial takes integer arguments of Year, Month, and Day. This helps avoid location dependency ' Assumes dates are after 1999. NOT pre-2k date safe! ConvertYYMMDDStringToDate = DateSerial(CInt("20" & strYear), CInt(strMonth), CInt(strDay)) ConvertYYMMDDStringToDate_Exit: Exit Function ConvertYYMMDDStringToDate_Err: MsgBox "Error " & Err & " : " & Err.Description GoTo ConvertYYMMDDStringToDate_Exit End Function Now you can test this function in the Immediate Window (Ctrl-G) ? ConvertYYMMDDStringToDate("010101") 1/1/2001 ? Format(ConvertYYMMDDStringToDate("030201"), "mmmm dd, yyyy") February 01, 2003 Next, I would add a date field to your table and run and UPDATE query. UPDATE tbleraw SET RealDate = ConvertYYMMDDStringToDate([tbleraw].[Date]) HTH, Kevin >I have tried to use this function to convert my date from a string to a >date [quoted text clipped - 47 lines] >> > values between the two data types? The string value is in a >> > date format. I have tried to use this function to convert my date from a string to a date and am having trouble. I keep receiving a conversion error when I run my function. I am relatively new to Access programming, so I am sure I have just missed something in my code, so if anyone is willing to help me out it would be great. I am trying to convert a string in one table that is in yymmdd format to a date in another table using dd/mm/yy format. The following is the function I have come up with so far. Public Function ConvertDate() On Error GoTo ConvertDate_Err Dim datein As String Dim date1 As String Dim date2 As String Dim date3 As String Dim dateout As String datein = DLookup("[date]", "[tbleraw]") date1 = Left("datein", 2) date2 = Mid("datein", 2, 2) date3 = Mid("datein", 4, 2) dateout = date3 & date2 & date1 DLookup("[date]", "[tblenldb]") = CDate(dateout) ConvertDate_Exit: Exit Function ConvertDate_Err: MsgBox "Error " & Err & " : " & Err.Description GoTo ConvertDate_Exit End Function If anyone can tell me what I am doing wrong, I would appreciate it. > To convert a string representation of a date into a date/time value, use > CDate(). [quoted text clipped - 6 lines] > > values between the two data types? The string value is in a > > date format. If the string value is in a format that Access should recognize as a date, you should be able to convert it. Try CDate(strDateString) or placing # signs (i.e. date delimiters) around the string (like you would put quotes around text). SignatureWayne Morgan Microsoft Access MVP > Since Access apparently has no way to convert strings to > dates (or am I mistaken?), what's the best way to compare > values between the two data types? The string value is in a > date format. |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2008 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.