IIF and ADODB.Recordset

[dandot]

I have a query in which the values in one column are defined by the following formula:

Compliane: IIf([OPCControl].[OPC Assertions] Like "*Compliance*","Yes","No")

When I run this query in access I get the correct value of Yes/No where applicable.

However, if I use the following code in my macro:

Dim rst As New ADODB.RecordSet

rst.Open strQuery, CurrentProject.Connection (where strQuery is the name of my query)
x1Ws.Cells (2,1).CopyFromRecordset rst

I always get No in this column.

Why is this happening?

FYI.... x1Ws is an excel application object.

 

[SkipVought]

Hi,

First of all, Cells, a range object is a property of a Worksheet, NOT the Applicaiton object.

Use % as the wildcard...

strQuery  = "...IIf([OPCControl].[OPC Assertions] Like "%Compliance%","Yes","No")..."

Skip,
[sub]
[red][/red]
[/sub]

 

[PHV]

And I'd use single quotes too:
strQuery = "... IIf(OPCControl.[OPC Assertions] Like '%Compliance%','Yes','No') AS Compliance ..."

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[SkipVought]

Oops...

Yes, as PHV as pointed out!

Skip,
[sub]
[red][/red]
[/sub]

 

[dandot]

Interesting it worked with the double quotes. Thanks for the help guys!