expression cannot be used as a criterium?

Giganews Newsgroups
Subject: expression cannot be used as a criterium?
Posted by:  Henrootje (Henr…@gmail.com)
Date: 1 Aug 2006

Hello folks,

I have a groupBy query, in one of the columns (SNISNU_KWARTAAL)  there
is a textfield.
I have an expression that deducts (from a numeric field) another
expression that has the same shape as the values in SNISNU_KWARTAAL.
I want that expression used as the criterium on the textfield but it
then gives me no results!
I made a new column (Kwartaal) with the expression  that shows the
computed value.
If I put in "2006Q1" manually as an expression in either
SNISNU_KWARTAAL or KWARTAAL things work fine.
Why can I not use the expression as a criterium then?

SNISNU_KWARTAAL = 2006Q1
SNISNU_Kwartaal_UI = 61

the SQL Syntax not working

SELECT qrySNISNU1.SNISNU_CLNR, qrySNISNU1.SNISNU_KWARTAAL,
2000+Left([SNISNU_Kwartaal_UI],1) & "Q" & Right([SNISNU_Kwartaal_UI],1)
AS Kwartaal, qrySNISNU1.SNISNU_SYSTEEM, Sum(qrySNISNU1.SNISNU_AANTAL)
AS SomVanSNISNU_AANTAL, qrySNISNU1.SNISNU_Kwartaal_UI
FROM qrySNISNU1
GROUP BY qrySNISNU1.SNISNU_CLNR, qrySNISNU1.SNISNU_KWARTAAL,
qrySNISNU1.SNISNU_SYSTEEM, qrySNISNU1.SNISNU_Kwartaal_UI
HAVING
(((qrySNISNU1.SNISNU_KWARTAAL)="2000"+Left([SNISNU_Kwartaal_UI],1) &
"Q" & Right([SNISNU_Kwartaal_UI],1)) AND
((qrySNISNU1.SNISNU_SYSTEEM)="SNU"));

the SQL Syntax working

SELECT qrySNISNU1.SNISNU_CLNR, qrySNISNU1.SNISNU_KWARTAAL,
2000+Left([SNISNU_Kwartaal_UI],1) & "Q" & Right([SNISNU_Kwartaal_UI],1)
AS Kwartaal, qrySNISNU1.SNISNU_SYSTEEM, Sum(qrySNISNU1.SNISNU_AANTAL)
AS SomVanSNISNU_AANTAL, qrySNISNU1.SNISNU_Kwartaal_UI
FROM qrySNISNU1
GROUP BY qrySNISNU1.SNISNU_CLNR, qrySNISNU1.SNISNU_KWARTAAL,
qrySNISNU1.SNISNU_SYSTEEM, qrySNISNU1.SNISNU_Kwartaal_UI
HAVING (((2000+Left([SNISNU_Kwartaal_UI],1) & "Q" &
Right([SNISNU_Kwartaal_UI],1))="2006Q1") AND
((qrySNISNU1.SNISNU_SYSTEEM)="SNU"));

Thanks for bearing and thinking with me

Replies