Results 1 to 2 of 2

Thread: SQL Delete Code

  1. #1
    Join Date
    Oct 2007
    Posts
    214

    Question Unanswered: SQL Delete Code

    I keep running into an type mismatch on the below code. I looked at it for a few hours and can't seem to find the issue. Any suggestions?

    The errors I get point to the lines with the stars below.


    Const c_SQL As String = "DELETE FROM UCMClients WHERE (@C);"
    Const c_Criteria As String = "(employer = '@1') and (account number = '@2') and (state = '@3') and ([parent Number] = '@4')"

    Dim strSQL As String
    Dim strCriteria As String
    Dim lngRowCount As Long

    If Len(Nz(Me.TxtEmployer.Value)) > 0 And Len(Nz(Me.txtAccountNumber.Value, "")) > 0 And Len(Nz(Me.txtState.Value, "")) > 0 And Len(Nz(Me.txtParentNumber.Value, "")) > 0 Then
    ***** strCriteria = Replace(Replace(Replace(Replace(c_Criteria, "@1", Me.TxtEmployer.Value, ""), "@2", Me.txtAccountNumber.Value, ""), "@3", Me.txtState.Value, ""), "@4", Me.txtParentNumber.Value, "")
    lngRowCount = DCount("*", "CLients", strCriteria)
    If lngRowCount > 0 Then
    **** strSQL = Replace(c_SQL, "@C", strCriteria)
    CurrentDb.Execute strSQL, dbFailOnError
    MsgBox lngRowCount & " deleted.", vbInformation
    Else
    MsgBox "No matching rows.", vbInformation
    End If
    End If

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    1. What's in strSQL?
    Code:
    strSQL = Replace(c_SQL, "@C", strCriteria)
    Debug.Print strSQL: Stop
    2. Missing brackets that are mandatory because of the space character in the name of the column:
    Code:
    ... and ([account number] = '@2') and
    3. If, as their names seems to indicate, [account number] and [parent number] are numeric, it should be (without quotes):
    Code:
    Const c_Criteria As String = "(employer = '@1') and (account number = @2) and (state = '@3') and ([parent Number] = @4)"
    Have a nice day!

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •