How to delete everything but blanks

[Calvary]

Hi all,

I'm trying to write a query to search a column for cells with data in it and delete the row and only keep rows that have blanks in the same column.

The current SQL code I've written returns a syntax error. Can someone take a look at it and tell me if there is a way to re write this in order of rit to function in the desired way.

DELETE [R6 Letter file].BILLING_ACCOUNT_ID, [R6 Letter file].CUSTOMER_TYPE1, [R6 Letter file].DATE_OF_NEXT_STEP1, [R6 Letter file].ACCOUNT_STATUS, [R6 Letter file].DATE_OF_BIRTH, [R6 Letter file].CREDIT_CLASS, [R6 Letter file].BALANCE, [R6 Letter file].OVERDUEBALANCE, [R6 Letter file].WRITEOFFBALANCE, [R6 Letter file].DUNNING_STATUS_KEY, [R6 Letter file].LAST_STATUS_CHANGE, [R6 Letter file].LENGTH_OF_STATUS, [R6 Letter file].NEXT_STEP, [R6 Letter file].DATE_OF_NEXT_STEP, [R6 Letter file].RULE_IN_PROGRESS, [R6 Letter file].EXTENSION_DUE_DATE, [R6 Letter file].PAUSED, [R6 Letter file].CUSTOMER_NAME, [R6 Letter file].PHONE_NUMBER_DAYTIME, [R6 Letter file].PHONE_NUMBER_EVENING, [R6 Letter file].MOBILE_NUMBER, [R6 Letter file].EMAIL_ADDRESS, [R6 Letter file].BILLING_ADDRESS_LINE_1, [R6 Letter file].BILLING_ADDRESS_LINE_2, [R6 Letter file].BILLING_ADDRESS_LINE_3, [R6 Letter file].BILLING_ADDRESS_LINE_4, [R6 Letter file].BILLING_ADDRESS_COUNTY, [R6 Letter file].PAYMENT_METHOD, [R6 Letter file].PAPERLESS_BILLING
FROM [R6 Letter file]
WHERE ((([R6 Letter file].RULE_IN_PROGRESS)= is not ""));

Thanks in advance,

Cal

 

[isladogs]

See change to final line

DELETE [R6 Letter file].BILLING_ACCOUNT_ID, [R6 Letter file].CUSTOMER_TYPE1, [R6 Letter file].DATE_OF_NEXT_STEP1, [R6 Letter file].ACCOUNT_STATUS, [R6 Letter file].DATE_OF_BIRTH, [R6 Letter file].CREDIT_CLASS, [R6 Letter file].BALANCE, [R6 Letter file].OVERDUEBALANCE, [R6 Letter file].WRITEOFFBALANCE, [R6 Letter file].DUNNING_STATUS_KEY, [R6 Letter file].LAST_STATUS_CHANGE, [R6 Letter file].LENGTH_OF_STATUS, [R6 Letter file].NEXT_STEP, [R6 Letter file].DATE_OF_NEXT_STEP, [R6 Letter file].RULE_IN_PROGRESS, [R6 Letter file].EXTENSION_DUE_DATE, [R6 Letter file].PAUSED, [R6 Letter file].CUSTOMER_NAME, [R6 Letter file].PHONE_NUMBER_DAYTIME, [R6 Letter file].PHONE_NUMBER_EVENING, [R6 Letter file].MOBILE_NUMBER, [R6 Letter file].EMAIL_ADDRESS, [R6 Letter file].BILLING_ADDRESS_LINE_1, [R6 Letter file].BILLING_ADDRESS_LINE_2, [R6 Letter file].BILLING_ADDRESS_LINE_3, [R6 Letter file].BILLING_ADDRESS_LINE_4, [R6 Letter file].BILLING_ADDRESS_COUNTY, [R6 Letter file].PAYMENT_METHOD, [R6 Letter file].PAPERLESS_BILLING
FROM [R6 Letter file]
WHERE ((([R6 Letter file].RULE_IN_PROGRESS)<> ""));

 

[Galaxiom]

Also be aware that there are two types of "blanks", Null and Null String.

Null is nothing at all whole the Null String is a zero length string.

This condition will not find Nulls:

WHERE [R6 Letter file].RULE_IN_PROGRESS <> ""

In most circumstances, Access will convert Null String to Null. However use this to be sure:

WHERE [R6 Letter file].RULE_IN_PROGRESS <> "" OR [R6 Letter file].RULE_IN_PROGRESS Is Null