is null/not is null

[thread]

Hi all,
i'm trying to transfer criteria to a query via form but the problem is
that when the combo box is empty,the criteria consider it as "" while i
accually want to erase all data
what should be transfered to combo box in order to make all data to be
deleted
(in another words how can i transfer not is null to query's criteria)

 

[Michael J. Strickland]

Hi all,
i'm trying to transfer criteria to a query via form but the problem is
that when the combo box is empty,the criteria consider it as "" while i
accually want to erase all data
what should be transfered to combo box in order to make all data to be
deleted
(in another words how can i transfer not is null to query's criteria)

If I understand what you want, In a blank box of query designer, use:

Len(Trim([Field_Name]))

and in the Criteria box, use:
=0


--

 

[Guest]

An 'empty' control will normally be Null, so if you want to delete those rows
from a table which match the selection in the combo box, or delete all rows
if the combo box is Null, do like so:

DELETE *
FROM SomeTable
WHERE SomeField = Forms!frmSomeForm!cboSomeComboBox
OR Forms!frmSomeForm!cboSomeComboBox IS NULL;

This does seem a little dangerous to me as it would be very easy to delete
all rows inadvertently. The user can be to be forced to make a specific
selection for all rows in the combo box with a Resource along the lines of:

SELECT SomeField, 1 AS SortColumn
FROM SomeTable
UNION
SELECT "All Records", 0
FROM SomeTable
ORDER BY SortColumn, SomeField;

Which puts "All Records" at the top of the list. The query would then be:

DELETE *
FROM SomeTable
WHERE SomeField = Forms!frmSomeForm!cboSomeComboBox
OR Forms!frmSomeForm!cboSomeComboBox = "All Records";

SomeField would be of text data type in the above of course.

Ken Sheridan
Stafford, England