Controlling Make-table data types

[jclark33]

I have created a make-table query that pulls vendors from the current month, prev month, and three months past. I am using another query to pull from that the current top ten and do some calculations based on the past data. My problem is this when the make-table querie runs, it generates a field I need to sum, with a text-datatype, and i get an aggregate function error. Can I control the datatypes with in the table the query generates?

thanks

 

[Pat Hartman]

It is generating the field as text because something you are doing in the query is causing it. Are you formatting the field? That would make it text.

 

[jclark33]

figured it out, i just ran a delete qry then appended whats necessary.

thanks for the help

 

[akt01]

Is there an answer to the original question:

Is it possible to program a make-table query to define the datatypes for the fields in the new table?

If not, is it possible to write code to change field types and sizes?

Thanks for any help

 

[Pat Hartman]

Sometimes all you need to do is to format the field in the query by using the appropriate conversion function such as CDate(), CLng(), CDbl(), etc. There are two other ways also. You can use DAO and the tabledefs collection. Look up collections in help for code examples. The final way is to use DDL. To find out about DDL, open Access help to the table of contents and search for the section on SQL. The part of SQL that we are most familiar with is called DML (data manipulation language). That is the select and action queries that we use all the time. DDL (data definition language) is the part of SQL that is used to create or modify table structures. Look for the "ALTER TABLE" to find examples of changing column definitions.

 

[FISHiEE]

That solved my problem also. Cheers for that Pat!