format date field for imported table

[Jay]

I have a table imported from another table that has
a text date field. How can I format the imported
table date field to date format using vba I guess?

Date format for the new imported table field:
mm/dd/yy

 

[Allen Browne]

Use DAO to set (or create if necessary) the Format property of the Field in
the TableDef.

There's a function to do it here:
http://allenbrowne.com/AppPrintMgtCode.html#SetPropertyDAO

Usage example:
Call SetPropertyDAO(CurrentDb.TableDefs("Table1").Fields("Field1"),
"Format", dbText, "mm/dd/yy")

 

[Guest]

I am having a similar issue where I have to change a fields property from
text to short date.

In short, I have Access Tables linked to an external database (ODBC) which I
need to refresh with external data on demand. I use VBA to Delete the
existing Table, then to run the Make-Table Query keeping data current.

Problem I have is that the default Data Type for the Date, is Text. I can't
make the Make-Table Query save it as "Date/Time" even though the Format is
"mm/dd/yy". All the table data looks like a date yet...

I'd like to change the Data Type to Date/Time automatically after the table
is created.

I reviewed the Function attached and am having trouble getting it to work.
"VBA encountered problem attempting to access property or method."

I'm sure I'm not calling it correctly or not declaring everything? Being
that I am still an intermediate user, do you have any pointers on setting up
the Function and how to call it?

Save it as a Module? Leave it attached to the form (The Update button is
there)?

Thanks.

 

[Aaron Kempf]

Access Data Projects are _MUCH_ easier

when you update the table-- it shows up and you don't need to relink or
refresh anythnig

no connection strings

no hour-long queries


Access Data Projects made MDB obsolete 10 years ago

 

[John W. Vinson]

I am having a similar issue where I have to change a fields property from
text to short date.

In short, I have Access Tables linked to an external database (ODBC) which I
need to refresh with external data on demand. I use VBA to Delete the
existing Table, then to run the Make-Table Query keeping data current.

Problem I have is that the default Data Type for the Date, is Text. I can't
make the Make-Table Query save it as "Date/Time" even though the Format is
"mm/dd/yy". All the table data looks like a date yet...

I'd like to change the Data Type to Date/Time automatically after the table
is created.

I reviewed the Function attached and am having trouble getting it to work.
"VBA encountered problem attempting to access property or method."

I'm sure I'm not calling it correctly or not declaring everything? Being
that I am still an intermediate user, do you have any pointers on setting up
the Function and how to call it?

Save it as a Module? Leave it attached to the form (The Update button is
there)?

Rather than doing a MakeTable every time, create and save your local table
(with a Date/Time field). Run a Delete query to empty it, followed by an
Append query to repopulate it as appropriate.

John W. Vinson [MVP]

 

[Guest]

Thanks Aaron, Thanks John!

Unfortunately I am stuck with mdb for now.

I took the 'Append' advice. Why I didn't come to such a "logical"
conclusion on my own is depressing to me.

Thanks for the help, as always.