Create new date column

[hazel4832]

I currently have a table in MS Access, which is an import from another
program. It has the column 'Date of Confirmed Corrective Action
Closure', which goes in as text. The dates in this field are formatted
as 'MMM-DD-YYYY', with MMM being the first 3 letters of the month. I
an trying to basically convert this into a date. I have an ALTER query
that adds the column Closed Date to the Table and sets it as type Date/
Time. I have created a function that converts the MMM into a number.

Public Function StrToMonth(strIn As String) As Integer
Dim arrMonth(12) As Variant
Dim i As Integer

arrMonth(0) = "JAN"
arrMonth(1) = "FEB"
arrMonth(2) = "MAR"
arrMonth(3) = "APR"
arrMonth(4) = "MAY"
arrMonth(5) = "JUN"
arrMonth(6) = "JUL"
arrMonth(7) = "AUG"
arrMonth(8) = "SEP"
arrMonth(9) = "OCT"
arrMonth(10) = "NOV"
arrMonth(11) = "DEC"

For i = 0 To UBound(arrMonth) - 1
If strIn = arrMonth(i) Then
StrToMonth = i + 1
Exit Function
End If
Next i
End Function

I am trying to create and UPDATE query that calls this function in
order to get the Month number.

UPDATE Environmental SET Environmental.ClosedDate = DateSerial(Val(Mid
([Date of Confirmed Corrective Action Closure],8,4)),monthname,Val(Mid
([Date of Confirmed Corrective Action Closure],5,2)));

I also have monthname:StrToMonth('Date of Confirmed Corrective Action
Closure')

I am getting errors. It doesn't even look as itf it is getting tot he
function part of the query.

Could anyone assist me with this?

Thanks.

 

[KARL DEWEY]

Try doing it the easy way.
CDate([of Confirmed Corrective Action Closure])

 

[hazel4832]

Try doing it the easy way.
          CDate([of Confirmed Corrective Action Closure])
--
Build a little, test a little.

I currently have a table in MS Access, which is an import from another
program. It has the column 'Date of Confirmed Corrective Action
Closure', which goes in as text. The dates in this field are formatted
as 'MMM-DD-YYYY', with MMM being the first 3 letters of the month. I
an trying to basically convert this into a date. I have an ALTER query
that adds the column Closed Date to the Table and sets it as type Date/
Time. I have created a function that converts the MMM into a number.

Public Function StrToMonth(strIn As String) As Integer
Dim arrMonth(12) As Variant
Dim i As Integer

arrMonth(0) = "JAN"
arrMonth(1) = "FEB"
arrMonth(2) = "MAR"
arrMonth(3) = "APR"
arrMonth(4) = "MAY"
arrMonth(5) = "JUN"
arrMonth(6) = "JUL"
arrMonth(7) = "AUG"
arrMonth(8) = "SEP"
arrMonth(9) = "OCT"
arrMonth(10) = "NOV"
arrMonth(11) = "DEC"

For i = 0 To UBound(arrMonth) - 1
    If strIn = arrMonth(i) Then
        StrToMonth = i + 1
        Exit Function
    End If
Next i
End Function

I am trying to create and UPDATE query that calls this function in
order to get the Month number.

UPDATE Environmental SET Environmental.ClosedDate = DateSerial(Val(Mid
([Date of Confirmed Corrective Action Closure],8,4)),monthname,Val(Mid
([Date of Confirmed Corrective Action Closure],5,2)));

I also have monthname:StrToMonth('Date of Confirmed Corrective Action
Closure')

I am getting errors. It doesn't even look as itf it is getting tot he
function part of the query.

Could anyone assist me with this?

Thanks.

Wish I had known about that earlier.
I am getting an error with that also, though. It says:
Microsoft Access didn't update 2 fields due to a type conversiion
failure, 0 records due to key violations, 0 record(s) due to lock
violations and 0 record(s) due to validation rule violations.
I've double checked to make sure CloseDate is type Date/Time.
Any ideas why this would be?

 

[John Spencer]

Try using

IIF(IsDate([Date of Confirmed Corrective Action Closure])
,CDate([Date of Confirmed Corrective Action Closure]),Null)

Or in the update query you can use

UPDATE YourTable
SET RealDateField = CDate([Date of Confirmed Corrective Action Closure])
WHERE IsDate([Date of Confirmed Corrective Action Closure]) = True

John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County

Try doing it the easy way.
CDate([of Confirmed Corrective Action Closure])
--
Build a little, test a little.

I currently have a table in MS Access, which is an import from another
program. It has the column 'Date of Confirmed Corrective Action
Closure', which goes in as text. The dates in this field are formatted
as 'MMM-DD-YYYY', with MMM being the first 3 letters of the month. I
an trying to basically convert this into a date. I have an ALTER query
that adds the column Closed Date to the Table and sets it as type Date/
Time. I have created a function that converts the MMM into a number.
Public Function StrToMonth(strIn As String) As Integer
Dim arrMonth(12) As Variant
Dim i As Integer
arrMonth(0) = "JAN"
arrMonth(1) = "FEB"
arrMonth(2) = "MAR"
arrMonth(3) = "APR"
arrMonth(4) = "MAY"
arrMonth(5) = "JUN"
arrMonth(6) = "JUL"
arrMonth(7) = "AUG"
arrMonth(8) = "SEP"
arrMonth(9) = "OCT"
arrMonth(10) = "NOV"
arrMonth(11) = "DEC"
For i = 0 To UBound(arrMonth) - 1
If strIn = arrMonth(i) Then
StrToMonth = i + 1
Exit Function
End If
Next i
End Function
I am trying to create and UPDATE query that calls this function in
order to get the Month number.
UPDATE Environmental SET Environmental.ClosedDate = DateSerial(Val(Mid
([Date of Confirmed Corrective Action Closure],8,4)),monthname,Val(Mid
([Date of Confirmed Corrective Action Closure],5,2)));
I also have monthname:StrToMonth('Date of Confirmed Corrective Action
Closure')
I am getting errors. It doesn't even look as itf it is getting tot he
function part of the query.
Could anyone assist me with this?
Thanks.

Wish I had known about that earlier.
I am getting an error with that also, though. It says:
Microsoft Access didn't update 2 fields due to a type conversiion
failure, 0 records due to key violations, 0 record(s) due to lock
violations and 0 record(s) due to validation rule violations.
I've double checked to make sure CloseDate is type Date/Time.
Any ideas why this would be?

 

[John W. Vinson]

I am getting an error with that also, though. It says:
Microsoft Access didn't update 2 fields due to a type conversiion

It's probably a typo like "FEB-30-2001" or "MAU-15-2005" - something that
Access cannot recognize as a date. To find the two records create a query with
a calculated field IsDate([fieldname]) with a criterion of False, and correct
the erroneous data.