Does this query with IIf statement contain syntax errors?

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I'm trying to write a query that displays records based on dates the user enters. If the user doesn't enter anything, then it'll use the min(date). Is there something wrong with this query?

SELECT * FROM History WHERE (TransType Like "P*" Or TransType Like "N*")
AND
TranDate >= IIf(IsNull([Forms]![UnivCriteriaFrm].[txtFrom]),select min(trandate) from history, [Forms]![UnivCriteriaFrm].[txtFrom])
Also, when I click OK on the syntax error messagebox, it highlights the 2nd part of the query: select min(trandate) from history, [Forms]![UnivCriteriaFrm].[txtFrom])

What am I doing wrong?

Thanks.
 
A couple of things, first in a query, to reference a control on a form you
must use the bang syntax.

[Forms]![UnivCriteriaFrm]![txtFrom]

Also your subquery needs to be wrapped in parentheses.

SELECT *
FROM History
WHERE
(TransType Like "P*" Or TransType Like "N*")
AND trandate >=
IIf(IsNull( [Forms]![UnivCriteriaFrm]![txtFrom]),
( select min(trandate) as mindate from orders),
[Forms]![UnivCriteriaFrm]![txtFrom]);
 
Back
Top