#Error in select query

[Aivars]

Hello,
I am getting #Error in VAL field when I do very simple query based on
qryKS3 query:

SELECT *
FROM qryKS3
WHERE round([SUMMA],2)>=0.01

here is qryKS3:

PARAMETERS [DATUMSLIDZ] DateTime;
select
"doc_parc" as DOC,
[DATUMSLIDZ] as DATUMS,
"mana_parc" as [TEXT],
IIF(Q2.EXCHDIF>=0,"82500",Q2.KONTS) as DEB,
IIF(DEB=Q2.KONTS,"81500",Q2.KONTS) as KRED,
0 as VSUM,
Q2.CURR1 as [VAL],
abs(Q2.EXCHDIF) as SUMMA,
Q2.RATE as KURS,
iif(DEB=Q2.KONTS,Q2.PKONTS,"") as PACCD,
IIF(KRED=Q2.KONTS,Q2.PKONTS,"") as PACCC
from qryKS2 as Q2

qryKS3 is based on qryKS2
and qryKS3 itself works as expected.

qryKS2 goes like this:
PARAMETERS [DATUMSLIDZ] DateTime;
SELECT
Q1.KONTS,
Q1.CURR1,
Q1.PKONTS,
sum(
Q1.VATL
) AS VSAL,
sum(
Q1.LATL
) AS LSAL,

(SELECT
Kurss
FROM
tblKURSI
WHERE
tblKURSI.Datums=[DATUMSLIDZ]
AND
tblKURSI.ValutasKods=CURR1) as RATE,
Nz(RATE,1) * VSAL AS PERSALATL, LSAL-PERSALATL as EXCHDIF
FROM qryKS1 as Q1 group by Q1.KONTS,Q1.CURR1,Q1.PKONTS



I am using Access 2003 and can be considered as newbee.

Thank you all very much

Aivars

 

[David F Cox]

VAL is an Access reserved word

 

[Aivars]

Thanks David,
Suspected that to be a problem. How to avoid it if I cannot change the
field name in the initial table (It's linked ODBC Interbase table).
Should I make an intermediary table, simply?
thanks
Aivars

 

[Aivars]

I changed VAL field to VALX and used query instead of table. Still I
get the same error. There is something else wrong...
Aivars

 

[David F Cox]

I am still looking, but TEXT is an Access and SQL reserved word too.

 

[Neil Sunderland]

I am getting #Error in VAL field when I do very simple query based on
qryKS3 query:

SELECT *
FROM qryKS3
WHERE round([SUMMA],2)>=0.01

Assuming that it might be a problem with Access Reserved words in the
source data, have you tried testing each field?

eg:
SELECT DOC
FROM qryKS3
WHERE round([SUMMA],2)>=0.01

Then:
SELECT DOC, DATUMS
FROM qryKS3
WHERE round([SUMMA],2)>=0.01

....and so on, until you isolate the errors.

 

[Aivars]

Thank Neil and David,

Finally I made a table from qryKS3 and did the WHERE clause not on
query but on this table. It worked like charm with NO errors
whatsoever. And it worked even with reserved words.
Are there any limits in Access as to how many inside queries there can
be? In my case the query SELECT * from qryKS3 WHERE round
(SUMMA,2)>=0.01 was fourth query (each with a parameter).
I have very experience with Access (coming slowly from Excel) so I am
confused. But this is maybe not the nicest solution but it works so
let it be.
Do not waste more of your time on this. It may be even a bug!! Who
knows.

Thanks
Aivars