Query and another field (1 Viewer)

kitty77

Registered User.
Local time
Today, 08:41
Joined
May 27, 2019
Messages
693
How can I do the following...

I have a query that brings back records with "aaa" or "bbb"
How can I get records with "aaa" or "bbb" and only are null from another field?

So, need "bbb" and null. How can I combine that code?

Hope this makes sense...
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:41
Joined
Jan 23, 2006
Messages
15,361
Please post your query SQL.
 

isladogs

MVP / VIP
Local time
Today, 12:41
Joined
Jan 14, 2017
Messages
18,186
For bbb and null, filter Field1 for 'bbb' and Field2 for Is Null

For aaa or bbb together with null, do the same as above but on a new filter row, enter 'aaa' and Is Null respectively
 

kitty77

Registered User.
Local time
Today, 08:41
Joined
May 27, 2019
Messages
693
I'm trying something like this... but I'm sure I'm missing something.

"AAA" Or "BBB" Or Iif(isnull([main].[field1]) and [main].[field2] = "CCC")
 

isladogs

MVP / VIP
Local time
Today, 12:41
Joined
Jan 14, 2017
Messages
18,186
I gave you two answers because your first post was unclear. Your reply gives different criteria again.
As already suggested provide your query sql and a clear indication of the filter criteria required. Some sample data and the desired result would also help.
 

kitty77

Registered User.
Local time
Today, 08:41
Joined
May 27, 2019
Messages
693
So, is something like this possible on one line?

"AAA" Or "BBB" Or Iif(isnull([main].[field1]) and [main].[field2] = "CCC")
 

jdraw

Super Moderator
Staff member
Local time
Today, 08:41
Joined
Jan 23, 2006
Messages
15,361
Post your current query SQL.
 

The_Doc_Man

Immoderate Moderator
Staff member
Local time
Today, 07:41
Joined
Feb 28, 2001
Messages
26,996
First, that syntax is bad SQL anyway.

It would have to resemble

Code:
Fieldx = "AAA" OR Fieldx = "BBB" OR ( IsNull(Field1) AND Field2 = "CCC" )

If you do this in the query grid, you would put "AAA" on line 1 of the critera under Fieldx, whatever it is. Put "BBB" on line 2 under the same field. You don't need an equals sign because that is the default for the criteria grid. However, that IsNull is s where it gets tricky. You have to create a new field that reads IsNull(Field1). Then on line 3 of the criteria grid, put TRUE under that field and put "CCC" under field2. IF it happens that fieldX is actually field 1, then YES the field will appear twice in the query - once as itself and once as the argument to an ISNULL() function.

If this doesn't make sense, kitty77, then you MUST find a way to post the full SQL or give us something else to go on. We are stabbing in the darkness here.
 

Users who are viewing this thread

Top Bottom