if statement

[losthome]

Hi There,

I am having major problems with getting my query to filter data using a yes/no field.

What I am trying to do is in the form, I click mark off employees from the "Select" field to say that I have completed their 90 day review. But once I check mark somebody off, I don't want them to appear anymore. If I use a delete query, it deletes the record from the table, which is not what I want.

My query is pulling the names from my employee table and the "Select" and "Date" fields are coming from my 90 day review table.

I have tried to put NO in the select field, then it either doesn't show any records (and yes I have the relationship proper) or it shows the records, but then won't save any of the records that I check mark off.

Can I put an if statment in the query, IF Select = true, then don't show those records, but show the false records?

Any suggestions?

Thank you,

Jennifer

 

[Pat Hartman]

You can use - 0 - as the criteria to check for the false value.

BTW, what happened to the other thread?
http://www.access-programmers.co.uk/forums/showthread.php?s=&threadid=33949

 

[losthome]

Hi Pat,

Thank you for your help. You must be an expert with Access since you help so many of us beginners out

My last thread that I posted, I took your advise and stopped trying to do things the hard way and simplifed my query. I have the data saving to a table now.

But I still can figure out how to just see the people who don't have check marks on their names.

If I set the criteria to 0, no records show up???

Jennifer

 

[boblarson]

You say that you can get the records with NO in the criteria but that it won't save when you edit the checks to YES. That would indicate to me that you have a query that is non-updateable and that you're either using grouping within the query or the joins are not correct (or there is another table in there that isn't joined).

If what I just said doesn't help you track down the problem, can you provide a list of which tables are in your query along with the fields? We might be able to spot your problem. Or, you can post the query SQL and that would help too.

Good luck!

 

[losthome]

Here is the sql view code. I put no in the Select field and no records show up.

SELECT qry90dayreview.EmployeeID, [FirstName] & " " & [LastName] AS Employee, [tbl-Employee Info].EmployeeStartDate, IIf((([EmployeeStartDate]+90)>=(Date()-14)) And (([EmployeeStartDate]+90)<Date()),"Time for meeting",IIf(([EmployeeStartDate]+90)<Date(),"MEETING OVERDUE!",Null)) AS MeetingTime, qry90dayreview.[Select], qry90dayreview.ReviewDate AS LastReview
FROM [tbl-Employee Info] LEFT JOIN qry90dayreview ON [tbl-Employee Info].EmployeeID = qry90dayreview.EmployeeID
WHERE (((IIf((([EmployeeStartDate]+90)>=(Date()-14)) And (([EmployeeStartDate]+90)<Date()),"Time for meeting",IIf(([EmployeeStartDate]+90)<Date(),"MEETING OVERDUE!",Null)))="Time for meeting" Or (IIf((([EmployeeStartDate]+90)>=(Date()-14)) And (([EmployeeStartDate]+90)<Date()),"Time for meeting",IIf(([EmployeeStartDate]+90)<Date(),"MEETING OVERDUE!",Null)))="MEETING OVERDUE!") AND ((qry90dayreview.[Select])=No))
ORDER BY [tbl-Employee Info].LastName;

Any suggestions?

Jennifer