C
Christopher Caswell
Hi,
I've got a dlookup column in an underlying query on a datasheet subform.
This dlookup is represented on the form by a combo box. when I'm displaying
the form on which the datasheet resides, I can filter/sort by any field on
the form but this one. when I attempt filter/sort, I get "type mismatch in
expression", which, upon selecting help, states "Type mismatch in JOIN
expression. (Error 3615) A JOIN expression is attempting to join two tables
on fields of incompatible data types. For example, you will get this error
if you attempt to join a Memo field with a Text field."
However, when I take the actual record source and paste it into a query
window, I can sort/filter by the dlookup column. The only missing variable
here is that naturally it's not in combo box form when displayed in query
form. If I didn't have other combo boxes in my datasheet subform, I'd think
this was the problem (they're all filter/sort compatible), but maybe the
combination of dlookup value and combo box is causing my problems?
One other thing to note: the form On Error event trips when this error
occurs, but err.number is '0' and the error routine doesn't fire; I can't
even trap for this problem.
Although you don't have my tables, the query syntax is as follows. It's
basically comprised of the master table and the dlookup column, with a
couple of criteria. I am deliberately not joining to rspResp, because this
table can be many-to-one with tIssues, and I don't want to repeat rows from
tIssues, nor do I want to do a group by to eliminate, because I'd be
required to list every field from tIssues and I don't want to do that either
(plus I want to keep the query as light and efficient as I can).
SELECT tIssues.*, DLookUp("rspResp","tIssuesResp","rspIssNo=" & [issNo] & "
and rspActive=True and rspRole=" & AppSetRet("OWNER")) AS rspResp
FROM tIssues
WHERE (((tIssues.issProject)=538 Or (tIssues.issProject)=542) AND
((tIssues.issCat)=627 Or (tIssues.issCat)=665 Or (tIssues.issCat)=644 Or
(tIssues.issCat)=632))
ORDER BY tIssues.issDate;
I've got a dlookup column in an underlying query on a datasheet subform.
This dlookup is represented on the form by a combo box. when I'm displaying
the form on which the datasheet resides, I can filter/sort by any field on
the form but this one. when I attempt filter/sort, I get "type mismatch in
expression", which, upon selecting help, states "Type mismatch in JOIN
expression. (Error 3615) A JOIN expression is attempting to join two tables
on fields of incompatible data types. For example, you will get this error
if you attempt to join a Memo field with a Text field."
However, when I take the actual record source and paste it into a query
window, I can sort/filter by the dlookup column. The only missing variable
here is that naturally it's not in combo box form when displayed in query
form. If I didn't have other combo boxes in my datasheet subform, I'd think
this was the problem (they're all filter/sort compatible), but maybe the
combination of dlookup value and combo box is causing my problems?
One other thing to note: the form On Error event trips when this error
occurs, but err.number is '0' and the error routine doesn't fire; I can't
even trap for this problem.
Although you don't have my tables, the query syntax is as follows. It's
basically comprised of the master table and the dlookup column, with a
couple of criteria. I am deliberately not joining to rspResp, because this
table can be many-to-one with tIssues, and I don't want to repeat rows from
tIssues, nor do I want to do a group by to eliminate, because I'd be
required to list every field from tIssues and I don't want to do that either
(plus I want to keep the query as light and efficient as I can).
SELECT tIssues.*, DLookUp("rspResp","tIssuesResp","rspIssNo=" & [issNo] & "
and rspActive=True and rspRole=" & AppSetRet("OWNER")) AS rspResp
FROM tIssues
WHERE (((tIssues.issProject)=538 Or (tIssues.issProject)=542) AND
((tIssues.issCat)=627 Or (tIssues.issCat)=665 Or (tIssues.issCat)=644 Or
(tIssues.issCat)=632))
ORDER BY tIssues.issDate;