Access2000: "This field is too small..." error in query

[Arvi Laanemets]

Hi

I have a query
qUserDevices1:
SELECT [qUserDevices0].[QueryDate], _
[qUserDevices0].[DeviceID],
LEFT([qUserDevices0].[DeviceID],2) AS DevGroup, _
[qUserDevices0].[TransactDate], _
[tblTransactions].[TabN], _
ValidValue("ctbUserDep",_
"TabN",_
[tblTransactions].[TabN],_
True,_
"DepID",_
"ValidFrom",_
[qUserDevices0].[QueryDate]) AS DepID, _
DLookup("OutlayCode",_
"tblDepartments",_
"DepID = '" & _
ValidValue("ctbUserDep",_
"TabN",_
[tblTransactions].[TabN],_
True,_
"DepID",_
"ValidFrom",_
[qUserDevices0].[QueryDate]) _
& "'") AS OutlayCode
FROM tblTransactions _
RIGHT JOIN qUserDevices0 _
ON ([tblTransactions].[DeviceID]=[qUserDevices0].[DeviceID]) _
AND
([tblTransactions].[TransactDate]=[qUserDevices0].[TransactDate]);


I'm trying to apply a filter to this query. When I apply filter to fields
OutlayCode, DepID, or DevGroup, it all works fine.
To avoid too long expressions in where clause, I created for testing an
additional query qUserDevices, where all fields from qUserDevices1 are
selected. P.e. filtering by field DevGroup works OK.
SELECT qUserDevices1.QueryDate, _
qUserDevices1.DeviceID, _
qUserDevices1.DevGroup, _
qUserDevices1.TransactDate, _
qUserDevices1.TabN, _
qUserDevices1.DepID, _
qUserDevices1.OutlayCode
FROM qUserDevices1
WHERE (((qUserDevices1.OutlayCode)='400'));

When I apply the filter to field TabN, then there will be no error when the
Tabn value used in WHEN clause is missing in result table - and nothing is
returned of-course. Whenever Tabn value used as filter condition in WHEN
clause must be present in result table, an error message "The field is too
small to accept the amount of data you attempted to add. Try inserting or
pasting less data." appears. P.e. this query below returns such an error:
SELECT qUserDevices1.QueryDate, _
qUserDevices1.DeviceID, _
qUserDevices1.DevGroup, _
qUserDevices1.TransactDate, _
qUserDevices1.TabN, _
qUserDevices1.DepID, _
qUserDevices1.OutlayCode
FROM qUserDevices1
WHERE (((qUserDevices1.TabN)='505'));

PS. tblTransactions.TabN is a text field (field size 10), and contains
values like "3", "1111", "9999999999", or "Xxxx0000". I googled for this
error, and there were some articles where this error was linked with having
memo fields in result table - but this isn't the case here.



Thanks in advance for any assistance!

 

[Arvi Laanemets]

Hi

I did find a solution!
SELECT qUserDevices1.QueryDate, _
qUserDevices1.DeviceID, _
qUserDevices1.DevGroup, _
qUserDevices1.TransactDate, _
qUserDevices1.TabN, _
qUserDevices1.DepID, _
qUserDevices1.OutlayCode
FROM qUserDevices1
WHERE (((CStr(qUserDevices1.TabN))='505'));

I have no clue, why I must convert the string to string, but it works ))