Unlikely error msg from query

[RandomTask]

Query B gets its results from Query A and populates a form. This works fine.
Now I need to alter the WHERE clause in Query A, but when I do it results in
an error that doesn't make any sense to me.

First, the queries:

Query A
SELECT CUSIP.issuer_num, CUSIP.issue_num, CUSIP.issue_chk, CUSIP.issue_d1,
CUSIP.issue_d2, CUSIP.issue_a1, CUSIP.issue_a2, CUSIP.issue_a3,
CUSIP.issue_a4, CUSIP.issue_sta, CUSIP.dated_date, CUSIP.mat_date,
CUSIP.part_mat, CUSIP.rate, CUSIP.issue_del, CUSIP.issue_trn, CUSIP.update1,
CUSIP.update2, CUSIP.offer_amt, CUSIP.callable, CUSIP.underwrit, CUSIP.cusip,
CUSIP.cusip8, CUSIP.maturity, CUSIP.issuer_st, CUSIP.issuer_n1,
CUSIP.nxt_call, CUSIP.dated, CUSIP.frst_cpn, CUSIP.pymt_frq, CUSIP.coupon,
CUSIP.cpn_type, CUSIP.yr_update, ([maturity]-[dated])/365.25 AS t_term,
IIf([dated] Is Null,"",IIf([t_term]<0.97,Round([t_term]*365.25/30.5,0) & "
MO",Round([t_term],1) & " YR")) AS term, Round(([maturity]-[dated])/365.25,1)
AS t2_term, IIf([dated] Is
Null,"",IIf([t_term]<0.97,Val(Round([t_term]*365.25/30.5,0)),Val(Round([t_term],1)*100))) AS term_sort, Date()-CDate([update1]) AS days_sell
FROM CUSIP
WHERE (((CUSIP.callable)="N") AND ((CUSIP.dated)>=Now()) AND
((CUSIP.cpn_type)="Fixed Rate"))
ORDER BY CUSIP.maturity;


Query B
SELECT Val([term_sort]) AS srt, [CUSIP BulletB].term, [CUSIP BulletB].rate,
Count([CUSIP BulletB].term) AS CountOfterm, [rate]/100 AS rate2
FROM [CUSIP BulletB]
GROUP BY Val([term_sort]), [CUSIP BulletB].term, [CUSIP BulletB].rate,
[rate]/100
ORDER BY Val([term_sort]), [CUSIP BulletB].rate DESC;


I need to alter the 'where' clause in Query A to remove the criteria on the
'dated' field. However, if I make ANY changes to it I end up with a "Data
type mismatch in criteria expression" error. Even if all I do is change the >
sign to a < sign, like so:

WHERE (((CUSIP.callable)="N") AND ((CUSIP.dated) >=Now()) AND
((CUSIP.cpn_type)="Fixed Rate"))

WHERE (((CUSIP.callable)="N") AND ((CUSIP.dated) <=Now()) AND
((CUSIP.cpn_type)="Fixed Rate"))

Yup; simply changing the sign results in "Data type mistmatch".

This makes zero sense to me and clearly Access is confused about something.
How can changing the sign possibly change the perceived data type?

I'm open to any suggestions here. I've already wasted like an hour trying to
figure this out.

 

[mscertified]

When you change SQL, you also change the internal representation of the query
and that can result in errors unrelated to what you changed. Sometimes just
putting in or removing parentheses can result in a similar thing. There is
something wrong with your query you just need to find it. Are you comparing a
string column with a number or a date with a non-date column?

-Dorian

 

[RandomTask]

Turns out that somewhere in that rats' nest of IIF's it was possible for one
of the grouping columns (in Query A) to come up as #ERROR.

Then when Query B tried to sort on that column, it saw it as a data mismatch
presumably because ERROR != a date.

Now, the more interesting question is: what is wrong with the internal query
representation where a small and completely irrelevant change (like a < sign
in the WHERE) will cause this error to be suppressed? Clearly there's a
problem that extends beyond user error.

When you change SQL, you also change the internal representation of the query
and that can result in errors unrelated to what you changed. Sometimes just
putting in or removing parentheses can result in a similar thing. There is
something wrong with your query you just need to find it. Are you comparing a
string column with a number or a date with a non-date column?

-Dorian

Query B gets its results from Query A and populates a form. This works fine.
Now I need to alter the WHERE clause in Query A, but when I do it results in
an error that doesn't make any sense to me.

First, the queries:

Query A
SELECT CUSIP.issuer_num, CUSIP.issue_num, CUSIP.issue_chk, CUSIP.issue_d1,
CUSIP.issue_d2, CUSIP.issue_a1, CUSIP.issue_a2, CUSIP.issue_a3,
CUSIP.issue_a4, CUSIP.issue_sta, CUSIP.dated_date, CUSIP.mat_date,
CUSIP.part_mat, CUSIP.rate, CUSIP.issue_del, CUSIP.issue_trn, CUSIP.update1,
CUSIP.update2, CUSIP.offer_amt, CUSIP.callable, CUSIP.underwrit, CUSIP.cusip,
CUSIP.cusip8, CUSIP.maturity, CUSIP.issuer_st, CUSIP.issuer_n1,
CUSIP.nxt_call, CUSIP.dated, CUSIP.frst_cpn, CUSIP.pymt_frq, CUSIP.coupon,
CUSIP.cpn_type, CUSIP.yr_update, ([maturity]-[dated])/365.25 AS t_term,
IIf([dated] Is Null,"",IIf([t_term]<0.97,Round([t_term]*365.25/30.5,0) & "
MO",Round([t_term],1) & " YR")) AS term, Round(([maturity]-[dated])/365.25,1)
AS t2_term, IIf([dated] Is
Null,"",IIf([t_term]<0.97,Val(Round([t_term]*365.25/30.5,0)),Val(Round([t_term],1)*100))) AS term_sort, Date()-CDate([update1]) AS days_sell
FROM CUSIP
WHERE (((CUSIP.callable)="N") AND ((CUSIP.dated)>=Now()) AND
((CUSIP.cpn_type)="Fixed Rate"))
ORDER BY CUSIP.maturity;


Query B
SELECT Val([term_sort]) AS srt, [CUSIP BulletB].term, [CUSIP BulletB].rate,
Count([CUSIP BulletB].term) AS CountOfterm, [rate]/100 AS rate2
FROM [CUSIP BulletB]
GROUP BY Val([term_sort]), [CUSIP BulletB].term, [CUSIP BulletB].rate,
[rate]/100
ORDER BY Val([term_sort]), [CUSIP BulletB].rate DESC;


I need to alter the 'where' clause in Query A to remove the criteria on the
'dated' field. However, if I make ANY changes to it I end up with a "Data
type mismatch in criteria expression" error. Even if all I do is change the >
sign to a < sign, like so:

WHERE (((CUSIP.callable)="N") AND ((CUSIP.dated) >=Now()) AND
((CUSIP.cpn_type)="Fixed Rate"))

WHERE (((CUSIP.callable)="N") AND ((CUSIP.dated) <=Now()) AND
((CUSIP.cpn_type)="Fixed Rate"))

Yup; simply changing the sign results in "Data type mistmatch".

This makes zero sense to me and clearly Access is confused about something.
How can changing the sign possibly change the perceived data type?

I'm open to any suggestions here. I've already wasted like an hour trying to
figure this out.