Question about Deleting records using VBA

I am writing some code to delete records from a table, but I am pretty sure I am going about it wrong.

The thing that is hanging the code up (I think) is that sometimes there will be a null value in one of the fields. when I run this code and there is no null value, it works fine. When there is a null value, it tells me I have a syntax error.

I am also thinking that I am using Len wrong. It is for string length, and I am using it here to check to see if there is a value in the PersonID field. Is that appropriate?

Dim frm As Form
Dim ctl As Control
Dim db As DAO.Database
Dim strSQL As String
Dim i As Variant

Set frm = Forms("f_SubTaskAssignments")
Set ctl = frm![lstExistingAssignments]
Set db = CurrentDb

For Each i In ctl.ItemsSelected

'***For future reference - Add chr(34) around text!

If Len(" & ctl.Column(7, i) & ") > 0 Then
strSQL = "DELETE * FROM SubTaskAssignment WHERE " & _
        "[TaskOrderID] = " & ctl.Column(0, i) & " AND [SubTaskID] = " & ctl.Column(5, i) & " AND [CompanyID] = " & ctl.Column(6, i) & " And [PersonID] = " & ctl.Column(7, i) & " And [ReportAs] = " & Chr(34) & ctl.Column(4, i) & Chr(34) & ""
End If


If Len(" & ctl.Column(7, i) & ") = Null Then
strSQL = "DELETE * FROM SubTaskAssignment WHERE " & _
        "[TaskOrderID] = " & ctl.Column(0, i) & " AND [SubTaskID] = " & ctl.Column(5, i) & " AND [CompanyID] = " & ctl.Column(6, i) & "  And [ReportAs] = " & Chr(34) & ctl.Column(4, i) & Chr(34) & ""
        
End If
db.Execute strSQL, dbFailOnError



Next i

ctl.Requery
End Sub

Open in new window


I know this is lots of wrong and I look forward to getting some advice on how to make it right.

Thank you!
MeginAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Jeffrey CoachmanMIS LiasonCommented:
Not sure I understand your code...
This line:
    For Each i In ctl.ItemsSelected
...will commonly refer to controls selected while the form is in design View...
(so you might want to comment out that loop...)

In any event, ...
I have sometimes had bad luck when using LEN() to check for nulls
Try IsNull instead, something roughly like this:

    If IsNull(XXX) then...
        Your Delete Code
    End If

Lets see if another expert spots something more specific...


JeffCoachman
0
BeartlaoiCommented:
Checking for NULL in SQL requires different syntax.  Rather than "= NULL" you must use "IS NULL", this is a pain with ad-hoc queries but there is a way.

"[TaskOrderID]" & IIF(ISNULL(ctl.Column(0, i)), " = " & ctl.Column(0, i) , " IS NULL" ) & 

Open in new window

although to make your code more readable  use this

Function MyEq(Expr As String) As String
     MyEq = IIf(IsNull(Expr), " IS NULL", " = """ & Expr & """")
End Function

"[TaskOrderID]" & MyEq(ctl.Column(0, i)) & 

Open in new window

0
MeginAuthor Commented:
Beartlaoi: I am a bit confused on how to use the IIF as opposed to the If statement. I didn't get very far with that. But I am sure that I my ignorance.

I also tried using IsNull and it didn't help.

I am getting a syntax error. It states that there is a missing operator in my query expression.

I went and made PersonID = null in the second sql statement because I thought that it might be looking for that, but nothing changed when I added it. I also tried PersonID IsNull.

Can you guys take another look or explain to me again?

If Len(" & ctl.Column(7, i) & ") > 0 Then
strSQL = "DELETE * FROM SubTaskAssignment WHERE " & _
        "[TaskOrderID] = " & ctl.Column(0, i) & " AND [SubTaskID] = " & ctl.Column(5, i) & " AND [CompanyID] = " & ctl.Column(6, i) & " And [PersonID] = " & ctl.Column(7, i) & " And [ReportAs] = " & Chr(34) & ctl.Column(4, i) & Chr(34) & ""
End If



If IsNull(" & ctl.Column(7, i) & ") Then
strSQL = "DELETE * FROM SubTaskAssignment WHERE " & _
        "[TaskOrderID] = " & ctl.Column(0, i) & " AND [SubTaskID] = " & ctl.Column(5, i) & " AND [CompanyID] = " & ctl.Column(6, i) & " AND [PersonID]= Null And [ReportAs] = " & Chr(34) & ctl.Column(4, i) & Chr(34) & ""
        
End If
db.Execute strSQL, dbFailOnError

Open in new window

0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

MeginAuthor Commented:
This still isn't working. I don't think my first If statement is right. I have tried it a few different ways.

If ctl.Column(7, i) = Null Then
Debug.Print " & ctl.Column(7, i) & "

strSQL = "DELETE * FROM SubTaskAssignment WHERE " & _
        "[TaskOrderID] = " & ctl.Column(0, i) & " AND [SubTaskID] = " & ctl.Column(5, i) & " AND [CompanyID] = " & ctl.Column(6, i) & " AND [PersonID]= Null And [ReportAs] = " & Chr(34) & ctl.Column(4, i) & Chr(34) & ""
Else
strSQL = "DELETE * FROM SubTaskAssignment WHERE " & _
        "[TaskOrderID] = " & ctl.Column(0, i) & " AND [SubTaskID] = " & ctl.Column(5, i) & " AND [CompanyID] = " & ctl.Column(6, i) & " And [PersonID] = " & ctl.Column(7, i) & " And [ReportAs] = " & Chr(34) & ctl.Column(4, i) & Chr(34) & ""


        
End If
db.Execute strSQL, dbFailOnError



Next i

ctl.Requery
End Sub

Open in new window


The debug.print isn't working. I am not sure what I am doing wrong here.
0
BeartlaoiCommented:
If IsNull(ctl.Column(7, i)) Then
Debug.Print " & ctl.Column(7, i) & "

strSQL = "DELETE * FROM SubTaskAssignment WHERE " & _
        "[TaskOrderID] = " & ctl.Column(0, i) & " AND [SubTaskID] = " & ctl.Column(5, i) & " AND [CompanyID] = " & ctl.Column(6, i) & " AND [PersonID] IS Null And [ReportAs] = " & Chr(34) & ctl.Column(4, i) & Chr(34) & ""
Else
strSQL = "DELETE * FROM SubTaskAssignment WHERE " & _
        "[TaskOrderID] = " & ctl.Column(0, i) & " AND [SubTaskID] = " & ctl.Column(5, i) & " AND [CompanyID] = " & ctl.Column(6, i) & " And [PersonID] = " & ctl.Column(7, i) & " And [ReportAs] = " & Chr(34) & ctl.Column(4, i) & Chr(34) & ""

End If

Another problem you might be having is that onscreen controls dont often have a real NULL in them, maybe an empty string. If that is the case then you use "" in the if statement, the IS NULL in the query string.

If ctl.Column(7, i) = "" Then
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
BeartlaoiCommented:
The problem with your original line 15
If Len(" & ctl.Column(7, i) & ") > 0 Then

Open in new window

is that it is a constant, that is it is getting the length of & ctl.Column(7, i) &   Everything between the quotes is taken exactly, not evaluated, so it is always 22 characters.

and the other If on line 21, Len will never return null, it should look like
If IsNull(ctl.Column(7, i)) Then

Open in new window


Do you expect that other columns on the form will be null?
What is the output of
Debug.Print IIf(IsNull(ctl.Column(7, i)), "(null)", """" & ctl.Column(7, i) & """")

Open in new window


* Explanation of IIf, it takes three inputs, a boolean and two variants
IIf( TrueOrFalse, TruePart, FalsePart)
If the first parameter is true then it returns the true part, if the first parameter is false then it returns the false part
It is literally this function
Function IIf(bWhich as boolean, vTruePart as variant, vFalsePart as variant) as Variant
  if bWhich = True Then
    IIf = vTruePart
  else
    IIf = vFalsePart
End If

Open in new window

0
MeginAuthor Commented:
Setting it equal to "" rather than Null worked!!!!!!!

THANK YOU!!!!!!
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.