Criteria Issue

[RichardS]

I am new to the forums and I hope someone can help me with what seems like a simple issue. I need to exclude records in a query where the PMGenerated field equals N/A. Simple enough, I just use <> "N/A" and it works. However, when I try to use a booleon field (PMOnly) on a form to determine if I should exclude them, the query will not return any records, eventhough it should.

Here is the SQL view when using the criteria of <> "N/A" and the query works properly and returns 17 records.

SELECT LateWorkOrders.*, LateWorkOrders.PMGenerated


FROM LateWorkOrders
WHERE (((LateWorkOrders.PMGenerated)<>"N/A"));

When I add in the reference to the form, and try to get the query to select records that do not contain N/A when the PMOnly field is set to Y, the query will not return any records when it should return 17.

SELECT LateWorkOrders.*, LateWorkOrders.PMGenerated
FROM LateWorkOrders
WHERE (((LateWorkOrders.PMGenerated) Like IIf([Forms].[MainForm].[PMOnly]="Y",(LateWorkOrders.PMGenerated)<>"N/A","*")));

If I set PMOnly to N or anything else, it returns all records as it should.

This is probably something simple, but it is driving me nuts. Any help will be sincerely appreciated.

Richard

[HiTechCoach]

Richard,

First thing is you are return the same field twice.

SELECT LateWorkOrders.*, LateWorkOrders.PMGenerated
FROM LateWorkOrders

should be:

SELECT LateWorkOrders.* FROM LateWorkOrders

Your where clause logic goes not make sense.

If Forms].[MainForm].[PMOnly]="Y" is true the you would get this:

WHERE (((LateWorkOrders.PMGenerated) Like (LateWorkOrders.PMGenerated)<>"N/A";

This is not valid use of the Like


Try something like this

SELECT LateWorkOrders.*, 
IIf([Forms].[MainForm].[PMOnly]="Y",(LateWorkOrders.PMGenerated<>"N/A"), True) as Selected 
FROM LateWorkOrders WHERE Selected = True;

*** Warning *** Air Code *** Untested

[RichardS]

Thanks for the response. Unfortunately, this did not help me. I redid my query to get the criteria from a form control. I think this will be cleaner anyway, but I am still running up against the same problem.

My form control name is PMOnly.
My criteria in the query is Forms.MainForm.PMOnly

In my query, I need to be able to include or exclude records where Field PMGenerated equals or does not equal N/A. I can get the query to return the correct records when PMOnly is equal N/A.

But when I set the PMOnly control to <> "n/a", <> n/a, <> n/a, or not "n/a" the query does not return any records. If I hard code the criteria to <> "n/a" or not "n/a" it works fine, but not if the criteria references the form control with the same value. I am willing to bet this is an issue with quotes, but I cannot figure it out.

Can anyone assist me? Any help will be appreciated.

Richard

[HiTechCoach]

I am still not totally clear on what you are doing.

Again we need to see the SQL and probably more about the form design.

It would be really make it easier to help you if you would post a sample of the database.