Create new date column

  • Thread starter Thread starter hazel4832
  • Start date Start date
H

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.
 
Try doing it the easy way.
          CDate([of Confirmed Corrective Action Closure])
--
Build a little, test a little.

hazel4832 said:
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.
Click to expand...
Public Function StrToMonth(strIn As String) As Integer
Dim arrMonth(12) As Variant
Dim i As Integer
Click to expand...
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"
Click to expand...
For i = 0 To UBound(arrMonth) - 1
    If strIn = arrMonth(i) Then
        StrToMonth = i + 1
        Exit Function
    End If
Next i
End Function
Click to expand...
I am trying to create and UPDATE query that calls this function in
order to get the Month number.
Click to expand...
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)));
Click to expand...
I also have monthname:StrToMonth('Date of Confirmed Corrective Action
Closure')
Click to expand...
I am getting errors. It doesn't even look as itf it is getting tot he
function part of the query.
Click to expand...
Could anyone assist me with this?
Click to expand...
Thanks.
Click to expand...
Click to expand...

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

hazel4832 said:
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.
Click to expand...
Click to expand...

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?
Click to expand...
 
I am getting an error with that also, though. It says:
Microsoft Access didn't update 2 fields due to a type conversiion
Click to expand...

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.
 
Back
Top