Using IIF and LIKE in Query Criteria

[Clearview]

This is my first post to the forum and hoping I can get help as I have spent a way too much time on this problem, and thinking it is probably something really simple to resolve.

I am using Access 2000 and trying to create a Query which draws its parameters from a Report Criteria Selection Form, which will define the data source for a report.

On a Report Selection Form if user enters a part# containing "PP"
then criteria will be for the exact part entered.
If part# entered does not contain "PP", then I want the results to do a Like Statement for all parts containing the text entered.

When I manually type a like statement e.g. Like "AB123*" in criteria of query design grid everything works fine, and I get AB123, AB123PP01, AB123PP07. But... when I try and pull the parameter from report selection form and add an IIF statement, I get no results.

Is Access 2000 not capable of using "Like" in an IIF statement? Is there any other way for me to accomplish this?

My Select statement looks like this:

SELECT Measurement.ProdId, Measurement.Subgrp_Num, Measurement.MeasDate, Measurement.MeasTime, Str([MeasDate])+" "+Str([MeasTime]) AS [DateTime], Measurement.CharName, Measurement.Subgrp_Order, Measurement.UserCont1, Measurement.UserCont2, Measurement.UserCont3, Measurement.Comment, Measurement.Measurement, Product.UserTitle1, Product.UserTitle2, Product.UserTitle3, Measurement.CorAction
FROM Measurement INNER JOIN Product ON Measurement.ProdId = Product.ProdId
WHERE (((Measurement.ProdId)=IIf(InStr(6,[Forms]![ReportSelection]![Part],"PP")>0,[Forms]![ReportSelection]![Part],([Measurement].[ProdId]) Like "[Forms]![ReportSelection]![Part]" & "*")) AND ((Measurement.MeasDate) Between [Forms]![ReportSelection]![BeginDate] And [Forms]![ReportSelection]![EndDate]))
ORDER BY Measurement.ProdId, Measurement.Subgrp_Num DESC , Measurement.CharName, Measurement.MeasDate, Measurement.MeasTime;

- unfortunately upgrading Access is not an option :(

If anyone could help, it would be much appreciated.

Thanks,
Mary

[MMcCarthy]

Hi Mary

Try this ...

SELECT Measurement.ProdId, Measurement.Subgrp_Num,
Measurement.MeasDate, Measurement.MeasTime, Str([MeasDate])+"
"+Str([MeasTime]) AS [DateTime], Measurement.CharName,
Measurement.Subgrp_Order, Measurement.UserCont1,
Measurement.UserCont2, Measurement.UserCont3,
Measurement.Comment, Measurement.Measurement,
Product.UserTitle1, Product.UserTitle2, Product.UserTitle3,
Measurement.CorAction
FROM Measurement INNER JOIN Product 
ON Measurement.ProdId = Product.ProdId
WHERE (((Measurement.ProdId)
Like IIf(InStr(6,[Forms]![ReportSelection]![Part],"PP")>0,
[Forms]![ReportSelection]![Part],
[Forms]![ReportSelection]![Part] & "*")) 
AND ((Measurement.MeasDate) 
Between [Forms]![ReportSelection]![BeginDate] 
And [Forms]![ReportSelection]![EndDate]))
ORDER BY Measurement.ProdId, Measurement.Subgrp_Num DESC,
Measurement.CharName, Measurement.MeasDate,
Measurement.MeasTime;

Mary

[Clearview]

Hi Mary

Try this ...

SELECT Measurement.ProdId, Measurement.Subgrp_Num,
Measurement.MeasDate, Measurement.MeasTime, Str([MeasDate])+"
"+Str([MeasTime]) AS [DateTime], Measurement.CharName,
Measurement.Subgrp_Order, Measurement.UserCont1,
Measurement.UserCont2, Measurement.UserCont3,
Measurement.Comment, Measurement.Measurement,
Product.UserTitle1, Product.UserTitle2, Product.UserTitle3,
Measurement.CorAction
FROM Measurement INNER JOIN Product 
ON Measurement.ProdId = Product.ProdId
WHERE (((Measurement.ProdId)
Like IIf(InStr(6,[Forms]![ReportSelection]![Part],"PP")>0,
[Forms]![ReportSelection]![Part],
[Forms]![ReportSelection]![Part] & "*")) 
AND ((Measurement.MeasDate) 
Between [Forms]![ReportSelection]![BeginDate] 
And [Forms]![ReportSelection]![EndDate]))
ORDER BY Measurement.ProdId, Measurement.Subgrp_Num DESC,
Measurement.CharName, Measurement.MeasDate,
Measurement.MeasTime;

Mary

It Worked! Thanks so much!

[MMcCarthy]

It Worked! Thanks so much!

No problem.

Glad to help.

Mary