Results 1 to 7 of 7
  1. #1
    Join Date
    Mar 2004
    Posts
    68

    Unanswered: using SQL in VBA

    Hello, I have been struggling with getting some vba code to work and am ready to quit!! Any help is GREATLY appreciated. Here's the code:

    Function ParseInjuryTypes()

    Dim ParseString() As String
    Dim SourceString As String
    Dim strClaimNumber As String

    SourceString = DLookup("[InjuryType]", "ExcelLegalFactorsCapEarly")
    strClaimNumber = DLookup("[Claim Number]", "ExcelLegalFactorsCapEarly")

    ParseString = Split(SourceString, ";", , vbTextCompare)

    ' To read the Array ...
    Dim i As Integer, a$
    Dim strSQL1 As String

    For i = 0 To UBound(ParseString)
    a$ = a$ & ParseString(i) & vbNewLine

    'EVERYTHING WORKS UP TO THIS POINT!

    strSQL1 = "INSERT INTO tblInjuriesperClaimECA " & _
    "([ClaimNumber], [InjuryType1])VALUES ('" & strClaimNumber & "','" & ParseString(i));"
    CurrentDb.Execute strSQL1
    Next i

    End Function

    I am trying to use strSQL1 to insert the values from the array into a new row. I know that this statement is not correct, but don't know where to go from here.

    Thank you in advance for your help.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    strSQL1 = "INSERT INTO tblInjuriesperClaimECA " & _
    "([ClaimNumber], [InjuryType1])VALUES ('" & strClaimNumber & "','" & ParseString(i) & ");"

  3. #3
    Join Date
    Mar 2004
    Posts
    68

    SQL in VBA

    Thanks for the reply. I am still getting "Syntax error in string in query expression." when it tries to execute strSQL1 ????

  4. #4
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Sorry, missed an apostraphe:

    strSQL1 = "INSERT INTO tblInjuriesperClaimECA " & _
    "([ClaimNumber], [InjuryType1]) VALUES ('" & strClaimNumber & "','" & ParseString(i) & "');"

    Also, dump that into a message box if it still errors and make sure it looks good.

    Just a simple:

    MsgBox "INSERT INTO tblInjuriesperClaimECA " & _
    "([ClaimNumber], [InjuryType1]) VALUES ('" & strClaimNumber & "','" & ParseString(i) & "');"
    Last edited by Teddy; 04-07-04 at 11:59.

  5. #5
    Join Date
    Mar 2004
    Posts
    68

    Still having problems ...

    Thanks! That worked. However I have another problem. In the code that I originally posted, I had this SQL statement inserted in a loop and it doesn't seem to be working the way I want it to. I actually think I need two loops. One to continue to look at the SourceString and another so that it continues to insert the rest of the array values into [InjuryType2], [InjuryType3], etc.... I don't know if this makes sense, but I hope so. Thanks again.


    Function ParseInjuryTypes()

    Dim ParseString() As String
    Dim SourceString As String
    Dim strClaimNumber As String

    SourceString = DLookup("[InjuryType]", "ExcelLegalFactorsCapEarly")
    strClaimNumber = DLookup("[Claim Number]", "ExcelLegalFactorsCapEarly")
    MsgBox SourceString
    MsgBox strClaimNumber

    ParseString = Split(SourceString, ";", , vbTextCompare)

    ' To read the Array ...
    Dim i As Integer, a$
    Dim strSQL1 As String

    For i = 0 To UBound(ParseString)
    a$ = a$ & ParseString(i) & vbNewLine

    'EVERYTHING WORKS UP TO THIS POINT!

    strSQL1 = "INSERT INTO tblInjuriesperClaimECA " & _
    "([ClaimNumber], [InjuryType1]) VALUES ('" & strClaimNumber & "','" & ParseString(i) & "');"

    CurrentDb.Execute strSQL1

    Next i
    MsgBox "Your Array Contains:" & vbNewLine & vbNewLine & a$

    End Function

  6. #6
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    Could you go into further detail regarding the unexpected behavior? I don't see the need for two loops here.. after the split function, you shouldn't have to refer to the sourcestring anymore...

  7. #7
    Join Date
    Mar 2004
    Posts
    68

    SQL in VBA

    Basically, what I'm trying to do is this ....

    I have another module that I run that imports data from an Excel Spreadsheet. Each record contains a field called [ClaimNumber] which is the primary key and another cone called [Injury Types]. The field contains a string of values separated by a semicolon (i.e. 22;26;28). Each number corresponds to a type of injurty that I list in another table "tblInjuryList".

    So, what I'm trying to do in the code on this post is to parse out the Injury Types field and insert the values in a table "tblInjuriesperClaim" so that the data becomes "normalized".

    There can be anywhere from 1 to 25 Injury types per claim.

    Am I making this harder than it is? I am working on getting myself to a VBA class because I really have no support where I work!!! (Can you tell?)

    Please let me know if you need additional information in this explanation. I will check in later, as I have to run right now.

    thanks again for your help. don't know what I would do with this forum!

Posting Permissions

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