Argh!

[dancerjodi]

Access update: I'm still fighting with it. These are the annoying little issues that seem like they should be so simple, and yet take hours to figure out.

So far I've:
Searched in Google
Searched in manuals
Searched the MS Access Help file
Asked an MS Access trainer
Asked a data analyst at Partners Health Care
Asked an IS person at Partners Health Care
Asked an old co-worker from THP
Asked a random stranger who found my LJ I'm guessing from someone on his friend's list.

This is just silly! It shouldn't be so difficult to change a text variable to a date.

Comments

No,

[dancer.livejournal.com]

That doesn't work (I just end up with a blank column). Making a new column, copying the data into it and changing *that* one doesn't either.

Neither does importing the data in with the variable initially set as a date (versus as text) :/

I do have data in their already - I'm not sure what you meant by:
"I am not sure what it may do if you have data in that field already but I can almost guarentee you it won't convert it."

Re: No,

[dancer.livejournal.com]

"When you convert a column from one data type to another, if they are not of similar data types (i.e. text -> varchar) the data in the original column will be lost. (i.e. text -> date). "

I know, hence my dilema :)

"Have you tried doing it with a SQL query? Create the column of data type date and then use the query to populate the column instead of a copy/paste?"

How do you create a table with sql (and then in another query, put data into it)? I think I'm missing something :)

Re: No,

[dancer.livejournal.com]

Thanks for all of your help with this - once I have some time to check these out I'll see if they work :)

[mizdarkgirl.livejournal.com]

What is the format of the date in the raw file?
I have had occasion in which I to play with the format before bringing into Microsoft.

Access issue

[dancer.livejournal.com]

"What is the format of the date in the raw file?"

Its in .txt format. I wish I could get it into another format, but its too big to open in Excel, and I don't have any software available to me that could convert it to a .dbf (which is what some of my old co-workers suggested I do).

"I have had occasion in which I to play with the format before bringing into Microsoft."

Yeah, its too bad I can't do that (since all I have to work with is Excel - that won't read in all of the rows).

[mizdarkgirl.livejournal.com]

Actually big problems if it is in DDMMMYY format
http://support.microsoft.com/default.aspx?scid=kb;en-us;131933
http://support.microsoft.com/default.aspx?scid=kb;EN-US;159323

I also found this
a) For more information about changing a field's data type, search for "data type conversion," and then "Changing a Field Data Type" using the Microsoft Access Help menu. For more information about formatting dates, search for "date formats," and then "Format Property - Date/Time Data Types" using the Microsoft Access Help menu.

b)For more information about changing a field's data type, search the Help Index for "data types, converting," or ask the Microsoft Access 97 Office Assistant. For more information about formatting dates, search the Help Index for "dates, formats," or ask the Microsoft Access 97 Office Assistant.

Thanks

[dancer.livejournal.com]

I'll take a look at this Access info and see if it helps, thanks :)