We help IT Professionals succeed at work.

Re-Modify Function

thenone asked
Last Modified: 2008-02-01
Problem with this function Im finding out is if the keywords don't have a , in it it will delte it like it should but some records only have one keyword so it will still delete it.What I want to do is find out if it is one keyword or phrase and keep that record but still delete the records that don't have the keywords comma minized.

Dim DeleteRecord As Boolean, fLoop As Integer
Dim conn As ADODB.Connection
Set conn = New ADODB.Connection
conn.CursorLocation = adUseClient
    conn.ConnectionString = "DRIVER={MySQL ODBC 3.51 Driver};" _
            & "SERVER=;" _
            & "DATABASE=sites;" _
            & "UID=howard;" _
            & "PWD=password;" _
            & "OPTION=" & 1 + 2 + 8 + 32 + 2048 + 16384

    Dim rs As ADODB.Recordset
    Set rs = New ADODB.Recordset
    rs.Open "Select * From Business_Split", conn, adOpenKeyset, adLockOptimistic
    With rs
        Do Until .EOF
        DeleteRecord = False
            For fLoop = .Fields(0) To .Fields.Count - 1 'loop among all fields and set DeleteRecord to true if one of them is empty
                If Trim(.Fields(fLoop).Value) = "" Or IsNull(.Fields(fLoop).Value) Then
                    DeleteRecord = True
                    Exit For
                End If
                If InStr(.Fields("keywords"), ",") = 0 Then 'do you mean of there is a double quote?
                    DeleteRecord = True
                End If
                If DeleteRecord Then
                .Delete adAffectCurrent
                End If
    End With

Label2.Caption = "Cleaning Up Records is done"

Watch Question

Sorry, but can you explain a little more

Please put some static examples for the both situations (the right and the fault) and I'll be glad to help out


Sure no problem what I want to do is modify this a little bit.First of all the fields title,description,body,keywords can sometimes be blank.So what I want to do is delete all records where the body and the title are blank.Second thing is if the keywords and the description are blank then grab the first four words from the title and put that into the keywords part of the record to fill that part of the data.I hope that makes since.

Example data...

initial part
title                                Description                Keywords                           Body
                                                                                                       bla bla blah
The storm is rising            

Title                              Description               keywords                              body
The storm is rising                                       The storm is rising            blah blah blah

So the end result will look like this.
This one is on us!
(Get your first solution completely free - no credit card required)


so with this modifivcation if the body is missing or the title it will delete the record and if the description and keywords are missing it will take the first four words from the title and put it into the keywords part is that correct?


Title                     Description              Keywords               Body

the dogs play                                     The dogs play        blah bl;ah blah       Action:Keep
                            where are we         the part               bla blah                  Action:Delete
the dogs play          where are we       the part                blah blah               Action:keep

Is this what it will do?

Gain unlimited access to on-demand training courses with an Experts Exchange subscription.

Get Access
Why Experts Exchange?

Experts Exchange always has the answer, or at the least points me in the correct direction! It is like having another employee that is extremely experienced.

Jim Murphy
Programmer at Smart IT Solutions

When asked, what has been your best career decision?

Deciding to stick with EE.

Mohamed Asif
Technical Department Head

Being involved with EE helped me to grow personally and professionally.

Carl Webster
CTP, Sr Infrastructure Consultant
Empower Your Career
Did You Know?

We've partnered with two important charities to provide clean water and computer science education to those who need it most. READ MORE

Ask ANY Question

Connect with Certified Experts to gain insight and support on specific technology challenges including:

  • Troubleshooting
  • Research
  • Professional Opinions
Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.


Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.