changing format of table field in macro

[esther-perkins]

Hi, Is there anyone out there who knows how to change the format of a table field in a macro? I have lots of macros which need to be halted in order to change the format of some table fields before I can proceed... Try though i might, I can't work out how to automate this process in a macro. Would be reeeely grateful for any advice on this.

 

[FoFa]

You can use SETVALUE to change a property of a field, if that is what you mean.

 

[esther-perkins]

Thanks for replying Fofa. I need to change the format of certain fields in a table, for example, change a text format to a date/time format. So yes, the format is a field property.

I looked at the SETVALUE macro action, and am not sure what to put down for the item or expression.

If my table is called MPI and the field I want to change from text to date/time is FIRSTCARE, what should I write for item and expression?

[MPI]![FIRSTCARE]![Format] ??


Any ideas?

Thanks for your help.

 

[esther-perkins]

sorry I mean field data type not format...

 

[FoFa]

You can't change the datatype. Why would you want to? Sounds like you need to use different fields, or use a string field and format the data into that. You see, the datatype for a column in a table applies to all data in that column. Not a date field for this row, number field for this row, string for this row, etc. They are all a xxx type field. Maybe you need to explain a little further.

 

[esther-perkins]

If not, that's bad news for us. I understand that the data type applies to the whole column....

For example, let's say I've got a make-table query which contains a function which outputs a text string when what I need for my end product is a date/time formatted field (I have returns to do at work which have such specific requirements)... Or a text field when what is required is a number field...

If this was just a small-scale project, I would just go to the design view of the table and change the data type manually, and then proceed to run the next queries, but this is something which is cropping up frequently, and would be good to straighten out within a macro...

 

[FoFa]

Maybe we are just about it the wrong way. It sounds like you are not putting the data in the same table (or other data elements would be invalidated), so what is the origin of the data, where is it going, and is it always in the same format?

 

[esther-perkins]

Dear Fofa,
Thanks for your reply... I understand that in an ideal world all the data types of the fields would stay the same and not need to be changed throughout the process, but I'll give you a sketch of my problem:

I use ODBC to extract data fields from a system which has been configured by a different company -- so I don't have any choice about the formats. One of the fields in question is a number field, which actually contains date data. In order to process this I need to do a make table query, then +24837 days while it is still in the Long Integer format, and then change the data type of the field so that it is date/time. As I mentioned earlier, I would like to automate this process in a macro but I have a feeling that I need to write VB code in order to do this...

Any advice?

 

[Pat Hartman]

I'm sure you don't want to change your process but for the future, you can link to the ODBC tables and rather than importing them you can use append queries that append rows from the ODBC linked table to a table with the correct column formats. Your queries can do calculations such as your date calculation and convert the results to a more normal looking date format that Access will accept.

To make structural changes to a table after the fact, you can use ADO or DAO or you can use DDL. DDL (data definition language) is a subset of SQL that contains the commands that manipulate data structures. Look up ALTER TABLE in help.

 

[esther-perkins]

Dear Pat!

Thanks so much for your solution -- I tried the ALTER TABLE and ALTER COLUMN statement as a Run SQL action in my macro and it worked like a charm!

Esther.