problem with IIF expression in query criteria containing OR condit

[Dick Minter]

The query shown below returns no records. The criteria entered direcly
without the iif performs as intended, e.g. '[forms].[fmChkList].[cbSeu] OR 1'
works if entered without the IIF expression, but as the value returned by the
iif finds no records. Note: The appostrophes in my example are for
emphasis; the values are integers.

SELECT Sources.Name, Sources.SourceID, SourceStatus.Description AS Status,
Sources.Payment_required, Market.SEUno
FROM Market INNER JOIN (SourceStatus INNER JOIN Sources ON
SourceStatus.Status = Sources.Status) ON Market.SEUno = Sources.SEU
WHERE
(((Market.SEUno)=IIf([forms].[fmChkList].[cbseu]=1,[market].[seuno],([Market].[SEUno])=[forms].[fmChkList].[cbseu] Or ([Market].[SEUno])=1)))
ORDER BY Sources.Name;

I've considered defiing and running a sql statement in the form code where
the correct criteria can be set as a string, but why doesn't the above
statement work?

DM

 

[Michel Walsh]

Depends on what you want to do really, but I suspect your intention is :

WHERE ( Market.SEUno = IIf(forms.fmChkList.cbseu=1, market.seuno,
Market.SEUno=forms.fmChkList.cbseu) )
Or ( Market.SEUno =1 )


Hoping it may help,
Vanderghast, Access MVP

 

[Jerry Whittle]

You can't have the Or statement in the third argument of the IIf. Would
moving the "Or ([Market].[SEUno])=1" behind the IIf statement return the
right records?

 

[Dick Minter]

You got me on the right track. It works when I remove 'market.SEUno=' from
the expression, i.e.

WHERE ( Market.SEUno = IIf(forms.fmChkList.cbseu=1, market.seuno,
forms.fmChkList.cbseu) ) Or ( Market.SEUno =1 ). Otherwise the query
returned only seuno = 1.

DM

Depends on what you want to do really, but I suspect your intention is :

WHERE ( Market.SEUno = IIf(forms.fmChkList.cbseu=1, market.seuno,
Market.SEUno=forms.fmChkList.cbseu) )
Or ( Market.SEUno =1 )


Hoping it may help,
Vanderghast, Access MVP

The query shown below returns no records. The criteria entered direcly
without the iif performs as intended, e.g. '[forms].[fmChkList].[cbSeu] OR
1'
works if entered without the IIF expression, but as the value returned by
the
iif finds no records. Note: The appostrophes in my example are for
emphasis; the values are integers.

SELECT Sources.Name, Sources.SourceID, SourceStatus.Description AS Status,
Sources.Payment_required, Market.SEUno
FROM Market INNER JOIN (SourceStatus INNER JOIN Sources ON
SourceStatus.Status = Sources.Status) ON Market.SEUno = Sources.SEU
WHERE
(((Market.SEUno)=IIf([forms].[fmChkList].[cbseu]=1,[market].[seuno],([Market].[SEUno])=[forms].[fmChkList].[cbseu]
Or ([Market].[SEUno])=1)))
ORDER BY Sources.Name;

I've considered defiing and running a sql statement in the form code where
the correct criteria can be set as a string, but why doesn't the above
statement work?

DM