using OR in IIF("",condition True,"")

[anil]

Hi all
I am using the query as(example)
Select * from Q1 where frequency=F1(WeekNo)

and using this function as

Function F1(WeekNo As Integer) As String
If WeekNo = 1 Then
F1 = "weekly" Or "Monthly"
ElseIf WeekNo = 2 Then
F1 = "weekly" Or "Quaterly"
ElseIf WeekNo = 3 Then
F1 = "weekly" Or "Annually"
Else
FrequencyNo = "weekly"
End If
End Function

Actually what I need is
Select * from Q1 where frequency = "weekly" or "Monthly"
if the weekNo is 1 which is calculated in Q1

I tried using
Select * from Q1 where Frequency = iif(IIf([Q1].[WeekNo])=1,"Monthly"or
"weekly",IIf([Q1].[WeekNo])=2,"Quaterly" or
"weekly",IIf([Q1].[WeekNo])=3,"Annually"or "weekly","Weekly")))

but not success

problem comes that frequency does not comes equal to "weekly" or
"Monthly" (combined)

Can anyone fix it
thanks
anil

 

[David F Cox]

Try:-

SELECT * FROM Q1 WHERE (Frequency = "Weekly") OR (Frequency = CHOOSE( 1,
"Monthly", 2, "Quarterly", 3, "Anually", 4, "Weekly")

Explanation of my thinking:-

You appear to be looking at a frequency of "weekly" whatever value is in
WeekNo. In that case it should not feature in any conditional (if, iif)
statements at all.

SELECT * FROM Q1 WHERE (Frequency = "Weekly") OR .....

and we must choose the condition for the other test:-

(Frequency = CHOOSE( 1, "Monthly", 2, "Quarterly", 3, "Anually" .....

if we do not cater for 4 then CHOOSE will return NULL for that condition, or
for any other non-match situations, if they are possible. If we do not want
that behaviour we can do a kludge :-

..., 4, "Weekly")

the query will do the same test twice (if the optimiser does not catch it)

Untested, at your own risk ...

 

[anil]

Thanks David
It worked with slight change.
Actually 'choose function' was new thing for me to learn
Thanks a lot

Try:-

SELECT * FROM Q1 WHERE (Frequency = "Weekly") OR (Frequency = CHOOSE( 1,
"Monthly", 2, "Quarterly", 3, "Anually", 4, "Weekly")

Explanation of my thinking:-

You appear to be looking at a frequency of "weekly" whatever value is in
WeekNo. In that case it should not feature in any conditional (if, iif)
statements at all.

SELECT * FROM Q1 WHERE (Frequency = "Weekly") OR .....

and we must choose the condition for the other test:-

(Frequency = CHOOSE( 1, "Monthly", 2, "Quarterly", 3, "Anually" .....

if we do not cater for 4 then CHOOSE will return NULL for that condition, or
for any other non-match situations, if they are possible. If we do not want
that behaviour we can do a kludge :-

..., 4, "Weekly")

the query will do the same test twice (if the optimiser does not catch it)

Untested, at your own risk ...

Hi all
I am using the query as(example)
Select * from Q1 where frequency=F1(WeekNo)

and using this function as

Function F1(WeekNo As Integer) As String
If WeekNo = 1 Then
F1 = "weekly" Or "Monthly"
ElseIf WeekNo = 2 Then
F1 = "weekly" Or "Quaterly"
ElseIf WeekNo = 3 Then
F1 = "weekly" Or "Annually"
Else
FrequencyNo = "weekly"
End If
End Function

Actually what I need is
Select * from Q1 where frequency = "weekly" or "Monthly"
if the weekNo is 1 which is calculated in Q1

I tried using
Select * from Q1 where Frequency = iif(IIf([Q1].[WeekNo])=1,"Monthly"or
"weekly",IIf([Q1].[WeekNo])=2,"Quaterly" or
"weekly",IIf([Q1].[WeekNo])=3,"Annually"or "weekly","Weekly")))

but not success

problem comes that frequency does not comes equal to "weekly" or
"Monthly" (combined)

Can anyone fix it
thanks
anil

 

[David F Cox]

Thanks David
It worked with slight change.
Actually 'choose function' was new thing for me to learn
Thanks a lot
Anil

I am glad I was able to help. CHOOSE is a fairly new addition to my toolbelt
too, and I used it instead of alternate solutions to introduce it to others
too.

Try:-

SELECT * FROM Q1 WHERE (Frequency = "Weekly") OR (Frequency = CHOOSE( 1,
"Monthly", 2, "Quarterly", 3, "Anually", 4, "Weekly")

Explanation of my thinking:-

You appear to be looking at a frequency of "weekly" whatever value is in
WeekNo. In that case it should not feature in any conditional (if, iif)
statements at all.

SELECT * FROM Q1 WHERE (Frequency = "Weekly") OR .....

and we must choose the condition for the other test:-

(Frequency = CHOOSE( 1, "Monthly", 2, "Quarterly", 3, "Anually" .....

if we do not cater for 4 then CHOOSE will return NULL for that condition,
or
for any other non-match situations, if they are possible. If we do not
want
that behaviour we can do a kludge :-

..., 4, "Weekly")

the query will do the same test twice (if the optimiser does not catch
it)

Untested, at your own risk ...

Hi all
I am using the query as(example)
Select * from Q1 where frequency=F1(WeekNo)

and using this function as

Function F1(WeekNo As Integer) As String
If WeekNo = 1 Then
F1 = "weekly" Or "Monthly"
ElseIf WeekNo = 2 Then
F1 = "weekly" Or "Quaterly"
ElseIf WeekNo = 3 Then
F1 = "weekly" Or "Annually"
Else
FrequencyNo = "weekly"
End If
End Function

Actually what I need is
Select * from Q1 where frequency = "weekly" or "Monthly"
if the weekNo is 1 which is calculated in Q1

I tried using
Select * from Q1 where Frequency = iif(IIf([Q1].[WeekNo])=1,"Monthly"or
"weekly",IIf([Q1].[WeekNo])=2,"Quaterly" or
"weekly",IIf([Q1].[WeekNo])=3,"Annually"or "weekly","Weekly")))

but not success

problem comes that frequency does not comes equal to "weekly" or
"Monthly" (combined)

Can anyone fix it
thanks
anil