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

A

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
 
D

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 ...
 
A

anil

Thanks David
It worked with slight change.
Actually 'choose function' was new thing for me to learn
Thanks a lot
Anil said:
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 said:
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
Click to expand...
Click to expand...
 
D

David F Cox

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

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.

David said:
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 said:
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
Click to expand...
Click to expand...
Click to expand...
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Function for week number 5
IIF returning text value 6
Using OR in IIF statement 6
query error 12
Passing Parameters between Subroutines 3
#Error in select query 6
Crosstab 4
Data Type mismatch in a query that uses a calculated field from another query 4

Top