Adding an If statement to Where part of a Select

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

Guest

How can I add an If to a Select statement? The query's run based on dates
the user enters before execution.
I would like to add an If statement saying, "If
[Forms]![UnivCriteriaFrm].[txtFrom] is empty, the date used will equal
SELECT Min(TranDate) FROM table1".

The logic would be in the WHERE section...

SELECT * FROM History
WHERE (TransType Like "P*" Or TransType Like "N*")
AND TranDate >= [Forms]![UnivCriteriaFrm].[txtFrom] <-
AND TranDate<=[Forms]![UnivCriteriaFrm]![txtTo] <-

Thanks,
VM
 
Try this...

SELECT * FROM History
WHERE (TransType Like "P*" Or TransType Like
"N*")
AND TranDate >=
IIf(IsNull([Forms]![UnivCriteriaFrm].[txtFrom]), SELECT
Min(TranDate) FROM table1,
[Forms]![UnivCriteriaFrm].[txtFrom]), <-
AND TranDate<=[Forms]![UnivCriteriaFrm]![txtTo]
<-

Gary Miller
Sisters, OR

How can I add an If to a Select statement? The query's run based on dates
the user enters before execution.
I would like to add an If statement saying, "If
[Forms]![UnivCriteriaFrm].[txtFrom] is empty, the date used will equal
SELECT Min(TranDate) FROM table1".

The logic would be in the WHERE section...

SELECT * FROM History
WHERE (TransType Like "P*" Or TransType Like "N*")
AND TranDate >=
Click to expand...
[Forms]![UnivCriteriaFrm].[txtFrom] <-
AND
Click to expand...
TranDate<=[Forms]![UnivCriteriaFrm]![txtTo] <-
 
I tried what you said and it didn't work. So I tried writing a simple query
that uses the IIf statement and it still doesn't work. This is the 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]))

It gives me a syntax error and highlights the Select in the IIF statement-
SELECT Min(TranDate) FROM History,[Forms]![UnivCriteriaFrm].[txtFrom])
*but* the error doesn't highlight the last parenthesis.

What could be wrong?

Thanks.





Gary Miller said:
Try this...

SELECT * FROM History
WHERE (TransType Like "P*" Or TransType Like
"N*")
AND TranDate >=
IIf(IsNull([Forms]![UnivCriteriaFrm].[txtFrom]), SELECT
Min(TranDate) FROM table1,
[Forms]![UnivCriteriaFrm].[txtFrom]), <-
AND TranDate<=[Forms]![UnivCriteriaFrm]![txtTo]
<-

Gary Miller
Sisters, OR

How can I add an If to a Select statement? The query's run based on dates
the user enters before execution.
I would like to add an If statement saying, "If
[Forms]![UnivCriteriaFrm].[txtFrom] is empty, the date used will equal
SELECT Min(TranDate) FROM table1".

The logic would be in the WHERE section...

SELECT * FROM History
WHERE (TransType Like "P*" Or TransType Like "N*")
AND TranDate >=
Click to expand...
[Forms]![UnivCriteriaFrm].[txtFrom] <-
AND
Click to expand...
TranDate<=[Forms]![UnivCriteriaFrm]![txtTo] <-
Thanks,
VM
Click to expand...
Click to expand...
 
Looks like a missing bracket in the IIf statement and one
too many at the end of the SQL. Try this one.

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])

Gary Miller


I tried what you said and it didn't work. So I tried writing a simple query
that uses the IIf statement and it still doesn't work. This is the 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]))

It gives me a syntax error and highlights the Select in the IIF statement-
SELECT Min(TranDate) FROM History,[Forms]![UnivCriteriaFrm].[txtFrom])
*but* the error doesn't highlight the last parenthesis.

What could be wrong?

Thanks.





Try this...

SELECT * FROM History
WHERE (TransType Like "P*" Or TransType Like
"N*")
AND TranDate >=
IIf(IsNull([Forms]![UnivCriteriaFrm].[txtFrom]), SELECT
Min(TranDate) FROM table1,
[Forms]![UnivCriteriaFrm].[txtFrom]), <-
AND TranDate<=[Forms]![UnivCriteriaFrm]![txtTo]
<-

Gary Miller
Sisters, OR

How can I add an If to a Select statement? The query's
Click to expand...
run
based on dates
the user enters before execution.
I would like to add an If statement saying, "If
[Forms]![UnivCriteriaFrm].[txtFrom] is empty, the date used will equal
SELECT Min(TranDate) FROM table1".

The logic would be in the WHERE section...

SELECT * FROM History
WHERE (TransType Like "P*" Or TransType
Click to expand...
Like
"N*")
AND TranDate >=
Click to expand...
[Forms]![UnivCriteriaFrm].[txtFrom] <-
AND
Click to expand...
TranDate<=[Forms]![UnivCriteriaFrm]![txtTo] <-
Thanks,
VM
Click to expand...
Click to expand...
Click to expand...
 
Back
Top