Multiple Criteria Query Help

[china99boy]

Hi,

I have 3 fields that I need to run a query on. Date_Image, IMAGE_SYSTEM and DATE_TO_BR. If there is no entry in either fields, it should be part of the query. If entry is in both Date_Image and Image_system, I do not need those results in the query. If Date_to_br field is empty, I must have an entry in the other 2 fields before this record is not displayed in the query. I hope I was able to explain this clear enough. I was trying to do this with the IS null and Is Not Null expressions, but I am not getting the results I want.

 

[motleyjew]

In your word example you have Image_system as IS Null but i think your meant IS NOT NULL if there should be data in this field

 

[stopher]

I have 3 fields that I need to run a query on. Date_Image, IMAGE_SYSTEM and DATE_TO_BR. If there is no entry in either fields, it should be part of the query. If entry is in both Date_Image and Image_system, I do not need those results in the query. If Date_to_br field is empty, I must have an entry in the other 2 fields before this record is not displayed in the query. I hope I was able to explain this clear enough. I was trying to do this with the IS null and Is Not Null expressions, but I am not getting the results I want.

If I understand this correctly, I think you want:
All records where either Date_Image is null OR IMAGE_SYSTEM is null (or both are null)

The value (or lack of value) of DATE_TO_BR is irrelevant.

So the SQL will be:

SELECT APPROACH.Date_Image, APPROACH.IMAGE_SYSTEM, APPROACH.DATE_TO_BR
FROM APPROACH
WHERE APPROACH.Date_Image Is Null OR APPROACH.IMAGE_SYSTEM Is Null

And the designer view will look like the attached.

hth
Stopher

 

[china99boy]

Actually if there is an entry in the DATE_TO_BR field, it should not show those results. But if nothing is in the date_to_Br and there is an entry in both the other two, then those records should not be displayed. I know it is confusing, but I appreciate the help that you have provided.

 

[stopher]

Ok, I admit I'm a little lost as to the exact logic you are looking for. Here's my guess and referencing your points:

1. If there is no entry in either fields, it should be part of the query

2. But if nothing is in the date_to_Br and there is an entry in both the other two, then those records should not be displayed

3. Actually if there is an entry in the DATE_TO_BR field, it should not show those results.

4. If Date_to_br field is empty, I must have an entry in the other 2 fields before this record is not displayed in the query. [must both have a value]

Date_Image___IMAGE_SYSTEM___DATE_TO_BR____Show___ref
___null__________null_____________null__________Yes____1.
___null__________null____________Value__________No____3.
___null_________Value____________null__________Yes____4.
___null_________Value___________Value__________No____3.
__Value_________null_____________null__________Yes____4.
__Value_________null____________Value__________No____3.
__Value________Value____________null___________No____2.
__Value________Value___________Value__________No____3.

This boils down to:
IF DATE_TO_BR is null AND (Date_Image is null OR IMAGE_SYSTEM is null) then show

The SQL:

SELECT Date_Image, IMAGE_SYSTEM, DATE_TO_BR
FROM APPROACH
WHERE DATE_TO_BR is null AND (Date_Image Is Null OR IMAGE_SYSTEM Is Null)

And the picture attached

At least I think that's right.

Stopher

 

[Brianwarnock]

I would write a Function as below, your field names have been abbreviated

Function required(difld As Variant, isfld As Variant, dbfld As Variant) As Variant
required = "yes"
'Now enter the conditions for a "no" , i.e. not required, I'm not sure but I think the following apply

If Not IsNull(dbfld) Then required = "no"
If IsNull(dbfld) And (IsNull(difld) And IsNull(isfld)) Then required = "no"

End Function


Then in the query req:required(difld,isfld,dbfld) would be tested for "yes", but need not be selected to appear in the query's output.

Brian

Edit I think Stopher has cracked it, the thinking about what's not required rather than what is had me up the wall. hence my approach.

 

[stopher]

the thinking about what's not required rather than what is had me up the wall.

Me too. Even now I'm not unsure if i'm not wrong

 

[Brianwarnock]

Me too. Even now I'm not unsure if i'm not wrong

Brian