data type for Make Table Query

[Ted]

Hi all,

I have a Make Table Query. How do I control the data type that comes from a
Make Table Query? I need the data type of the table to be a number but it
keeps coming in as text. The data type of the underlying table is text and i
can't change that. i'm using the CStr function but it gives me an error. I'm
guessing bc some of the fields are blank.

i've tried
Rating Modification Factor: CSng([RatingModificationFactor])
but i get errors bc of the null values

i've tried
Rating Modification Factor: IIf(Not
(IsNull([RatingModificationFactor])),CSng([RatingModificationFactor]),[RatingModificationFactor])
but it still comes up as a Text field in the new table

i've also tried
Rating Modification Factor: CSng(nz([RatingModificationFactor]))
it works but it forces a 0 in all of the blank fields

any help would be very much appreciated
TIA
Ted

 

[Jeff Boyce]

Ted

When you use the Make Table query, Access gets to "decide" what the data
types are.

If you want to end up with a table full of data in the data types you
decide, here's another way to get there:

* create a table with the fields and data types you want
* create a query that deletes any/all rows from that table
* create a query that "loads" that table (this would be your "Make Table"
query converted to an "Append" query, designating the fields)

Regards

Jeff Boyce
Microsoft Office/Access MVP