Hi all,
Is it possible to run a macro that would open a table in design view and go change in a field the type of data? How is it done if possible?
Hi all,
Is it possible to run a macro that would open a table in design view and go change in a field the type of data? How is it done if possible?
Macro might be able to call an ALTER TABLE sql action. Why would you want to programmatically modify table design?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Hi VIP,
I'm trying to modify and existing database that an employee did a while back and left the company.
I have an original query that return pieces of various length of lumber. I did a table query from that query and the data in the field in the length of lumber is in text as data type.
Now I'm running a append query to change the length of the lumber to the next available size because we do not carry the length anymore.
IIF([Reliure_grosseur] In (8;10;11;12;14;17;18;19;20);[Reliure_grosseur];Switch([Reliure_grosseur] Entre 6 Et 7;8;[Reliure_grosseur]=9;10;[Reliure_grosseur]=13;14;[Reliure_grosseur] Entre 15 Et 16;17))
Give me a message conversion error
If the field type needs to be changed, would not need code to do that, just open table in design view and change data type. However, need more info. Open the table in design view and look at properties of the field. Is it number or text type? If it is text and you are saving number data (which it appears the case from your expression) then why is the data type not already number? If the field is text type then the data elements in your expression need apostrophe or quote delimiters.
Is this field saving the ID of records from a lookup table? Is that ID field a text type?
I would use comma instead of semi-colon but maybe is different in non-English database.
Instead of Entre 6 Et 7 could just use <8.
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
Hey, Thanks for replying!If the field type needs to be changed, would not need code to do that, just open table in design view and change data type. However, need more info. Open the table in design view and look at properties of the field. Is it number or text type? If it is text and you are saving number data (which it appears the case from your expression) then why is the data type not already number? If the field is text type then the data elements in your expression need apostrophe or quote delimiters.
Is this field saving the ID of records from a lookup table? Is that ID field a text type?
I would use comma instead of semi-colon but maybe is different in non-English database.
Instead of Entre 6 Et 7 could just use <8.
I will not be the only one using the database, therefore the need for automation from a button. The data is taken from a query that take it from a table. I am not willing to change things from existing. As I am "afraid of fucking it all up" sorry for the language!!!!
I have put the database as attachment so that you can look. The query that make the table is tablequery spirale and create the table Table spirale modifié.
Where would I put apostrophe or quote delimiters so that the below statement would be reconnize in text?
IIF([Reliure_grosseur] In (8;10;11;12;14;17;18;19;20);[Reliure_grosseur];Switch([Reliure_grosseur] Entre 6 Et 7;8;[Reliure_grosseur]=9;10;[Reliure_grosseur]=13;14;[Reliure_grosseur] Entre 15 Et 16;17))
None of the tables are local so there is no data to examine and test with. But try:
IIf([Reliure_grosseur] In ('8';'10';'11';'12';'14';'17';'18';'19';'20');[Reliure_grosseur];Switch([Reliure_grosseur] Entre '6' Et '7';'8';[Reliure_grosseur]='9';'10';[Reliure_grosseur]='13';'14';[Reliure_grosseur] Entre '15' Et '16';'17'))
Why are you programmatically creating and/or modifying tables?
I see tables in Relationships window but there are no links so relationships are not established.
I see [Event Procedure] in event properties but there is no VBA code. How did that happen?
How to attach file: http://www.accessforums.net/showthread.php?t=70301 To provide db: copy, remove confidential data, run compact & repair, zip w/Windows Compression.
These are add on (new query returning adjusted data and querytable) I dont want to change, add or remove from existing tables and queries. Trying to create from what is there.None of the tables are local so there is no data to examine and test with. But try:
IIf([Reliure_grosseur] In ('8';'10';'11';'12';'14';'17';'18';'19';'20');[Reliure_grosseur];Switch([Reliure_grosseur] Entre '6' Et '7';'8';[Reliure_grosseur]='9';'10';[Reliure_grosseur]='13';'14';[Reliure_grosseur] Entre '15' Et '16';'17'))
Why are you programmatically creating and/or modifying tables?
I see tables in Relationships window but there are no links so relationships are not established.
I see [Event Procedure] in event properties but there is no VBA code. How did that happen?
I will try and let you know.
As for the attachement it is probably corrupted!!!!
Thanks
Thank you VIP the quote delimiters worked perfectly.