Deleteing records with blank field

[Wylie C]

I have a close form command button that when I click it I want it to delete
all records in the Fishdata table where the TagNum is blank. The Delete *
From Fishdata Where TagNum = '""'; syntax does not work.
Thank you

 

[Steve Schapel]

Wylie,

Try it like this...
DELETE * FROM Fishdata WHERE TagNum Is Null

 

[Wylie C]

I tried this and it did not work. When I comple it it shows it doesn't like
the * character. I also tried the syntax with an ; at the end and it didn't
work either. Any other ideas?

 

[Steve Schapel]

Wylie,

When you compile it? Where and how are you using this?

The syntax with the * is correct for a Delete Query. It is not
relevant whether there is a ; at the end or not.

 

[Wylie C]

Steve,
I copied your code into my code and got an error message box that read:
Compile Error..Syntax Error and the line is highlighted.
Here is the entire code for the command button:

Private Sub cmdClose_Click()
On Error GoTo Err_Command8_Click

Delete * FROM Fishdata WHERE TagNum is null
DoCmd.Close

Exit_Command8_Click:
Exit Sub

Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click

End Sub
Thanks again

 

[Ken Snell [MVP]]

PMFJI....

You must use the CurrentDb object to run the query, which should be a text
string.

Change this line:
Delete * FROM Fishdata WHERE TagNum is null

to these lines:
Dim strSQL As String
strSQL = "Delete * FROM Fishdata WHERE TagNum is null"
CurrentDb.Execute strSQL, dbFailOnError

 

[Steve Schapel]

Wylie,

No, you can't do it like that. What we have been discussing is a SQL
string. If you are trying to do something with this within an event
procedure, as you are, you have to tell VBA what to do with it. Here's
an example...

Private Sub cmdClose_Click()
On Error GoTo Err_Command8_Click

Dim strSQL As String
strSQL = "DELETE * FROM Fishdata WHERE TagNum Is Null"
CurrentDb.Execute strSQL, dbFailOnError
DoCmd.Close acForm, Me.Name

Exit_Command8_Click:
Exit Sub

Err_Command8_Click:
MsgBox Err.Description
Resume Exit_Command8_Click

End Sub

 

[Wylie C]

Thank you. Now that I see it I understand it and will now be able to apply
the logic to other areas of the project. Thanks again.

 

[Steve Schapel]

PMFJI....

Well, since we apparently agree, what's to pardon?

 

[Ken Snell [MVP]]

Well, since we apparently agree, what's to pardon?

my eagerness...... < g >

 

[Steve Schapel]

my eagerness...... < g >

Vavoom, I'd call it ;-)

 

[Ken Snell [MVP]]

< g >!

--

Ken Snell
<MS ACCESS MVP>

Vavoom, I'd call it ;-)