Query Syntax on If Statement

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

Guest

In the below example, the condition 1 does not work. I am sure it is a syntax
issue. It will accept a single argument, but does not accept a "Between"
statement.

Like IIf([Forms]![MainForm]![selectPeriod]="Week",Between
[Forms]![MainForm]![Begin_Week_Nbr] And [Forms]![MainForm]![End_Week_Nbr],"*")

If the user selects week in [selectPeriord] I want to show the week range or
wise show all. Any way of getting this to work? Thanks.
 
Is this what you mean?

=IIf([Forms]![MainForm]![selectPeriod]="Week",Between
[Forms]![MainForm]![Begin_Week_Nbr] And [Forms]![MainForm]![End_Week_Nbr],
Like "*")

Sam
In the below example, the condition 1 does not work. I am sure it is a syntax
issue. It will accept a single argument, but does not accept a "Between"
statement.

Like IIf([Forms]![MainForm]![selectPeriod]="Week",Between
[Forms]![MainForm]![Begin_Week_Nbr] And [Forms]![MainForm]![End_Week_Nbr],"*")

If the user selects week in [selectPeriord] I want to show the week range or
wise show all. Any way of getting this to work? Thanks.
Click to expand...
 
Yes, but even with the like at this position it still does not pull any data.

OfficeDev18 via AccessMonster.com said:
Is this what you mean?

=IIf([Forms]![MainForm]![selectPeriod]="Week",Between
[Forms]![MainForm]![Begin_Week_Nbr] And [Forms]![MainForm]![End_Week_Nbr],
Like "*")

Sam
In the below example, the condition 1 does not work. I am sure it is a syntax
issue. It will accept a single argument, but does not accept a "Between"
statement.

Like IIf([Forms]![MainForm]![selectPeriod]="Week",Between
[Forms]![MainForm]![Begin_Week_Nbr] And [Forms]![MainForm]![End_Week_Nbr],"*")

If the user selects week in [selectPeriord] I want to show the week range or
wise show all. Any way of getting this to work? Thanks.
Click to expand...
Click to expand...
 
You can't put 'Between' or 'Like' or '=' inside the IIf().
Try this and substitute your actual field your a testing against for [Your
Field].

Between IIf([Forms]![MainForm]![selectPeriod]="Week",
[Forms]![MainForm]![Begin_Week_Nbr], [Your Field]) And
IIf([Forms]![MainForm]![selectPeriod]="Week",
[Forms]![MainForm]![End_Week_Nbr],[Your Field])
 
Hi,

iif arguments should be "evaluable". Here, the second argument is NOT
evaluable, neither the third one. The trick is to remember that iif works
like a function call, you cannot say:

MyFunction( 5*6 , 5* , LIKE "a*")

since 5* is not evaluable, neither LIKE "a*". You can, on the other hand,
use

MyFunction( 5*6, 5*7, myString LIKE "a*" )


So, to solve your problem, it is probably better to make a computed column:


iif( ([Forms]![MainForm]![selectPeriod]="Week" , yourFieldName Between
[Forms]![MainForm]![Begin_Week_Nbr] And [Forms]![MainForm]![End_Week_Nbr] ,
true)


and next, add the criteria, under this computed expression,

= true


Note that the second argument

yourFieldName Between
Forms![MainForm]![Begin_Week_Nbr] And Forms![MainForm]![End_Week_Nbr]

is completely evaluable (assuming you change yourFieldName for the real
field name).



Hoping it may help,
Vanderghast, Access MVP


OfficeDev18 via AccessMonster.com said:
Is this what you mean?

=IIf([Forms]![MainForm]![selectPeriod]="Week",Between
[Forms]![MainForm]![Begin_Week_Nbr] And [Forms]![MainForm]![End_Week_Nbr],
Like "*")

Sam
In the below example, the condition 1 does not work. I am sure it is a
syntax
issue. It will accept a single argument, but does not accept a "Between"
statement.

Like IIf([Forms]![MainForm]![selectPeriod]="Week",Between
[Forms]![MainForm]![Begin_Week_Nbr] And
[Forms]![MainForm]![End_Week_Nbr],"*")

If the user selects week in [selectPeriord] I want to show the week range
or
wise show all. Any way of getting this to work? Thanks.
Click to expand...
Click to expand...
 
Back
Top