Query issue

[jo]

Hi I have a query that will show wot has been done in the last 15 days but I
want another query to show wot has not been done? I just cant think how to
work my expression to show this?
Jo

 

[Dale Fye]

Well, it would help if you told us your table structure, and how you define
what has or has not been done.

My guess is that you have a date field (we'll call it WorkDate) and you are
querying to determine whether the value in that field is within the last 15
days. If that fields default value is NULL, then to identify records where no
work has been done, you would do something like:

SELECT * FROM yourTable where [WorkDate] IS NULL

 

[jo]

Hi Dale, my query has a [Date] field with the expression >Date()-15 and
another field that the user will enter the channel they have checked
[Channel], the results are based on the user entering data so if a channel is
not checked there would not be IsNull result or anything there. Would I need
is an expression that can sort out the channels that did not get checked. I
have tried the following but no luck?
Expr1:
IIf([Channel]="2",IIf([Channel]="4",IIf([Channel]="6",IIf([Channel]="8",IIf([Channel]="7",0,1)))))

Well, it would help if you told us your table structure, and how you define
what has or has not been done.

My guess is that you have a date field (we'll call it WorkDate) and you are
querying to determine whether the value in that field is within the last 15
days. If that fields default value is NULL, then to identify records where no
work has been done, you would do something like:

SELECT * FROM yourTable where [WorkDate] IS NULL

----
HTH
Dale

Hi I have a query that will show wot has been done in the last 15 days but I
want another query to show wot has not been done? I just cant think how to
work my expression to show this?
Jo

 

[Dale Fye]

Jo,

First, it is a bad idea to have a field called "Date". This is a reserved
word in Access, and should not be used as a the name of a table, field, or
even a variable. If you want to know the entire list of reserved words,
check out: http://www.allenbrowne.com/AppIssueBadWord.html

Your original post indicated that you have a query that shows what has been
done in the last 15 days, so, why don't you start by posting the SQL of the
query that works.

Your original question doesn't say anything at all about this [Channel]
field, or how it is "checked". What do you mean by "channels that did not
get checked"?

The iif() statement you have written can be interpreted as:

If [Channel] = 2 AND [Channel] = 4 AND [Channel] = 6 AND [Channel] = 8 AND
[Channel] = 7 then return a zero (0), otherwise, return a 1.

Since the value in the [Channel] field cannot have all of these values in a
single record, the IIF() statement will return 1 in all cases. Try to
explain (words) what functionality you are trying to achieve, and I'll see if
we can get you going down the right track.

----
HTH
Dale

Hi Dale, my query has a [Date] field with the expression >Date()-15 and
another field that the user will enter the channel they have checked
[Channel], the results are based on the user entering data so if a channel is
not checked there would not be IsNull result or anything there. Would I need
is an expression that can sort out the channels that did not get checked. I
have tried the following but no luck?
Expr1:
IIf([Channel]="2",IIf([Channel]="4",IIf([Channel]="6",IIf([Channel]="8",IIf([Channel]="7",0,1)))))

Well, it would help if you told us your table structure, and how you define
what has or has not been done.

My guess is that you have a date field (we'll call it WorkDate) and you are
querying to determine whether the value in that field is within the last 15
days. If that fields default value is NULL, then to identify records where no
work has been done, you would do something like:

SELECT * FROM yourTable where [WorkDate] IS NULL

----
HTH
Dale

Hi I have a query that will show wot has been done in the last 15 days but I
want another query to show wot has not been done? I just cant think how to
work my expression to show this?
Jo

 

[jo]

dale, I am a novice with Access and learning a lot along the way with the
help of you guys so plz bear with me...
Users enter figures from a measuring equipment and my database will
calculate whether the results are in spec. There are production lines
[Channels] that sholud be inspected every shift. The query at the moment
shows what has been inspected in the last 15 days, I need to know what
[Channels] has NOT been inspected.
So the query at the moment has [Date] with the criteria >Date()-15 there are
other fields that will show other info. But what I need to see is when a
[Channel] has not been done. Is there any way the query can sort through the
[Channel] field for missing channels. The user would put in e.g PC8 or PC6 or
PC4 etc .
There are no other expressions in this query apart from above.
Hope I making a bit more sense?

Jo,

First, it is a bad idea to have a field called "Date". This is a reserved
word in Access, and should not be used as a the name of a table, field, or
even a variable. If you want to know the entire list of reserved words,
check out: http://www.allenbrowne.com/AppIssueBadWord.html

Your original post indicated that you have a query that shows what has been
done in the last 15 days, so, why don't you start by posting the SQL of the
query that works.

Your original question doesn't say anything at all about this [Channel]
field, or how it is "checked". What do you mean by "channels that did not
get checked"?

The iif() statement you have written can be interpreted as:

If [Channel] = 2 AND [Channel] = 4 AND [Channel] = 6 AND [Channel] = 8 AND
[Channel] = 7 then return a zero (0), otherwise, return a 1.

Since the value in the [Channel] field cannot have all of these values in a
single record, the IIF() statement will return 1 in all cases. Try to
explain (words) what functionality you are trying to achieve, and I'll see if
we can get you going down the right track.

----
HTH
Dale

Hi Dale, my query has a [Date] field with the expression >Date()-15 and
another field that the user will enter the channel they have checked
[Channel], the results are based on the user entering data so if a channel is
not checked there would not be IsNull result or anything there. Would I need
is an expression that can sort out the channels that did not get checked. I
have tried the following but no luck?
Expr1:
IIf([Channel]="2",IIf([Channel]="4",IIf([Channel]="6",IIf([Channel]="8",IIf([Channel]="7",0,1)))))

Well, it would help if you told us your table structure, and how you define
what has or has not been done.

My guess is that you have a date field (we'll call it WorkDate) and you are
querying to determine whether the value in that field is within the last 15
days. If that fields default value is NULL, then to identify records where no
work has been done, you would do something like:

SELECT * FROM yourTable where [WorkDate] IS NULL

----
HTH
Dale



:

Hi I have a query that will show wot has been done in the last 15 days but I
want another query to show wot has not been done? I just cant think how to
work my expression to show this?
Jo