iif in access
-
Can iif be used to enter criteria in when statement returns true or return all records if false. I cant get the false statement to return any records at all. Essentially I only want criteria to be entered if the statement is true ... if its false I dont want criteria at all.
-
Can you post the query (at least pseudo-code) so that we can help developping it?
-
No. What you seem to be trying to do is construct dynamic SQL i.e.
iif(test, '> some value', 'like *')
and Access doesn't really like that. What you can do is to get the iif to generate a value that will allways be true (or false) . An example that I've used when comparing dates (some of which may be null) is to use a criteria like
>= iif(isnull([datefield]), datevalue,[datefield])
where the datevalue is chosen so that >=datevalue is always true e.g. a very early date, or always false a date long into the future e.g. 01/01/3005. The choice depends on the comparison operator outside the iif.
-
1. You have a statement that returns either True or False.
2. You have a IIf() expression for optional filter criteria.
3. If the statement (#1) is True, apply the filter criteria.
4. If the statement (#1) is False, display all records.
(this is supposed to look like the Access query grid, I'm no artist)
Field: | statement | field being filtered |
Crit 1:| True | IIf() expression |
Crit 2:| False | |
-
Essentially with Access the best way to do this is to have 2 queries. The first suffixed with _0 the second with _1. The _0 query contains your basic SQL that selects all records. In code behind your form for gathering the criteria, build a WHERE clause for a SQL statement. Open the _0 query, place the SQL statement into a string, remove the ; from the end, and append the WHERE clause. Open the _1 query and replace the SQL statement with the one you just built. Then open the report or form that is based on the _1 query.
By using the 2 queries, you never have to guess about what the base SQL statement is. And, you can debug problems with the SQL because you have the _1 query with the WHERE clause you created.
-
If I understand your question correctly I think this will give you what you want. Apply the criteria if there is any otherwise give you all the records.
-
You need to post text, the graphic does not work.
-
Thanks for the input -- I found a soulution to that particular problem.
I have a second question: Can you code SQL to delete the first set of criteria after running the query?
Ex: I would like to have the first string deleted each time the query is run.
Run 1 (after complete delete red)
WHERE (((Att.[18]) Like "A*") AND ((Att.[19]) Like "A*") AND ((Students.Drop)=No) AND ((Students.Cancel)=No)) OR (((Att.[19]) Like "A*") AND ((Att.[20]) Like "A*") AND ((Students.Drop)=No) AND ((Students.Cancel)=No)) OR (((Att.[20]) Like "A*") AND ((Att.[21]) Like "A*") AND ((Students.Drop)=No) AND ((Students.Cancel)=No)) OR (((Att.[21]) Like "A*") AND ((Att.[22]) Like "A*") AND ((Students.Drop)=No) AND ((Students.Cancel)=No))
Run 2 (after complete delete red)
WHERE (((Att.[19]) Like "A*") AND ((Att.[20]) Like "A*") AND ((Students.Drop)=No) AND ((Students.Cancel)=No)) OR (((Att.[20]) Like "A*") AND ((Att.[21]) Like "A*") AND ((Students.Drop)=No) AND ((Students.Cancel)=No)) OR (((Att.[21]) Like "A*") AND ((Att.[22]) Like "A*") AND ((Students.Drop)=No) AND ((Students.Cancel)=No))
-
If you do it like I told you to above, then you do not have to worry about deleting parts of it.
-
The problem is this - I dont want the query to return all records if the criteria isnt matched ... each day it needs query the next two fields for the same criteria (the fields that follow will always be NULL) ... the fields from the previous day shouldnt be requeried. Maybe I am missing something in your previous solution, but I dont see that working.
-
Gee, maybe if you had said that at the beginning it would have helped.
Sounds like you have a very poor design. First, column names should not be numbers alone. Second, you are violating the rules of normalization by doing it this way. You really just have a glorified Excel sheet.
Proper design would be something like this:
StudentAttendanceID Identity
StudentID Integer
AttendanceDate Date
Now you can do it for whatever date range you want to without worrying about Micky Mousing around with saving portions of the WHERE clause each time. And, the method that I told you will work.
-
I agree with Robert Stewart about the design issue. Separate point. Your query is unnecessarily complicated because of the repeated check on student cancelling or dropping. Ideally, you should have something like
- WHERE (All the Date related part) AND ((Students.Drop)=No) AND ((Students.Cancel)=No))
rather than
- WHERE ((Date related part1) AND ((Students.Drop)=No) AND ((Students.Cancel)=No))) OR
- ((Date related part2) AND ((Students.Drop)=No) AND ((Students.Cancel)=No))) OR
- ((Date related part3) AND ((Students.Drop)=No) AND ((Students.Cancel)=No)))
Secondly, I would tend to tie everything up together i.e.
- StudentAttendanceID Identity
StudentID Integer
AttendanceDate Date
CourseID Integer
- AttendanceMark Text
I would then have a table to explain the meaning of the marks e.g. Present, Late, Cancelled, Dropped and a Student Course Table which would include the student's course end date (May be null). This allows you to do a lot more analysis of things.
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply