Deleting a Record From Table

[BobV]

Group:

I want to write code that will delete a record from the table named
MasterData if the Name field is blank. I have been trying the following code
but it doesn't work. What am I missing?

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] = """

Any help will be greatly appreciated.

Thanks,
BobV

 

[Tom Lake]

Group:

I want to write code that will delete a record from the table named
MasterData if the Name field is blank. I have been trying the following
code but it doesn't work. What am I missing?

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] = """

Try this:

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] Is Null"

Strings that haven't had a value assigned yet are Null rather than ""

Tom Lake

 

[BobV]

Tom:

I tried your suggestion, but it still doesn't work -- the record is not
deleted. Any other suggestions?

Thanks,
BobV

Group:

I want to write code that will delete a record from the table named
MasterData if the Name field is blank. I have been trying the following
code but it doesn't work. What am I missing?

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] = """

Try this:

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] Is Null"

Strings that haven't had a value assigned yet are Null rather than ""

Tom Lake

 

[Duane Hookom]

Do you have referential integrity set on? Is there a record in a related
child table?
What happens if you paste the SQL into a blank query and attempt to run it?

--
Duane Hookom
MS Access MVP

Tom:

I tried your suggestion, but it still doesn't work -- the record is not
deleted. Any other suggestions?

Thanks,
BobV

Group:

I want to write code that will delete a record from the table named
MasterData if the Name field is blank. I have been trying the following
code but it doesn't work. What am I missing?

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] = """

Try this:

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] Is Null"

Strings that haven't had a value assigned yet are Null rather than ""

Tom Lake

 

[Alex Dybenko]

Also try to run:
db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] =
""",dbFailOnError

in this case you will get a runtime error if data can not be deleted for
some reason

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Tom:

I tried your suggestion, but it still doesn't work -- the record is not
deleted. Any other suggestions?

Thanks,
BobV

Group:

I want to write code that will delete a record from the table named
MasterData if the Name field is blank. I have been trying the following
code but it doesn't work. What am I missing?

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] = """

Try this:

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] Is Null"

Strings that haven't had a value assigned yet are Null rather than ""

Tom Lake

 

[BobV]

Alex:

Thanks, but that didn't work either. If I change the DELETE statement to the
following, the code works:

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] = 'ABC
Company'"

But where the Name field is null, the code that has been suggested does not
work.

Here is my subroutine:

'CANCEL BUTTON
Private Sub CancelButton_Click()
Dim db As DAO.Database
On Error Resume Next
If OriginalCompanyName = "" Then
Set db = CurrentDb
db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] Is
Null"
End If
End Sub

Any further suggestions?

Thanks,
BobV

Also try to run:
db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] =
""",dbFailOnError

in this case you will get a runtime error if data can not be deleted for
some reason

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Tom:

I tried your suggestion, but it still doesn't work -- the record is not
deleted. Any other suggestions?

Thanks,
BobV

Group:

I want to write code that will delete a record from the table named
MasterData if the Name field is blank. I have been trying the following
code but it doesn't work. What am I missing?

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] = """

Try this:

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] Is Null"

Strings that haven't had a value assigned yet are Null rather than ""

Tom Lake

 

[Duane Hookom]

What happens when you paste the sql into a new query and attempt to run it?
Also, do you expect the value of OrginalCompanyName to be a zero length
string or could it be Null (not the same thing). Have you tried to step
through the code? How about adding a Msgbox after the If OriginalCompanyName
= ""...?

--
Duane Hookom
MS Access MVP

Alex:

Thanks, but that didn't work either. If I change the DELETE statement to
the following, the code works:

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] = 'ABC
Company'"

But where the Name field is null, the code that has been suggested does
not work.

Here is my subroutine:

'CANCEL BUTTON
Private Sub CancelButton_Click()
Dim db As DAO.Database
On Error Resume Next
If OriginalCompanyName = "" Then
Set db = CurrentDb
db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] Is
Null"
End If
End Sub

Any further suggestions?

Thanks,
BobV

Also try to run:
db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] =
""",dbFailOnError

in this case you will get a runtime error if data can not be deleted for
some reason

--
Alex Dybenko (MVP)
http://alexdyb.blogspot.com
http://www.PointLtd.com

Tom:

I tried your suggestion, but it still doesn't work -- the record is not
deleted. Any other suggestions?

Thanks,
BobV



Group:

I want to write code that will delete a record from the table named
MasterData if the Name field is blank. I have been trying the
following code but it doesn't work. What am I missing?

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] = """

Try this:

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] Is Null"

Strings that haven't had a value assigned yet are Null rather than ""

Tom Lake

 

[Guest]

Alex:

Thanks, but that didn't work either. If I change the DELETE statement to the
following, the code works:

db.Execute "DELETE FROM [MasterData] WHERE [MasterData].[Name] = 'ABC
Company'"

The DELETE statement requires you delete *SOMETHING* - a fieldname or (more
commonly) the * pseudofield. Try

db.Execute "DELETE * FROM [MasterData] WHERE [MasterData].[Name] =
'ABC Company'"

or (more specifically for your example)

db.Execute "DELETE * FROM [MasterData] WHERE [MasterData].[Name] IS NULL"

John W. Vinson/MVP