By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,061 Members | 1,515 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,061 IT Pros & Developers. It's quick & easy.

Using IIF and LIKE in Query Criteria

P: 2
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:

Expand|Select|Wrap|Line Numbers
  1. 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
  2. FROM Measurement INNER JOIN Product ON Measurement.ProdId = Product.ProdId
  3. 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]))
  4. 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
Mar 17 '07 #1
Share this Question
Share on Google+
3 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Hi Mary

Try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT Measurement.ProdId, Measurement.Subgrp_Num,
  2. Measurement.MeasDate, Measurement.MeasTime, Str([MeasDate])+"
  3. "+Str([MeasTime]) AS [DateTime], Measurement.CharName,
  4. Measurement.Subgrp_Order, Measurement.UserCont1,
  5. Measurement.UserCont2, Measurement.UserCont3,
  6. Measurement.Comment, Measurement.Measurement,
  7. Product.UserTitle1, Product.UserTitle2, Product.UserTitle3,
  8. Measurement.CorAction
  9. FROM Measurement INNER JOIN Product 
  10. ON Measurement.ProdId = Product.ProdId
  11. WHERE (((Measurement.ProdId)
  12. Like IIf(InStr(6,[Forms]![ReportSelection]![Part],"PP")>0,
  13. [Forms]![ReportSelection]![Part],
  14. [Forms]![ReportSelection]![Part] & "*")) 
  15. AND ((Measurement.MeasDate) 
  16. Between [Forms]![ReportSelection]![BeginDate] 
  17. And [Forms]![ReportSelection]![EndDate]))
  18. ORDER BY Measurement.ProdId, Measurement.Subgrp_Num DESC,
  19. Measurement.CharName, Measurement.MeasDate,
  20. Measurement.MeasTime;
Mary
Mar 17 '07 #2

P: 2
Hi Mary

Try this ...

Expand|Select|Wrap|Line Numbers
  1. SELECT Measurement.ProdId, Measurement.Subgrp_Num,
  2. Measurement.MeasDate, Measurement.MeasTime, Str([MeasDate])+"
  3. "+Str([MeasTime]) AS [DateTime], Measurement.CharName,
  4. Measurement.Subgrp_Order, Measurement.UserCont1,
  5. Measurement.UserCont2, Measurement.UserCont3,
  6. Measurement.Comment, Measurement.Measurement,
  7. Product.UserTitle1, Product.UserTitle2, Product.UserTitle3,
  8. Measurement.CorAction
  9. FROM Measurement INNER JOIN Product 
  10. ON Measurement.ProdId = Product.ProdId
  11. WHERE (((Measurement.ProdId)
  12. Like IIf(InStr(6,[Forms]![ReportSelection]![Part],"PP")>0,
  13. [Forms]![ReportSelection]![Part],
  14. [Forms]![ReportSelection]![Part] & "*")) 
  15. AND ((Measurement.MeasDate) 
  16. Between [Forms]![ReportSelection]![BeginDate] 
  17. And [Forms]![ReportSelection]![EndDate]))
  18. ORDER BY Measurement.ProdId, Measurement.Subgrp_Num DESC,
  19. Measurement.CharName, Measurement.MeasDate,
  20. Measurement.MeasTime;
Mary
It Worked! Thanks so much!
Mar 17 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
It Worked! Thanks so much!
No problem.

Glad to help.

Mary
Mar 18 '07 #4

Post your reply

Sign in to post your reply or Sign up for a free account.