Solved-Trying to Delete empty records!

[turniporange23]

There is about 20 fields in each record, but if these 4 fields are all empty I want the record deleted. All the fields are Short text. At the moment I have a record that has 4 empty filds but it is not asking to Delete 1 Record.
Thanks for any help.......Bob

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

 

[theDBguy]

What happens when you run your delete query? If it's not picking up the empty fields/records, then perhaps you could try checking for nulls.

...WHERE HorseName Is Null AND FatherName Is Null AND etc...

 

[turniporange23]

Tried using Is Null same result, Thanks

 

[June7]

No need to list fields because entire record is deleted, although shouldn't hurt.

DELETE FROM mytable WHERE ...

If you want to provide db for analysis, follow instructions at bottom of my post.

I don't allow empty string in fields so don't have to deal with. A blank field would be Null or have a non-printing character such as CR.

Can you SELECT that one record with that criteria?

Check if those fields are really empty:
SELECT Len(HorseName) AS HN, Len(FatherName) AS FN, Len(MotherName) AS MN, Len(StableName) AS SN FROM tblHorseInfo WHERE ID = someID;

 

[theDBguy]

Tried using Is Null same result, Thanks

You didn't say what that result was. Did you get an error message? Did nothing happen? Also, can you please post the new sql code?

 

[DHookom]

I would set the Where clause to;
WHERE Trim(fld1 & fld2 & fld3 & fld4 &"")=""

 

[plog]

Often 'empty' isn't empty. Or even Null. Sometimes its a tab or a newline or any of the many characters that fool human eyes. I suggest you use the ASC and IsNull functions to determine what character is there:

MS Access: Asc Function

This MSAccess tutorial explains how to use the Access Asc function with syntax and examples. The Microsoft Access Asc function returns the NUMBER code that represents the specific character.

www.techonthenet.com

MS Access: IsNull Function

This MSAccess tutorial explains how to use the Access IsNull function with syntax and examples. The Microsoft Access IsNull function returns TRUE if the expression is a null value. Otherwise, it returns FALSE.

www.techonthenet.com

SELECT HorseName, ASC(HorseName) AS AscNumber, IsNull(HorseName) AS NullValue
FROM tblHorseInfo

Put a WHERE clause in there to isolate it to the record you know should have been caught by your DELETE query and run that to find out what is actually in there. Also, include the other fields as well, just too lazy to write all that SQL out.

Once you have that you can specifically tailor the WHERE clause of your DELETE to catch all of them.

 

[turniporange23]

DELETE tblHorseInfo.HorseName, tblHorseInfo.FatherName, tblHorseInfo.MotherName, tblHorseInfo.StableName, tblHorseInfo.HorseID
FROM tblHorseInfo
WHERE (((tblHorseInfo.HorseName)="") AND ((tblHorseInfo.FatherName)="") AND ((tblHorseInfo.MotherName)="") AND ((tblHorseInfo.StableName)=""));

Code says 0 records to delete but there is a record to delete, Thanks Bob

 

[turniporange23]

I Ran your code June7 , for some reasson record 218 does not have a zero, the top 3 records should be deleted

 

[isladogs]

Try this:

DELETE *
FROM tblHorseInfo
WHERE Nz(HorseName,"")="" AND Nz(FatherName,"")="" AND Nz(MotherName,"")="" AND Nz(StableName,"")="";

 

[tvanstiphout]

Not related to the Delete query, but re your database design: I question the StableName field in tblHorseInfo. Don't horses get traded all the time? Then you may need a new table tblStableHistory that records where the HorseID was from time to time.

 

[turniporange23]

Thanks Plog, This is my result

 

[turniporange23]

Not related to the Delete query, but re your database design: I question the StableName field in tblHorseInfo. Don't horses get traded all the time? Then you may need a new table tblStableHistory that records where the HorseID was from time to time.

Thanks Tom, Usally Horses keep there racing name unless they go to another country,This database is just for my stable so if the horse leaves I put that horse in finish mode, Actually Horse Name and Stable name are usally different. Example horse Racing Name "Perfect Dividends) and stable Name PD

 

[turniporange23]

Try this:

DELETE *
FROM tblHorseInfo
WHERE Nz(HorseName,"")="" AND Nz(FatherName,"")="" AND Nz(MotherName,"")="" AND Nz(StableName,"")="";

isladogs your a legend, Your code worked perfect deleted out the top 3 fields, Thanks for all your help everybody
Regards Bob

DELETE *<br>FROM tblHorseInfo<br>WHERE Nz(HorseName,"")="" AND Nz(FatherName,"")="" AND Nz(MotherName,"")="" AND Nz(StableName,"")="";

 

[theDBguy]

DELETE tblHorseInfo.HorseName, tblHorseInfo.FatherName, tblHorseInfo.MotherName, tblHorseInfo.StableName, tblHorseInfo.HorseID
FROM tblHorseInfo
WHERE (((tblHorseInfo.HorseName)="") AND ((tblHorseInfo.FatherName)="") AND ((tblHorseInfo.MotherName)="") AND ((tblHorseInfo.StableName)=""));

Code says 0 records to delete but there is a record to delete, Thanks Bob

Hmm, but that's not what I said to do. I said to check for nulls, and you said you did that. If so, what was the sql look like for it and what message did you get?

 

[turniporange23]

Ok one little problem ,when I double click on my query it works but when i enter in into my shut down button it does not delete the top 3 records, Thanks for any help ..Bob
qryDeleteNoDetails is my query

Private Sub cmdQuitSlow_Click()
Me.ckbUpdate = True
Forms!frmMain.Requery
DoCmd.SetWarnings False
DoCmd.SetWarnings False
DoCmd.OpenQuery "qryOrphanAddCharges"
DoCmd.OpenQuery "qryOrphanDaily"
DoCmd.OpenQuery "qryOrphanAddChargesInter"
DoCmd.OpenQuery "qryDeletedHorseDetails"
DoCmd.OpenQuery "QryDeleteDupHorseID"
DoCmd.OpenQuery "QryDeleteNoHorseID"
DoCmd.OpenQuery "QryDeleteInvoice"
DoCmd.OpenQuery "QryDeleteInvoiceNoNumber"
DoCmd.OpenQuery "QryDeleteChargeNoAmount"
DoCmd.OpenQuery "QryDeleteChargeNoID"
DoCmd.OpenQuery "QryDeleteHorseNoName"
DoCmd.OpenQuery "qryDeleteNoDetails"

DoCmd.SetWarnings True

TestBackup
DoCmd.Quit
End Sub

 

[turniporange23]

Hmm, but that's not what I said to do. I said to check for nulls, and you said you did that. If so, what was the sql look like for it and what message did you get?

When I entered the Nulls the query said there where no fields , Thanks

 

[theDBguy]

When I entered the Nulls the query said there where no fields , Thanks

I'd still like to see the sql statement to make sure we're on the same page. Please?

 

[turniporange23]

Hmm, but that's not what I said to do. I said to check for nulls, and you said you did that. If so, what was the sql look like for it and what message did you get?

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

This code says no records to delete and there should have been 3 records
Thanks DB GUY

 

[June7]

If Isladog's SQL works, then some of those 4 fields have Null and some have empty string.

Again, don't need fields in the DELETE clause, don't even need *, just: DELETE FROM myTable WHERE...