Delete query problem!

Question

If these 4 text fields are empty, I want to delete the record from the database. This code is not working

Thanks for any help..................Bob

DELETE tblHorseInfo.HorseName, tblHorseInfo.FatherName, tblHorseInfo.MotherName, tblHorseInfo.StableName, *
FROM tblHorseInfo
WHERE (((tblHorseInfo.HorseName) Is Null) AND ((tblHorseInfo.FatherName) Is Null) AND ((tblHorseInfo.MotherName) Is Null) AND ((tblHorseInfo.StableName) Is Null));

---

xxx

Answer 1

Try

DELETE 
FROM tblHorseInfo
WHERE ((tblHorseInfo.HorseName Is Null) AND (tblHorseInfo.FatherName Is Null) AND (tblHorseInfo.MotherName Is Null) AND (tblHorseInfo.StableName Is Null));

Instead of null, could they hold zero length strings?

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 2

Thanks Daniel, I have 3 records that should be deleted but it said I had 0 records to delete

Regards Bob

---

xxx

Answer 3

I have 3 records that should be deleted but it said I had 0 records to delete

That suggests that one or more of the column positions in those rows has a zero-length string as its value, rather than being Null.  Try setting the AllowZeroLength property of each column to False (No).  If Access won't let you do this that would confirm my suspicions.

You can correct this by executing the following UPDATE statements:

UPDATE tblHorseInfo
SET HorseName = NULL
WHERE LEN(HorseName) = 0;

UPDATE tblHorseInfo
SET FatherName = NULL
WHERE LEN(FatherName) = 0;

UPDATE tblHorseInfo
SET MotherName = NULL
WHERE LEN(MotherName) = 0;

UPDATE tblHorseInfo
SET StableName = NULL
WHERE LEN(StableName) = 0;

Following which you can then set the AllowZeroLength property of each of the four columns to False.

---

Ken Sheridan, Stafford, England

Answer 4

Thanks Ken. all 4 fields have Zero Length set at yes, Trouble is my friends have same database set up as me, So they cant change theirs,  Regards Bob

---

xxx

Answer 5

Or should I go a dont save record if 

tbHorsename, tbFatherName, tbMothername, tbStablemname 

are all null?

Thanks for any help.............Bob

---

xxx

Answer 6

Tried this but it still saved the record when tested...............Thanks Bob

 If IsNull(tbName) And IsNull(cbFatherName)And IsNull(CbMotherName)And IsNull(tbStableName) Then

   If Me.Dirty Then
     Me.Undo
   End If
End If
  
    DoCmd.Close acForm, Me.Name

---

xxx

Answer 7

Trouble is my friends have same database set up as me, So they cant change theirs

Even so, there's nothing to stop you changing any zero-length strings in the columns to Nulls by executing the four UPDATE statements I posted.  That won't stop the problem reoccurring though.  So you could cover both bases in the query with:

WHERE LEN(NZ(HorseName,"")) = 0 AND LEN(NZ(FatherName,"")) = 0
AND LEN(NZ(MotherName,"")) = 0 AND LEN(NZ(StableName,"")) = 0;

---

Ken Sheridan, Stafford, England

Answer 8

Or should I go a dont save record if 

tbHorsename, tbFatherName, tbMothername, tbStablemname 

are all null?

Hi Bob,

You could use  something like:

    If (tbHorseName > "") And (tbFatherName > "") And (tbMotherName > "") And (tbStableName> "") Then
        'Save the record
    Else
        'Don't save the record
    End.

The construction (tbHorseName > "") both covers the check for :    IS NULL and:    = "".

In my applications I use an unbound form to gather all data for new records. Only after all the checks are succesful, the record is stored.

Big advantage of this approach is that the locking of the record is very short, and only on the moment of saving.

Further, you can do all kind of checks with respect to key violation, obligatory fields, field length, type violation, field values in relation to other field values, and many,many more if necessary.

Imb

Answer 9

Try using:

DELETE DISTINCTROW tblHorseInfo.HorseName, tblHorseInfo.FatherName, tblHorseInfo.MotherName, tblHorseInfo.StableName,
FROM tblHorseInfo
WHERE (((tblHorseInfo.HorseName) Is Null) AND ((tblHorseInfo.FatherName) Is Null) AND ((tblHorseInfo.MotherName) Is Null) AND ((tblHorseInfo.StableName) Is Null));

Answer 10

Thank Imb,

But if any off these fields have data the record can be saved, only if all 4 fields are empty, I dont want the record to be saved................Thanks Bob

---

xxx

Answer 11

Thanks Lawrence, FROM is giving me a Syntax error...............Thanks Bob

---

xxx

Answer 12

Then why not stop the table to not accept zero length strings.  You can also use form events to validate the data entered and cancel saving the record of all the controls are empty.

Better to stop improper input rather than trying to cleanup things after the fact. 

---

Daniel Pineault, 2010-2019 Microsoft MVP
Professional Support: http://www.cardaconsultants.com
MS Access Tips and Code Samples: http://www.devhut.net

Answer 13

But if any off these fields have data the record can be saved, only if all 4 fields are empty, I dont want the record to be saved................Thanks

Hi Bob,

I see, a slip of the pen.

If at least one the fields may have a value, you can use:

    If (tbHorseName > "") Or (tbFatherName > "") Or (tbMotherName > "") Or (tbStableName> "") Then
        'Save the record
    Else
        'Don't save the record
    End.

Imb,

Answer 14

But if any off these fields have data the record can be saved, only if all 4 fields are empty, I dont want the record to be saved

When you say 'saved' do you mean 'not deleted'?  Your original question related to a DELETE query not an INSERT INTO statement.

Did you try using the LEN and NZ functions as I suggested?

---

Ken Sheridan, Stafford, England

Answer 15

I think what may have happened here is that the DELETE query worked the first time it was run and so when you ran it again, of course, no records were shown that needed to be deleted under your criteria because they had already been deleted. Are you absolutely sure there should be records in your table that meets the criteria in the query and should be deleted?

Answer 16

Hi Lawrence, Here is a screen shoot you will see the 2 top records are blank

Regards Bob

---

xxx

Answer 17

What happens if you Click 'Yes'? Are the records deleted or not? Can you delete the records manually? In other words, select the row and hit the Delete key? What happens if you do that? If that doesn't work either, then the query may not have 'Unique Records' set to 'Yes'.

Answer 18

.................. you will see the 2 top records are blank

The column positions in those rows could be Null or they could contain zero-length strings. The delete criteria in the query need to take account of both possibilities, which you can do by calling the NZ function to return a zero length sting in lieu of a Null as  I described.  You can then test either for the return value being a zero-length string, "", or by means of the LEN function, having a length of zero.

Before executing the query, open it as a datasheet to see if it returns the rows you expect to be deleted.

---

Ken Sheridan, Stafford, England

Answer 19

Change the code slightly to "" and not >"" and it worked. But will try and find a code as to not save the record if these 4 fields are empty when saving the record............Thanks Bob

SELECT tblHorseInfo.HorseName, tblHorseInfo.FatherName, tblHorseInfo.MotherName, tblHorseInfo.StableName, *
FROM tblHorseInfo
WHERE (((tblHorseInfo.HorseName)="") AND ((tblHorseInfo.FatherName)="") AND ((tblHorseInfo.MotherName)="") AND ((tblHorseInfo.StableName)=""));

---

xxx

Answer 20

Change the code slightly to "" and not >"" and it worked.

Hi Bob,

If the above works for you, than you only have ZLS's in your tables, and no Null's.

When you have to deal with Null's, then any comparison will yield a Null value, and the Boolean result can never be True, Or you must be some magician.

To check for a value you can use:     (field) > "".

To check for NO value (Null or ZLS) you can use:    (field) & "" = "".

Imb.

Answer 21

Thanks Guys for your help, I have managed to not save the record if those 4 fields are blank

Regards Bob

Private Sub cmdClose_Click()
   If (tbName = "") And (tbFatherName = "") And (tbMotherName = "") And (tbStableName = "") Then

Me.Undo
DoCmd.Close acForm, Me.Name
Else
 DoCmd.RunCommand acCmdSaveRecord

       

---

xxx