overflow error

[emir]

Hello, I'm a beginner in access and i have a problem when i start query because it show me an overflow error. In SQL view my query looks:



INSERT INTO data([Index], Index2, Index3, Index4, [Month], DatumAkt, collectorid, Team, AccsWorked, Bucket, PTPno, PTP_Rate, Taken_Amt, RPC, RPC_Rate, Connects, Connect_Rate, talktime, wraptime, breaktime)
SELECT DISTINCT CLng([DatumAkt])&"_"&Trim([collectorid]) AS [Index], CLng([Month])&"_"&Trim([collectorid]) AS Index2, CLng([DatumAkt])&"_"&[Bucket]&"_"&Trim([collectorid]) AS Index3, CLng([Month])&"_"&[Bucket]&"_"&Trim([collectorid]) AS Index4, CDate(Format([DatumAkt],"mm\yyyy")) AS [Month], q_kontakt_sum.DatumAkt, q_kontakt_sum.collectorid, q_kontakt_sum.Team, q_kontakt_sum.AccsWorked, q_kontakt_sum.Bucket, q_kontakt_sum.PTPno, [PTPno]/[RPC] AS PTP_Rate, q_kontakt_sum.Taken_Amt, q_kontakt_sum.talktime, q_kontakt_sum.wraptime, q_kontakt_sum.breaktime
FROM q_kontakt_sum;

Can someone please write the correct query in SQL?
Thanks in advance.

[ItsMe]

I was going to guess it is one of your Clng's. However, your CDate looks suspect. It is going to need a valid Date format. mm/dd/yyy

It seems like what you are trying to do is extract a month from a date. This would be a different function. Maybe Month()

What is the data type for DatumAkt, Text?

[emir]

Yes, data type for DatumAkt is text. I think that i need add on the all columns Clng or CDate or some of this things but I don't know write the correct query.

[ssanfu]

Also, be aware that "Index" and "Month" are reserved words and shouldn't be used for object names.
Plus, "Index" and "Month" aren't very descriptive.


Basic troubleshooting (no pun intended):
Start with one field for the select query.

SELECT DISTINCT CLng([DatumAkt])&"_"&Trim([collectorid]) AS [Index] FROM q_kontakt_sum;

If that executes properly (no error), then add 1 field:

SELECT DISTINCT 
CLng([DatumAkt])&"_"&Trim([collectorid]) AS [Index], 
CLng([Month])&"_"&Trim([collectorid]) AS Index2
FROM q_kontakt_sum;

Keep adding 1 field until you determine which field causes the error.
Once you determine which field is causing the error, you can fix it and move on to the next error (if any).

[NTC]

I would suggest you split the query into 2 queries. Make the first query just a Select Distinct query - and in that do all of your formatting tasks. Then make a second query, using the 1st query as its source, and do just the Update/Append function. It is far easier to debug using query objects rather that an sql string in visual. If ultimately you need the sql syntax you can put the query object into sql view.

[emir]

thanks people, i resolve this problem...