programatically altering the field size property of a number field. 1

[jackal63]

I have a make table query in my database that makes a table called "footage". When that table is created it contains a field called "docket", which is a four digit code number. Access makes this "docket" field a number with a field size of "double". In order to run a query on this made table, that matches against the "docket" field, I have to go into design mode for the table "footage" and change the field size property to "decimal" with a precision of "4". Obviously I cannot distribute this report to users until it is a one button process.

Does anybody know some code I could squeeze between the two queriers, to automatically change the field size of field "docket" to decimal with a precision of "4" ??

Or if I could ensure that "docket" would be given a field size of "decimal" with precision of "4", while the table was being created by my first query, that would be really cool too.

Thanks in advance

 

[DataChick]

If you need to define the size of the fields - I wouldn't use a make table query. I would design the table and run two queries - a delete query to remove all data from the table and an append query to add the new records (just change your make table query to an append query).

This way you are always using the new data (like from the make table query) but your field settings are not left to the discretion of the program.

Then, when you write your code (or macro) for the button to run the process, just run the delete query then the append query - all one step for end users.

Hope this helps.

Let me know if you have any other questions.

L. "The most likely way for the world to be destroyed, most experts agree, is by accident. That's where we come in; we're computer professionals. We cause accidents."
-Nathaniel Borenstein

 

[jackal63]

thanks, that was a great idea. It worked simply enough, i wrapped the code in a "docmd.setwarning = False/True" set of statements, just to get rid of all the clutter of, "your deleting fields", "your appending fields" and so forth. After I got this to work, I noticed a small descrepancy in the report, each time it was run. Sometimes one of the queries would miss a record somewhere. One of the headaches in dealing with ODBC and FoxPRO, it seems. Anyway, I don't think that has anything to do with the fix you gave me. Thanks again.