null fields

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

Hi,

I am trying to do a comparison in a query that says if the form's combobox
value is "No" then I want to display all values that have a null field for
that column else if the the form's combobox value is "Yes" then I want to
display all values that don't have a null field for that column.

Can I build this expression in the criteria for that field or do I have to
do something else.

Thanks
 
bbdobuddy said:
Hi,

I am trying to do a comparison in a query that says if the form's combobox
value is "No" then I want to display all values that have a null field for
that column else if the the form's combobox value is "Yes" then I want to
display all values that don't have a null field for that column.

Can I build this expression in the criteria for that field or do I have to
do something else.
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

Try this:

PARAMETERS Form!form_name!ComboBox_name TEXT;
SELECT *
FROM table_name
WHERE IIf(Form!form_name!ComboBox_name="No", column_name IS NULL,
column_Name IS NOT NULL)

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQhlApYechKqOuFEgEQL0dACfUwunmpxS3vUZvdXKxjy7x7a5k84An3GP
aqOUKcTcHUcJ7um2NJ52P14S
=oK21
-----END PGP SIGNATURE-----
 
I put IIf(Form!form_name!ComboBox_name="No", column_name IS NULL, column_Name
IS NOT NULL) in the SQL view on the query but it still doesn't work, any
other suggestions.
 
bbdobuddy said:
I put IIf(Form!form_name!ComboBox_name="No", column_name IS NULL, column_Name
IS NOT NULL) in the SQL view on the query but it still doesn't work, any
other suggestions.
Click to expand...

You have to use your own column names. IOW, what is the name of the
column that you want to search for NULLs? Put that in place of
"column_name."
 
Here is the exact code I wrote

IIF([forms]![frmAp]![cmbInput] = "No", (SupersededList.Item) IS NULL,
SupersededList.Item) IS NOT NULL)
 
What happens when the query is run that convinces you that it "doesn't
work"? Is an error returned, no data...?

The form "frmAp" must be open and valid data ("Yes" or "No") in
"cmbInput" when the query is run. The ComboBox's BoundColumn property
must be pointing at the column that contains the Yes/No word.

The code you posted (below) is missing an open parenthesis before the
SupesededList.Item in the 2nd line.

Post your complete SQL code so it can be analyzed.

--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

Here is the exact code I wrote

IIF([forms]![frmAp]![cmbInput] = "No", (SupersededList.Item) IS NULL,
SupersededList.Item) IS NOT NULL)

:

You have to use your own column names. IOW, what is the name of the
column that you want to search for NULLs? Put that in place of
"column_name."
Click to expand...
Click to expand...
 
That will never work. You can't put criteria operators (=, is not null, is
null) into the criteria in that manner.

You MIGHT be able to use.

SELECT *
FROM SupersededList
Where (Forms!FrmAp!cmbInput = "No" AND SupersededList.Item Is Null) Or
(Forms!FrmAp <> "No" and SupersededList.Item is Not Null)

Here is the exact code I wrote

IIF([forms]![frmAp]![cmbInput] = "No", (SupersededList.Item) IS NULL,
SupersededList.Item) IS NOT NULL)

MGFoster said:
You have to use your own column names. IOW, what is the name of the
column that you want to search for NULLs? Put that in place of
"column_name."
Click to expand...
Click to expand...
 
John said:
That will never work. You can't put criteria operators (=, is not null, is
null) into the criteria in that manner.

You MIGHT be able to use.

SELECT *
FROM SupersededList
Where (Forms!FrmAp!cmbInput = "No" AND SupersededList.Item Is Null) Or
(Forms!FrmAp <> "No" and SupersededList.Item is Not Null)
Click to expand...

-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm using Access 2002 w/ tables in Access 2000 format. Have option ANSI
92 checked. Use the following to create a table & load it w/ data:

create table t (
c char(1) ,
d date()
)

insert into t (t, d) values ('a', date())
insert into t (t) values ('b') -- column d is null
insert into t (t, d) values ('c', date())
insert into t (t) values ('d') -- column d is null

Created a form "frmTemp" w/ a ComboBox "cbo". The properties for the
ComboBox:
RowSourceType: ValueList
RowSource: No;Yes
BoundColumn: 1
ColumnCount: 1

I select "No" from the ComboBox & run the following query:

SELECT *
FROM t
WHERE iif(forms!frmtemp!cbo = "No", d is null, d is not null);

Result:

c d
==== ====
a
c

Column 'd' has NULL values.

I select "Yes" from the ComboBox & run the same query:

Result:

c d
==== ====
b 2/21/2005
d 2/21/2005

Column 'd' doesn't have NULL values.

QED
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQhqqEIechKqOuFEgEQKIqACggmibPTRMMe4Y8H9d01jAwoi1aSIAoJ5M
vEDT59Lh0hsu8p7QHa2jAyYh
=70Vg
-----END PGP SIGNATURE-----
 
Ypu're right, I'm wrong.
-----BEGIN PGP SIGNED MESSAGE-----
Hash: SHA1

I'm using Access 2002 w/ tables in Access 2000 format. Have option ANSI
92 checked. Use the following to create a table & load it w/ data:

create table t (
c char(1) ,
d date()
)

insert into t (t, d) values ('a', date())
insert into t (t) values ('b') -- column d is null
insert into t (t, d) values ('c', date())
insert into t (t) values ('d') -- column d is null

Created a form "frmTemp" w/ a ComboBox "cbo". The properties for the
ComboBox:
RowSourceType: ValueList
RowSource: No;Yes
BoundColumn: 1
ColumnCount: 1

I select "No" from the ComboBox & run the following query:

SELECT *
FROM t
WHERE iif(forms!frmtemp!cbo = "No", d is null, d is not null);

Result:

c d
==== ====
a
c

Column 'd' has NULL values.

I select "Yes" from the ComboBox & run the same query:

Result:

c d
==== ====
b 2/21/2005
d 2/21/2005

Column 'd' doesn't have NULL values.

QED
--
MGFoster:::mgf00 <at> earthlink <decimal-point> net
Oakland, CA (USA)

-----BEGIN PGP SIGNATURE-----
Version: PGP for Personal Privacy 5.0
Charset: noconv

iQA/AwUBQhqqEIechKqOuFEgEQKIqACggmibPTRMMe4Y8H9d01jAwoi1aSIAoJ5M
vEDT59Lh0hsu8p7QHa2jAyYh
=70Vg
-----END PGP SIGNATURE-----
Click to expand...
 
Back
Top