Customized Date Filter

[Jahlu]

I am trying to set up a filter that will allow users to enter specific dates
in "from" and "to" fields and which will then query specific results which
fall within that date range. The catch is that I only want the filter to use
these dates when a specific option button is selected, now... I have tested
my query using the criteria:

Between [StartDateField] and [EndDateField]

and I was able to return the results that I was looking for; however, when I
tried this inside of my IIf statement:

IIf([OptionButtonA]=True,Between [StartDateField] and [EndDateField],"*")

my query will not return any results. I have also tried:

IIf([OptionButtonA]=True,>= [StartDateField] and <=[EndDateField],"*")

but to no avail. I figure that I am either missing something really simple,
or there is a completely different method of going about this that I have not
considered. Any assistance would be greatly appreciated!

P.S. - I can only view information posted here at the newsgroups,
unfortunately, because of firewall issues here at work I am unable to follow
links to research additional code.

 

[John W. Vinson]

Between [StartDateField] and [EndDateField]

and I was able to return the results that I was looking for; however, when I
tried this inside of my IIf statement:

IIf([OptionButtonA]=True,Between [StartDateField] and [EndDateField],"*")

my query will not return any results. I have also tried:

IIf([OptionButtonA]=True,>= [StartDateField] and <=[EndDateField],"*")

You can't pass operators using an IIF. Try this instead:

WHERE ([OptionButtonA] AND [datefield] BETWEEN [StartDateField] AND
[EndDateField])

This will return records between the dates if OptionButtonA (a field in the
table???) is True, and nothing otherwise (you don't say what you want to
happen if OptionButtonA is false).

John W. Vinson [MVP]

 

[Jahlu]

John,

Thanks for the suggestion, I will try it first thing in the morning. For
clarification (sorry I did not mention this in the original post)
OptionButtonA is an option button on my form, but hopefully it will still
produce the desired results. Regarding what I would like to happen if
OptionButtonA is not true, well, I was just using a wildcard; however, if the
statement simply does nothing (does not further restrict my query at this
point) then that would be exactly what I am looking for. Thanks again.

Between [StartDateField] and [EndDateField]

and I was able to return the results that I was looking for; however, when I
tried this inside of my IIf statement:

IIf([OptionButtonA]=True,Between [StartDateField] and [EndDateField],"*")

my query will not return any results. I have also tried:

IIf([OptionButtonA]=True,>= [StartDateField] and <=[EndDateField],"*")

You can't pass operators using an IIF. Try this instead:

WHERE ([OptionButtonA] AND [datefield] BETWEEN [StartDateField] AND
[EndDateField])

This will return records between the dates if OptionButtonA (a field in the
table???) is True, and nothing otherwise (you don't say what you want to
happen if OptionButtonA is false).

John W. Vinson [MVP]

 

[John W. Vinson]

John,

Thanks for the suggestion, I will try it first thing in the morning. For
clarification (sorry I did not mention this in the original post)
OptionButtonA is an option button on my form, but hopefully it will still
produce the desired results. Regarding what I would like to happen if
OptionButtonA is not true, well, I was just using a wildcard; however, if the
statement simply does nothing (does not further restrict my query at this
point) then that would be exactly what I am looking for. Thanks again.

If it's a form control my code will NOT work - and if you want it to retrieve
all records then it won't do that either!

Try

WHERE (([datefield] >= [Forms]![yourform]![StartDateField] AND [datefield] <=
[Forms]![yourform]![EndDateField]) OR [Forms]![yourform]![OptionButtonA] =
False)


John W. Vinson [MVP]

 

[Jahlu]

Works beautifully! Thank you so much!

John,

Thanks for the suggestion, I will try it first thing in the morning. For
clarification (sorry I did not mention this in the original post)
OptionButtonA is an option button on my form, but hopefully it will still
produce the desired results. Regarding what I would like to happen if
OptionButtonA is not true, well, I was just using a wildcard; however, if the
statement simply does nothing (does not further restrict my query at this
point) then that would be exactly what I am looking for. Thanks again.

If it's a form control my code will NOT work - and if you want it to retrieve
all records then it won't do that either!

Try

WHERE (([datefield] >= [Forms]![yourform]![StartDateField] AND [datefield] <=
[Forms]![yourform]![EndDateField]) OR [Forms]![yourform]![OptionButtonA] =
False)


John W. Vinson [MVP]