Between Dates

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

Guest

I have a between statement that is in an IIf satement for the criteria in a query.

IIf(([Forms]![test frm]![OptionGroup1])=1,[Forms]![test frm]![StartingDate],Between [Forms]![test frm]![BetweenBox1] And [Forms]![test frm]![BetweenBox2])

Any idea why the between statement doesn't work?

Thanks, Josh
 
Can you describe exactly what you are trying to get as a result? A Between
clause requires that you compare some value to the 2 text boxes, but you are
simply stating that something (not in your statement) is between those
boxes.

--
Lynn Trapp
MS Access MVP
www.ltcomputerdesigns.com
Access Security: www.ltcomputerdesigns.com/Security.htm



Josh said:
I have a between statement that is in an IIf satement for the criteria in a query.

IIf(([Forms]![test frm]![OptionGroup1])=1,[Forms]![test
Click to expand...
frm]![StartingDate],Between [Forms]![test frm]![BetweenBox1] And
[Forms]![test frm]![BetweenBox2])
 
You can't set the criteria that way. You can set the parameters using an IIF
statement, but you cannot set the operators ( =, Between, And, <>, etc.).

You can try something like the following as criteria

Between IIf(([Forms]![test frm]![OptionGroup1])=1,
[Forms]![test frm]![StartingDate],
[Forms]![test frm]![BetweenBox1])
And IIf(([Forms]![test frm]![OptionGroup1])=1,
[Forms]![test frm]![StartingDate],
[Forms]![test frm]![BetweenBox2])

That all goes on one criteria cell on the query grid. I just broke it up with
line feeds for easier reading.
 
(Guessing of your requirements here)

You cannot use the posted syntax to create a Between ...
And ... criterion. If my guess is correct, you want
criteria smething like:

.... WHERE
(
([Forms]![test frm]![OptionGroup1]=1) AND
([SomeDateField] = [Forms]![test frm]![StartingDate])
)
OR
(
([Forms]![test frm]![OptionGroup1]<>1) AND
([SomeDateField]
BETWEEN [Forms]![test frm]![BetweenBox1]
AND [Forms]![test frm]![BetweenBox2])
)

I think you need to declare the Parameter Types also if
your TextBoxes are unbound so that Access recognises these
as DateTime.

HTH
Van T. Dinh
MVP (Access)



-----Original Message-----
I have a between statement that is in an IIf satement for the criteria in a query.

IIf(([Forms]![test frm]![OptionGroup1])=1,[Forms]![test
Click to expand...
frm]![StartingDate],Between [Forms]![test frm]!
[BetweenBox1] And [Forms]![test frm]![BetweenBox2])
 
Back
Top