data type for Make Table Query

  • Thread starter Thread starter Ted
  • Start date Start date
T

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
 
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
 
Back
Top