changing field type in code?

[AndrewWolford]

Hi all,

I have a sub routine that takes a date from a field in a table and converts it to the proper date format and copies it back to the field. My date comes in this format: "20031231" and gets copied back as "12/31/2003" The field is dbText. here is the code I have:

Do Until rst2.EOF
tmpDate = rst2.Fields(TDarray(2, i))
If tmpDate <> 0 Then
fxdDate = Mid(tmpDate, 5, 2) & &quot;/&quot; & Mid(tmpDate,7,2)
& &quot;/&quot; & Left(tmpDate, 4)
Else
fxdDate = 0
End If
rst2.Edit
rst2.Fields(TDarray(2, i)).Value = fxdDate
rst2.Update
rst2.MoveNext
Loop
rst2.Fields(TDarray(2, i)).Type = dbDate 'this is where I'm
'trying to change the field type.


After I've copied the dates back in the correct format I want to change the field type to dbDate. I guess all I'm really asking is how can you change the field type from text to date in code? Is this possible?

Thanks for any help!

-Andrew

 

[TonyJollans]

Hi Andrew,

Just use a bit of SQL ..

CurrentDb.Execute "ALTER TABLE

TableName

 ALTER COLUMN

ColumnName

 DATE"

.. should do the trick, but any invalid dates will be deleted.

In 2000 you might have to do it a bit differently ...

Dim tjDb as DAO.Database
Set tjDb = CurrentDb
tjDb.Execute

etc

Enjoy,
Tony

 

[AndrewWolford]

Thanks Tony,

I couldn't get the sql to work. I tried executing your sql code in a query and it gave me a syntax error in the statement. Is there something missing in the above sql? Also, shouldn't there be a &quot;;&quot; after &quot;DATE&quot; Thanks for your help!

-Andrew

 

[TonyJollans]

Andrew,

You are, of course, correct that there should be a semi-colon at the end of the SQL.

I've just gone back and re-read your original post and I'm no longer sure what you are trying to do. Do you want to update your table data to be 'valid' dates and then change the table definition or do you want to do something to the recordset? If the latter I'm not sure you can.

Enjoy,
Tony

 

[AndrewWolford]

Dates get imported from a fixed-width text file looking like this:
DOH
20031231
19990514
20010425
00000000
20020918
00000000

My code loops through the table (DO until rst2.EOF) and reads in each date, manipulates the date(as text) and copies the date back to the field as:
DOH
12/31/2003
05/14/1999
04/25/2001
0
09/18/2002
0

The problem is the field is still a &quot;text&quot; field, not a &quot;date&quot; field. Now that I have the dates in the proper format I want to convert the whole field to be a date field. Also, where the date is 0, I don't care if those records end up being null. I hope this better explains what I am trying to do.

Is it going to be possible to convert the field type as I am trying to do?

Thanks,

-Andrew

 

[TonyJollans]

I understand what you are trying to do with the dates but not how your code snippet fits into your wholeprocess.

You can't change the recordset field's type as you show because it reflects the underlying table, and while you've got the recordset open you can't change the table definition.

If you have done with the recordset at the end of your loop then close it and execute the SQL I posted earlier. Assuming your code starts off something like ..

Dim tjDb As DAO.Database
Dim rst2 As DAO.Recordset

Set tjDb = CurrentDb
Set rst2 = tjDb.OpenRecordset("

TableName

", dbOpenDynaset)

Do Until rst2.EOF

 ' start of your loop

.. and has your loop here before ..

  rst2.Update
  rst2.MoveNext
Loop

 ' end of your loop

.. then add the following here ..

rst2.Close
Set rst2 = Nothing

tjDb.Execute "ALTER TABLE

TableName

 ALTER COLUMN " & TDarray(2, i) & " DATE;"

.. now re-open the recordset if you want
Enjoy,
Tony

 

[AndrewWolford]

Hmm, still doesn't work. I copied your sql statement and tried to run it as a query and it errored out giving me a syntax error. This is the sql statement I wrote:

ALTER TABLE testdata ALTER COLUMN dot DATE;

where testdata is the table Im modifiying and dot is my date field. I'm guessing that this is the reason why my field type isn't changing, there is something wrong with the sql statement. Am I missing anything in the above statement?

Thank you for your help!

Andrew

 

[TonyJollans]

What do you mean when you say you tried to run it as a query? You should be executing it in the code. If you're still stuck I will be back tomorrow.

Tony