Error: The field is too small to accept the amount of data you attempted to add.

[jwill]

I'm getting this error and I've done searches on it. I don't have:

• any memo fields in the tables queried
  
  
  
• DISTINCT in any of the queries

I do have a double data type in 1 of the tables queried (don't know if this is the culprit)

There are 3 queries involved

assoc_daily_prod_time_qry

SELECT tl.assoc_id, tl.log_date, Format((DateDiff('n',Time_In,Time_out)-((DateDiff('n',Nz(break_out_1,0),Nz(break_in_1,0))-DateDiff('n',Nz(break_out_2,0),Nz(break_in_2,0))))-Nz(qty_of_prod,0))/60,'##.00') AS prod_hours, DateDiff('n',Time_In,Time_out)-((DateDiff('n',Nz(break_out_1,0),Nz(break_in_1,0))-DateDiff('n',Nz(break_out_2,0),Nz(break_in_2,0))))-Nz(qty_of_prod,0) AS prod_mins
FROM (time_logs AS tl LEFT JOIN (SELECT * FROM assoc_prod WHERE code_id = 'WO')  AS ap ON (tl.assoc_id = ap.assoc_id) AND (tl.log_date = ap.ap_date)) INNER JOIN qp_assocs AS a ON tl.assoc_id = a.assoc_id;

assoc_daily_earned_prod_qry

SELECT assoc_id, ap_date, Format(Sum(60/uph*qty_of_prod),'##.00') AS earned_prod
FROM assoc_prod AS ap INNER JOIN prod_codes AS pc ON ap.code_id = pc.code_id
WHERE (((ap.code_id)<>'WO'))
GROUP BY ap_date, assoc_id;

Query1

SELECT * FROM assoc_daily_prod_time_qry AS adpt INNER JOIN assoc_daily_earned_prod_qry AS adep ON (adpt.assoc_id = adep.assoc_id) AND (adpt.log_date = adep.ap_date);

The first 2 run fine, the 3rd one gives me the error. Anyone know why?

[ssanfu]

In a copy of the dB, I would try:

- checking the field size for text type fields. Set the size to 255.

- check the integer fields - change to long integers.

Error: The field is too small to accept the amount of data you attempted to add.

But I don't understand why you would get the error message. None of the Select queries are adding data to any table. Is theresomething else going on?

[jwill]

I maximized the text fields used in the query and it still didn't work. As for the doubles, I changed them to long integers and that didn't work either. I don't understand why the first 2 work perfectly fine and then when I try to join the 2 queries, there's an issue. There is nothing else going on everything I posted is it. I even compacted and repaired in case it was a corruption thing, but it will didn't work.

[ItsMe]

I was looking at it last night and still can not understand why you would get the error. Never seen the error before and searched it. Answers provided do not seem to relate to your situation.

[jwill]

Ok, I've narrowed it down. Access doesn't like my join on the assoc_id in "Query1". So it lets me join on the date but not on the assoc_id. The assoc Id is represented in both queries so, I'm not sure why its struggling. Anybody?

[jwill]

I tried changing the size of assoc_id in the tables to 255 and ran the query again and it still didn't work. I also tried pasting the objects into a new db and that didn't work either.

[ItsMe]

Maybe add assoc_id to your select for ap

SELECT *, ap.assoc_id FROM assoc_prod

Just a wild guess

[jwill]

Thanks for your help ITSME, I tried

SELECT  *, ap.assoc_id
FROM assoc_daily_prod_time_qry AS adpt INNER JOIN assoc_daily_earned_prod_qry AS adep ON (adpt.assoc_id = adep.assoc_id) AND (adpt.log_date = adep.ap_date);

It had more than 1 possible reference



So I tried....

SELECT  *, adep.ap.assoc_id
FROM assoc_daily_prod_time_qry AS adpt INNER JOIN assoc_daily_earned_prod_qry AS adep ON (adpt.assoc_id = adep.assoc_id) AND (adpt.log_date = adep.ap_date);

And it gave me the original error.

[ItsMe]

Cool......

[rpeare]

if you're still having problems with this (I can't tell based on the last post) could you post a sample of your database I'm curious about this because I've not encountered it before.

Secondly, I wouldn't set formats in your query using the FORMAT command, format tends to turn numbers into text values. Instead use the formatting available in the right click menu properties of the query design window.

[jwill]

SMH in astonishment, removing "FORMAT" did the trick. Thanks rpeare and everyone else that chimed in!!!!

[ItsMe]

That does make sense in retrospect. I was thinking more along the lines of explicitly describing each field in the SELECT statement.

[jwill]

@itsme

I think for some reason the amount of characters I allowed maybe wasn't sufficient. I used '##.00'. Maybe it should've been '####.00' or '##.##'. Who knows, Access is quirky sometimes. I'm just glad it works now.