comparing date and string values

  • Thread starter Thread starter Virgil
  • Start date Start date
V

Virgil

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.
 
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).
 
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.
 
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.
 
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


Kevin McKinnerney said:
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.

Allen Browne said:
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.
Click to expand...
Click to expand...
 
Back
Top