Field Created by Query won't be anything but "Binary"

[jgraves]

I am using Access 2003. I am using the "Make Table" query to create a new
table that contains records from an existing table plus creating some new
fields using the "NewField:Null" syntax in the design view of the query
writer.
I have also written update queries to be run after the make table query to
update these new fields.
Problem is, I am trying to update the new fields with a mathematical
equation. Since the field defaults to "Binary" as a data type for the field
created, I get garble in the new field after I run this update query. (Looks
to me like it could be Asian characters) If I change the field manually after
the make-table query runs, then no problem, it updates fine.
I am looking to automate this whole process and would like to avoid the
manual intervention of having to change the field data types.

Anyone know if it is possible to specify the data type of the new field
within the make table query?

 

[John Spencer]

You are really better off not using a make table query. Design the
table in advance where you can guarantee the data types.

You can have a dummy table with no records that you copy
programmatically to a new name and then append to it.
Or
You can delete all records from a pre-designed table and then append the
new records.

If you don't want to or can't for some reason, you could try the
following techniques - they should work.


NewField: IIF(False,CDbl(1),Null)

NewField: IIF(False,"A",Null)

NewField: IIF(False,CDate(0),Null)



'====================================================
John Spencer
Access MVP 2002-2005, 2007
Center for Health Program Development and Management
University of Maryland Baltimore County
'====================================================

 

[jgraves]

John, Both solutions you suggested are excellent. I am redesigning my queries
to use pre-built tables where I can, but there are still places because of
how much we customize databases for our customers that it is necessary and
the coding below works perfectly. Many many thanks.