Changing the field type from tx to date in Macros

[annied]

Hi,

I had a tx field (that should have been a date field) it read 20000101 I’ve
separated the tx in a qry and brought it back together in the right order 01
/01/2000 but it is still registering as a tx field,… i could simply go in an
d change the format of the field in the table design, but i have hundreds of
tables and thousands of these tx fields that should be dates. I need to wri
te a macro to take my updated field (01/01/2000) and format it in to date/ti
me.

Can anyone help me out with this one, because i'm v-new to VBA and macros in
general. Any help would be gratefully appreciated.

Annie

 

[Ken Snell [MVP]]

Add a new date/time field to the table.
Run an update query that uses the data from the tx field to write the date
into the new date field.
Delete the tx field from the table.

 

[annied]

Thanks Ken...thats the quick fix method, but i need to put something longer
tern in to action, so that i dont have to add fields to the hundreds of
tables i working with, (on a weekly basis)... i'm basically going to build a
structre table, and append my updated format in to it, ....then i'll run that
for all my date fields/tables in one macro. But cheers for that. ;-)

 

[Ken Snell [MVP]]

If you're importing data, use the import process to convert a text string
into a date value and then write it into a date/time field.

Perhaps I'm not understanding the dilemma and question that you have? Are
you looking for something to be done as data come to you? or to fix existing
data? or to change data as you export the data to something else?

--

Ken Snell
<MS ACCESS MVP>

 

[Ken Snell [MVP]]

If you want to get a date value from a text string of a date:
DateValue("12/23/2004")

or
CDate("12/23/2004")

--

Ken Snell
<MS ACCESS MVP>