#Error in select query

A

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
 
A

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
 
A

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
 
N

Neil Sunderland

Aivars said:
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.
 
A

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Top