Modify the format of a field in a table using sql

[padinka]

How do I write code to modify a date/time field to a date/time field with a "short date" format using VBA or sql?


Trisha
padinka@yahoo.com

 

[kathryn]

Do you want to change the format of the field in a table, or in a form or report?

In a form or report, just use the Format function. Create an unbound textbox and set its data source to Format(me!YourFieldName,"mm/dd/yyyy&quot or whatever format you want.

I'm not sure that this is what you are looking for, but I hope it helps.

Kathryn

 

[MichaelRed]

if you want to change the field/table characteristics - AND need to specifically use VBA/SQL, it is an "ALTER TABLE" statement which can only be generated in code or under the "datadefinition" query type menu (In Ms. Access). I know of NO useful purpose for doing this, as Ms. Access will still maintain the date/time type, which will include the storage for the time portion (fraction of the day). Any/all display of the value not only may - but MUST also be set to the short date format to show only the date anyway.

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[padinka]

First, Kathryn & Michael, thanks for responding to my thread.

Katheryn--Unfortunately it's not what I was looking for but thanks for trying to help me.


Michael Red:
I do need to change the field table characteristics for export purposes. These tables are data dumps that I receive from a Sybase database extract and start each month with a brand new set of data. I import a module into this database.

My module is currently exporting in a date/time format that I do not want for it to export. (There are 50+ exports made on this data). The easiest way to export correctly is to make the formatting change on the table rather than on the queries that do the extractions. However, there are many tables and fields and I am using code to do all the other modifications (indexing, etc.)to this database as well as the exporting and would like to make this change at the table level prior to the exports.

Got a piece of code that might do it? I couldn't figure out how to get the ALTER TABLE to do it.

Thanks
Trisha

Trisha
padinka@yahoo.com

 

[MichaelRed]

Trisha,

I haven't used the "Alter Table" construct in a while, and do not have an example. In a (brief) review of my lit., I note that it cannot be used to change the TYPE for a field. Apparently, you could add a new field with a constriant clause:

Alter Table tablename ADD NewField Date Constraint Short date

Then, export the NewField instead of the original field.

HOWEVER, rember that Ms. Access will STILL have the field as a date/time type and in a query/Export, it will retain the time (all the same - Midnight). The ONLY way I know to do this is to modify the queries to have the date values in the short date format:

MyDate: Format([[i]DateField[/i]], "Short Date") [code] as a column in the (query to) export.

I have not done this in a while, so Ms. Access may have changed some of hte rules.  If something here doesn't work as you expect, reply in the thread, or contact kathryn or myself.  If it appears "close" (but no cigar), let us know where the issues confound you.

 

MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over