type conversion failure

  • Thread starter Thread starter Elsie
  • Start date Start date
E

Elsie

Hi all,

I am trying to append a query to a table. Both the query and table have the
same number of fields. But I got a type conversion failure when I try to
append.

After some testing, found that inv_num and do_num are the ones causing the
problems. AFAIK, both do_num and inv_num are string type.

For do_num, I am trying to use Val(do_num). But this only works for those
deliver orders that are numeric... if alpha numeric do_num, this does not
work.

For inv_num, I tried Val(inv_num), Str(inv_num) and Mid(inv_num), but all
don't work....

INSERT INTO Sales_Report_YD ( goods, do_num, inv_num, item, description,
product_code, cust_num, cust_seq, county, country, name, cust_type,
qty_invoiced, price, cost, curr_code, inv_date, eff_date, sell_rate,
SalesAMT, CostAMT )
SELECT Sales_Report_M0410.goods, Val([do_num]) AS do,
Sales_Report_M0410.inv_num, Sales_Report_M0410.item,
Sales_Report_M0410.description, Sales_Report_M0410.product_code,
Sales_Report_M0410.cust_num, Sales_Report_M0410.cust_seq,
Sales_Report_M0410.county, Sales_Report_M0410.country,
Sales_Report_M0410.name, Sales_Report_M0410.cust_type,
Sales_Report_M0410.qty_invoiced, Sales_Report_M0410.price,
Sales_Report_M0410.cost, Sales_Report_M0410.curr_code,
Sales_Report_M0410.inv_date, Sales_Report_M0410.eff_date,
Sales_Report_M0410.sell_rate, Sales_Report_M0410.SalesAMT,
Sales_Report_M0410.CostAMT
FROM Sales_Report_M0410;
 
Elsie,

Have a look at the design view of the Sales_Report_YD table, and check
the Data Type of the do_num and inv_num fields. Whatever they are, you
will need to make sure these columns in the query are of compatible
type. If do_num in the table is Text, then Val([do_num]) in the query
doesn't seem right, because the Val() function returns a Number. Do you
mean that the Append Query works without error if you eliminate the
inv_num and do_num fields from the query? Is it both if them that are
producing an error? Have you had a look at the data returned by the
Sales_Report_M0410 query to see what it looks like?... this may give a
clue to the problem.
 
Hi Steve,

If both do_num and inv_num are removed from the append query, the append
query works fine....

I have looked up on this article
http://support.microsoft.com/default.aspx?scid=kb;en-us;302504...
and set the do_num and inv_num to be text instead of number and
allowzerolength to be 'Yes' and the required field to be 'No', it seems to
work now....
I just try this out and see it there are any problems down the road.

Thanks Steve!


Steve Schapel said:
Elsie,

Have a look at the design view of the Sales_Report_YD table, and check
the Data Type of the do_num and inv_num fields. Whatever they are, you
will need to make sure these columns in the query are of compatible
type. If do_num in the table is Text, then Val([do_num]) in the query
doesn't seem right, because the Val() function returns a Number. Do you
mean that the Append Query works without error if you eliminate the
inv_num and do_num fields from the query? Is it both if them that are
producing an error? Have you had a look at the data returned by the
Sales_Report_M0410 query to see what it looks like?... this may give a
clue to the problem.

--
Steve Schapel, Microsoft Access MVP

Hi all,

I am trying to append a query to a table. Both the query and table have the
same number of fields. But I got a type conversion failure when I try to
append.

After some testing, found that inv_num and do_num are the ones causing the
problems. AFAIK, both do_num and inv_num are string type.

For do_num, I am trying to use Val(do_num). But this only works for those
deliver orders that are numeric... if alpha numeric do_num, this does not
work.

For inv_num, I tried Val(inv_num), Str(inv_num) and Mid(inv_num), but all
don't work....

INSERT INTO Sales_Report_YD ( goods, do_num, inv_num, item, description,
product_code, cust_num, cust_seq, county, country, name, cust_type,
qty_invoiced, price, cost, curr_code, inv_date, eff_date, sell_rate,
SalesAMT, CostAMT )
SELECT Sales_Report_M0410.goods, Val([do_num]) AS do,
Sales_Report_M0410.inv_num, Sales_Report_M0410.item,
Sales_Report_M0410.description, Sales_Report_M0410.product_code,
Sales_Report_M0410.cust_num, Sales_Report_M0410.cust_seq,
Sales_Report_M0410.county, Sales_Report_M0410.country,
Sales_Report_M0410.name, Sales_Report_M0410.cust_type,
Sales_Report_M0410.qty_invoiced, Sales_Report_M0410.price,
Sales_Report_M0410.cost, Sales_Report_M0410.curr_code,
Sales_Report_M0410.inv_date, Sales_Report_M0410.eff_date,
Sales_Report_M0410.sell_rate, Sales_Report_M0410.SalesAMT,
Sales_Report_M0410.CostAMT
FROM Sales_Report_M0410;
Click to expand...
Click to expand...
 
Back
Top