IIF Expression

[GOL]

I created a form to gather date criteria for a query instead of using a
parameter pop up box. You simply put the dates in the txt boxes and the
query references its criteria to the txt boxes. I have two txt boxes for a
range of dates to view (ex. 1/1/04 to 1/6/04). Since not everyone knows the
exact date the database was installed (the earliest available date), my boss
wants it formatted so that if the first box is left blank, then it retrieves
from the first date entered, and if the 2nd txt box is left empty it
retrieves to the most recent data. (If both empty it retrieves all).
Thinking I could make an IIf statement stating something like
IIf(IsNull([nameoftxt1]),"1/1/1925",[nameoftxt1]) and do the same for the 2nd
txt box. 1/1/1925 would work because I know no data was before this date,
then I could use some date like 1/1/2080 for 2nd box. However the txt boxes
aren't fields and the form they are on isn't bound to any table, its just a
criteria for a query. How can I make some kind of statement so that this
will work, the IIF statements I've tried don't work.

 

[Tellabs T. Burr]

Here is an idea. I hope it works. For Example

(IIF [textbox1] isnull Then "1/1/1901" Else Form![Formname]![Textbox1]) =<
(IIF Form![Formname]![textbox2] isnull Then "12/12/2801" Else [Textbox2])

 

[Mike Painter]

I created a form to gather date criteria for a query instead of using
a parameter pop up box. You simply put the dates in the txt boxes
and the query references its criteria to the txt boxes. I have two
txt boxes for a range of dates to view (ex. 1/1/04 to 1/6/04). Since
not everyone knows the exact date the database was installed (the
earliest available date), my boss wants it formatted so that if the
first box is left blank, then it retrieves from the first date
entered, and if the 2nd txt box is left empty it retrieves to the
most recent data. (If both empty it retrieves all). Thinking I could
make an IIf statement stating something like
IIf(IsNull([nameoftxt1]),"1/1/1925",[nameoftxt1]) and do the same for
the 2nd txt box. 1/1/1925 would work because I know no data was
before this date, then I could use some date like 1/1/2080 for 2nd
box. However the txt boxes aren't fields and the form they are on
isn't bound to any table, its just a criteria for a query. How can I
make some kind of statement so that this will work, the IIF
statements I've tried don't work.

I would probably use a button to run the query and it would create it on the
fly when pushed.

If isdate(startDate) and isdate(enddate) then
x = startDate
y = enddate
else if not isdate(startDate) and isdate(enddate)then
x = #1/1/1925#
y = enddate
else if isdate(startDate) and not isdate(enddate)then
x = startdate
y = Date()
else if isdate(startDate) and not isdate(enddate)then
x = #1/1/1925#
y = Date()
end if

txtSQL = "SELECT FirstName, LastName, SomeDate
FROM Gleaners WHERE SomeDate Between " & #x# & " And " & #y#

 

[John Vinson]

How can I make some kind of statement so that this
will work, the IIF statements I've tried don't work.

One reason they don't work is that dates are not strings, so the "
delimiters are causing problems.

Try using the NZ() function instead. A criterion of

NZ([Forms]![myform]![txtStart], #1/1/1925#)

for instance.

John W. Vinson[MVP]

 

[John Vinson]

Here is an idea. I hope it works. For Example

(IIF [textbox1] isnull Then "1/1/1901" Else Form![Formname]![Textbox1]) =<
(IIF Form![Formname]![textbox2] isnull Then "12/12/2801" Else [Textbox2])

Sorry, Tellabs, but this fails on several grounds. The IIF function
does not support ELSE; IsNull() is a function not an operator; you
cannot pass operators such as =< in a parameter.

See my answer in the thread for another approach.

John W. Vinson[MVP]

 

[Micah Chaney]

Try this:

In the Name portion of the Query that runs based on these two fields, Create
fields as follows...

BeginDate try:
BeginDate: IIf([Form]![FormName].[Field] is Null,
#1/1/1901#,[Form]![FormName].[Field])

EndDate try:
EndDate: IIf([Form]![FormName].[Field] is Null, Date(),
[Form]![FormName].[Field])

Then for the field in your table that actually contains your dates use this
criteria:
Between [BeginDate] and [EndDate]

Hope this helps.

How can I make some kind of statement so that this
will work, the IIF statements I've tried don't work.

One reason they don't work is that dates are not strings, so the "
delimiters are causing problems.

Try using the NZ() function instead. A criterion of

NZ([Forms]![myform]![txtStart], #1/1/1925#)

for instance.

John W. Vinson[MVP]

 

[GOL]

thanks

Try this:

In the Name portion of the Query that runs based on these two fields, Create
fields as follows...

BeginDate try:
BeginDate: IIf([Form]![FormName].[Field] is Null,
#1/1/1901#,[Form]![FormName].[Field])

EndDate try:
EndDate: IIf([Form]![FormName].[Field] is Null, Date(),
[Form]![FormName].[Field])

Then for the field in your table that actually contains your dates use this
criteria:
Between [BeginDate] and [EndDate]

Hope this helps.

How can I make some kind of statement so that this
will work, the IIF statements I've tried don't work.

One reason they don't work is that dates are not strings, so the "
delimiters are causing problems.

Try using the NZ() function instead. A criterion of

NZ([Forms]![myform]![txtStart], #1/1/1925#)

for instance.

John W. Vinson[MVP]

 

[GOL]

thanks works well

How can I make some kind of statement so that this
will work, the IIF statements I've tried don't work.

One reason they don't work is that dates are not strings, so the "
delimiters are causing problems.

Try using the NZ() function instead. A criterion of

NZ([Forms]![myform]![txtStart], #1/1/1925#)

for instance.

John W. Vinson[MVP]