How to runSQL to delete record when empty.

[nmapeso]

I have a table "tblAgent" with following fieldname

AgentID - Autonumber
Name - text
Agent_Code - text

Need to runSQL to delete record when Name & Agen_Code are empty. I try this thiss but not sure what syntax on WHERE.

DoCmd.RunSQL "DELETE * FROM tblAgent WHRERE tblAgent.Agent_Code = ????" not sure what syntax on where.

Thanks for Anyhelp

 

[PCLewis]

The only strange "value" is NULL. If you want to delete records where tblAgent.Agent_code is null then your syntax is "DoCmd.RunSQL "DELETE * FROM tblAgent WHRERE tblAgent.Agent_Code IS NULL"

All other value's use the = sign like you have.

 

[PHV]

delete record when Name & Agen_Code are empty
DoCmd.RunSQL "DELETE FROM tblAgent WHERE Trim(Agent_Code & [Name] & '') = ''"

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[TheAceMan1]

How are ya nmapeso . . . . .

First try this:

[blue]   Dim SQL As String
   
   SQL = "DELETE AgentID " & _
         "FROM tblAgent " & _
         "WHERE Name Is Null AND " & _
               "Agent_Code Is Null"
   DoCmd.RunSQL SQL[/blue]

Since an ounce of prevention is worth a pound of cure, to your knowledge, [blue]how are these orphaned records[/blue] (records with AgentID and no other data) [blue]coming about?[/blue]

[purple]You really need to correct this problem at the source . . .[/purple]

See Ya! . . . . . .

 

[nmapeso]

Thank you all for the help.