Adding an If statement to Where part of a Select

[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

 

[Gary Miller]

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 >=

[Forms]![UnivCriteriaFrm].[txtFrom] <-

AND

TranDate<=[Forms]![UnivCriteriaFrm]![txtTo] <-

 

[Guest]

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

 

[Gary Miller]

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

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