Using OR in IIF statement

A

anil

Hi
I want to use OR in IIF statement as a condition in the query

'Quarterly' OR IIf([Sampler_loc].[monthID]=Month([tblnewone].[BDate])
AND WeekOfMonth2([tblnewone].[BDate])=1, 'monthly' OR
'Fortnightly','Weekly')))

But it gives me error at 'monthly' or 'Fortnightly'

Actually I need 'quarterly' or 'monthly' or 'fortnightly' for same
month and week 1
and 'quarterly' or 'weekly' for remaining weeks and same month.

Can some provide solution to this.

thanks
 
M

Marshall Barton

anil said:
I want to use OR in IIF statement as a condition in the query

'Quarterly' OR IIf([Sampler_loc].[monthID]=Month([tblnewone].[BDate])
AND WeekOfMonth2([tblnewone].[BDate])=1, 'monthly' OR
'Fortnightly','Weekly')))

But it gives me error at 'monthly' or 'Fortnightly'

Actually I need 'quarterly' or 'monthly' or 'fortnightly' for same
month and week 1
and 'quarterly' or 'weekly' for remaining weeks and same month.
Click to expand...


You can not expect Access to run a query and reconstruct the
criteria for every record. In other words, the expressions
can ony return a value, not another expression.

You did not provide enough information for me to correct
whatever you are trying to do, but the following might be
useful if what you posted is a criteria for some field.

I suspect that most of the confusion is caused by using the
query design grid and things would be a lot clearer if you
wrote the WHERE clause in SQL view.

I think(?) you want something like:

WHERE IIf(Sampler_loc.monthID=Month(tblnewone.BDate
AND WeekOfMonth2(tblnewone.BDate)=1.
somefield IN('quarterly','monthly','fortnightly')
IIf(Sampler_loc.monthID=Month(tblnewone.BDate,
somefield IN('quarterly','monthly'),
somefield = 'quarterly'))

You can do this in the query design grid by using a
calculated field with the above expression and setting its
criteria to True.
 
A

anil

anil said:
I want to use OR in IIF statement as a condition in the query
Click to expand...
'Quarterly' OR IIf([Sampler_loc].[monthID]=Month([tblnewone].[BDate])
AND WeekOfMonth2([tblnewone].[BDate])=1, 'monthly' OR
'Fortnightly','Weekly')))
Click to expand...
But it gives me error at 'monthly' or  'Fortnightly'
Click to expand...
Actually I need 'quarterly'  or 'monthly' or 'fortnightly' for same
month and week 1
and 'quarterly' or 'weekly' for remaining weeks and same month.
Click to expand...

You can not expect Access to run a query and reconstruct the
criteria for every record.  In other words, the expressions
can ony return a value, not another expression.

You did not provide enough information for me to correct
whatever you are trying to do, but the following might be
useful if what you posted is a criteria for some field.

I suspect that most of the confusion is caused by using the
query design grid and things would be a lot clearer if you
wrote the WHERE clause in SQL view.

I think(?) you want something like:

WHERE IIf(Sampler_loc.monthID=Month(tblnewone.BDate
                                                                                AND WeekOfMonth2(tblnewone.BDate)=1.
                                        somefield IN('quarterly','monthly','fortnightly')
                IIf(Sampler_loc.monthID=Month(tblnewone.BDate,
                                                somefield IN('quarterly','monthly'),
                        somefield = 'quarterly'))

You can do this in the query design grid by using a
calculated field with the above expression and setting its
criteria to True.
Click to expand...

HI Marshall
Thanks for replying. Although I tried using ( ) as suggested by
Yaacov, but it did not work.

Here is complete query

SELECT DISTINCT tblnewone.LocationName, tblnewone.SiteID,
tblnewone.SiteCode, tblnewone.SiteAddress, tblnewone.SiteType,
tblnewone.BDate, WeekofMonth2(tblnewone.Bdate) AS WeekNo,
tblWSP.Frequency, tblWSP.ParameterName, tblLocation.LocationSDWTown,
tblWSP.LabTested
FROM ((tblLocation INNER JOIN tblWSP ON tblLocation.LocationID =
tblWSP.LocationID) INNER JOIN tblnewone ON (tblWSP.Location =
tblnewone.LocationName) AND (tblWSP.SiteType = tblnewone.SiteType))
INNER JOIN sampler_Loc ON tblnewone.LocationName =
sampler_Loc.LocationName
WHERE (((tblnewone.SiteType)='S') AND ((tblWSP.Frequency)='Weekly' Or
(tblWSP.Frequency)=IIf([Sampler_loc].[monthID]=Month([tblnewone].
[BDate]) And WeekOfMonth2([tblnewone].[BDate])=1,
(tblWSP.Frequency)='monthly' Or
(tblWSP.Frequency)="fortnightly",IIf(([Sampler_loc].
[monthID]=Month([tblnewone].[BDate]) And [Sampler_loc].[monthID]=2)
And WeekOfMonth2([tblnewone].[BDate])=2 Or ([Sampler_loc].
[monthID]=Month([tblnewone].[BDate]) And [Sampler_loc].[monthID]=3)
And WeekOfMonth2([tblnewone].[BDate])=2 Or ([Sampler_loc].
[monthID]=Month([tblnewone].[BDate]) And [Sampler_loc].[monthID]=4)
And WeekOfMonth2([tblnewone].[BDate])=2,'Annually',IIf([Sampler_loc].
[monthID]=Month([tblnewone].[BDate]) And WeekOfMonth2([tblnewone].
[BDate])=3,(tblWSP.Frequency)='Quarterly' Or
(tblWSP.Frequency)="fortnightly",'Weekly')))));

Here in this query on basis on week and monthly I have to pick
parameters e.g some parameters are tested on frequency weekly,some
fortnightly , some monthly,some quarterly and some annually.

I get the problem at - tblWSP.Frequency)='monthly' Or
(tblWSP.Frequency)="fortnightly",
It gives data type mismatch in criteria expression. When I remove
fortnightly , it works fine.

Please help

Thanks
anil
 
M

Marshall Barton

anil said:
anil said:
I want to use OR in IIF statement as a condition in the query
Click to expand...
'Quarterly' OR IIf([Sampler_loc].[monthID]=Month([tblnewone].[BDate])
AND WeekOfMonth2([tblnewone].[BDate])=1, 'monthly' OR
'Fortnightly','Weekly')))
Click to expand...
But it gives me error at 'monthly' or  'Fortnightly'
Click to expand...
Actually I need 'quarterly'  or 'monthly' or 'fortnightly' for same
month and week 1
and 'quarterly' or 'weekly' for remaining weeks and same month.
Click to expand...

You can not expect Access to run a query and reconstruct the
criteria for every record.  In other words, the expressions
can ony return a value, not another expression.

You did not provide enough information for me to correct
whatever you are trying to do, but the following might be
useful if what you posted is a criteria for some field.

I suspect that most of the confusion is caused by using the
query design grid and things would be a lot clearer if you
wrote the WHERE clause in SQL view.

I think(?) you want something like:

WHERE IIf(Sampler_loc.monthID=Month(tblnewone.BDate
                                                                                AND WeekOfMonth2(tblnewone.BDate)=1.
                                        somefield IN('quarterly','monthly','fortnightly')
                IIf(Sampler_loc.monthID=Month(tblnewone.BDate,
                                                somefield IN('quarterly','monthly'),
                        somefield = 'quarterly'))

You can do this in the query design grid by using a
calculated field with the above expression and setting its
criteria to True.
Click to expand...

Here is complete query

SELECT DISTINCT tblnewone.LocationName, tblnewone.SiteID,
tblnewone.SiteCode, tblnewone.SiteAddress, tblnewone.SiteType,
tblnewone.BDate, WeekofMonth2(tblnewone.Bdate) AS WeekNo,
tblWSP.Frequency, tblWSP.ParameterName, tblLocation.LocationSDWTown,
tblWSP.LabTested
FROM ((tblLocation INNER JOIN tblWSP ON tblLocation.LocationID =
tblWSP.LocationID) INNER JOIN tblnewone ON (tblWSP.Location =
tblnewone.LocationName) AND (tblWSP.SiteType = tblnewone.SiteType))
INNER JOIN sampler_Loc ON tblnewone.LocationName =
sampler_Loc.LocationName
WHERE (((tblnewone.SiteType)='S') AND ((tblWSP.Frequency)='Weekly' Or
(tblWSP.Frequency)=IIf([Sampler_loc].[monthID]=Month([tblnewone].
[BDate]) And WeekOfMonth2([tblnewone].[BDate])=1,
(tblWSP.Frequency)='monthly' Or
(tblWSP.Frequency)="fortnightly",IIf(([Sampler_loc].
[monthID]=Month([tblnewone].[BDate]) And [Sampler_loc].[monthID]=2)
And WeekOfMonth2([tblnewone].[BDate])=2 Or ([Sampler_loc].
[monthID]=Month([tblnewone].[BDate]) And [Sampler_loc].[monthID]=3)
And WeekOfMonth2([tblnewone].[BDate])=2 Or ([Sampler_loc].
[monthID]=Month([tblnewone].[BDate]) And [Sampler_loc].[monthID]=4)
And WeekOfMonth2([tblnewone].[BDate])=2,'Annually',IIf([Sampler_loc].
[monthID]=Month([tblnewone].[BDate]) And WeekOfMonth2([tblnewone].
[BDate])=3,(tblWSP.Frequency)='Quarterly' Or
(tblWSP.Frequency)="fortnightly",'Weekly')))));

Here in this query on basis on week and monthly I have to pick
parameters e.g some parameters are tested on frequency weekly,some
fortnightly , some monthly,some quarterly and some annually.

I get the problem at - tblWSP.Frequency)='monthly' Or
(tblWSP.Frequency)="fortnightly",
It gives data type mismatch in criteria expression. When I remove
fortnightly , it works fine.
Click to expand...


I'm sorry, but I can't get a grip on all those conditions in
all those nested IIf functions. Most likely, there is
missing part of an expression causing the error.

I think(?) you can simplify things a litte by using the IN
operator instead of the long complex OR sequences.

I suggest that you create a function to contain all that
logic. At least a function can use VBA code constructs
instead of being limited to just the IIf function.
 
A

anil

anil said:
anil wrote:
I want to use OR in IIF statement as a condition in the query
'Quarterly' OR IIf([Sampler_loc].[monthID]=Month([tblnewone].[BDate])
AND WeekOfMonth2([tblnewone].[BDate])=1, 'monthly' OR
'Fortnightly','Weekly')))
But it gives me error at 'monthly' or  'Fortnightly'
Actually I need 'quarterly'  or 'monthly' or 'fortnightly' for same
month and week 1
and 'quarterly' or 'weekly' for remaining weeks and same month.
You can not expect Access to run a query and reconstruct the
criteriafor every record.  In other words, theexpressions
can ony return a value, not another expression.
You did not provide enough information for me to correct
whatever you are trying to do, but the following might be
useful if what you posted is acriteriafor some field.
I suspect that most of the confusion is caused byusingthe
query design grid and things would be a lot clearer if you
wrote the WHERE clause in SQL view.
I think(?) you want something like:
WHERE IIf(Sampler_loc.monthID=Month(tblnewone.BDate
                                                                              AND WeekOfMonth2(tblnewone.BDate)=1.
                                       somefield IN('quarterly','monthly','fortnightly')
                IIf(Sampler_loc.monthID=Month(tblnewone.BDate,
                                               somefield IN('quarterly','monthly'),
                        somefield = 'quarterly'))
You can do this in the query design grid byusinga
calculated field with the above expression and setting its
criteriato True.
Click to expand...
Click to expand...
Here is complete query
Click to expand...
SELECT DISTINCT tblnewone.LocationName, tblnewone.SiteID,
tblnewone.SiteCode, tblnewone.SiteAddress, tblnewone.SiteType,
tblnewone.BDate, WeekofMonth2(tblnewone.Bdate) AS WeekNo,
tblWSP.Frequency, tblWSP.ParameterName, tblLocation.LocationSDWTown,
tblWSP.LabTested
FROM ((tblLocation INNER JOIN tblWSP ON tblLocation.LocationID =
tblWSP.LocationID) INNER JOIN tblnewone ON (tblWSP.Location =
tblnewone.LocationName) AND (tblWSP.SiteType = tblnewone.SiteType))
INNER JOIN sampler_Loc ON tblnewone.LocationName =
sampler_Loc.LocationName
WHERE (((tblnewone.SiteType)='S') AND ((tblWSP.Frequency)='Weekly' Or
(tblWSP.Frequency)=IIf([Sampler_loc].[monthID]=Month([tblnewone].
[BDate]) And WeekOfMonth2([tblnewone].[BDate])=1,
(tblWSP.Frequency)='monthly' Or
(tblWSP.Frequency)="fortnightly",IIf(([Sampler_loc].
[monthID]=Month([tblnewone].[BDate]) And [Sampler_loc].[monthID]=2)
And WeekOfMonth2([tblnewone].[BDate])=2 Or ([Sampler_loc].
[monthID]=Month([tblnewone].[BDate]) And [Sampler_loc].[monthID]=3)
And WeekOfMonth2([tblnewone].[BDate])=2 Or ([Sampler_loc].
[monthID]=Month([tblnewone].[BDate]) And [Sampler_loc].[monthID]=4)
And WeekOfMonth2([tblnewone].[BDate])=2,'Annually',IIf([Sampler_loc].
[monthID]=Month([tblnewone].[BDate]) And WeekOfMonth2([tblnewone].
[BDate])=3,(tblWSP.Frequency)='Quarterly' Or
(tblWSP.Frequency)="fortnightly",'Weekly')))));
Click to expand...
Here in this query on basis on week and monthly I have to pick
parameters e.g some parameters are tested  on frequency weekly,some
fortnightly , some monthly,some quarterly and some annually.
Click to expand...
I get the problem at - tblWSP.Frequency)='monthly' Or
(tblWSP.Frequency)="fortnightly",
It gives data type mismatch incriteriaexpression. When I remove
fortnightly , it works fine.
Click to expand...

I'm sorry, but I can't get a grip on all those conditions in
all those nested IIf  functions.  Most likely, there is
missing part of an expression causing the error.

I think(?) you can simplify things a litte byusingthe IN
operator instead of the long complex OR sequences.

I suggest that you create a function to contain all that
logic.  At least a function can use VBA code constructs
instead of being limited to just the IIf function.

--
Marsh
MVP [MS Access]- Hide quoted text -

- Show quoted text -
Click to expand...

Hi Marshall
Although I tried to use function in the following query as advised by
you, but it is not helping out.

QUERY :

SELECT DISTINCT qryNewtbl.LocationName, qryNewtbl.SiteCode,
qryNewtbl.SiteAddress, qryNewtbl.SiteType, qryNewtbl.BDate,
qryNewtbl.WeekNo, qrySam_Loc_Mon.MonthId, tblWSP.Frequency,
tblWSP.ParameterName, tblWSP.LabTested
FROM (qryNewtbl INNER JOIN tblWSP ON (qryNewtbl.SiteType =
tblWSP.SiteType) AND (qryNewtbl.LocationName = tblWSP.Location)) INNER
JOIN qrySam_Loc_Mon ON qryNewtbl.LocationName =
qrySam_Loc_Mon.LocationName
WHERE (((tblWSP.Frequency)=Frequency2([qryNewtbl].[weekNo],
[qrySam_Loc_Mon].[MonthID])));

FUNCTION:

Public Function Frequency2(WeekNo As Integer, WWMonthId As Integer) As
String
' Dim Weekly As String, Monthly As String, Fortnightly As String,
Annually As String, Quarterly As String

If WWMonthId = 2 Or 3 Or 4 Then
If WeekNo = 1 Then
Frequency2 = "in('Weekly' , 'Fortnightly' , 'Monthly')"
ElseIf WeekNo = 2 Then
Frequency2 = "in('Weekly' , 'Quarterly')"
ElseIf WeekNo = 3 Then
Frequency2 = "in('Weekly','Fortnightly' , 'Annually')"
ElseIf WeekNo = 4 Or 5 Then
Frequency2 = "'Weekly'"
End If
Else
If WeekNo = 1 Then
Frequency2 = "in('Weekly' , 'Fortnightly' , 'Monthly')"
ElseIf WeekNo = 2 Then
Frequency2 = "in('Weekly' , 'Quarterly')"
ElseIf WeekNo = 3 Then
Frequency2 = "in('Weekly' , 'Fortnightly')"
ElseIf WeekNo = 4 Or 5 Then
Frequency2 = "Weekly"
End If
End If
End Function

Can you please point out mistakes

Thanks
Anil
 

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

Need some PWA help 0
Map fortnightly data to months - can it be done? 13
simplifying a query with SQL 1
using OR in IIF("",condition True,"") 3
IIF in Crosstab Expression 0
IIF Statement 2
Does not recognize >=10 2
Iif Statement based on 2 conditions 2

Top