Dynamic SQL Problem 3

[AlexCuse]

Hi All,

I need to export a query (with parameters) to create a varying number of different tables in an external database. I think the best way to do this is to dynamically build and execute a SQL Statement within a loop.

I am having a curious problem, in that it lets me assign the query I need to my string the first time, but the second time I get

Run Time Error '5':
Invalid Procedure Call or Argument

I am stumped as to why I am not able to reassign this variable.

Here is the basic structure of my code (I will spare you the SQL statement it is a few lines)

Dim x As Integer

Dim strSQL As String

For x = LBound(MMYY) To UBound(MMYY)

    strSQL = "select some, columns " _
             & "into '" & dbName & "' " _
             & "from someTable inner join another " _
             & "on someTable.PK = another.PK " _
             & "where someTable.BirthMonth = " & left(MMYY(x), 2) _
             & " someTable.BirthYear = " & right(MMYY(x), 4)

    DoCmd.RunSQL strSQL

Next

Any thoughts much appreciated.

Thanks,

Alex

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[VRoscioli]

Well according to your code above, you're missing your " AND " between your WHERE criteria. If that's not actually a problem, you should probably post your actual code.


-V

 

[AlexCuse]

Like I said, the query runs fine the first time. Here's the code, if it helps (might need to paste it into notepad or something, I don't have time to try and tek-tips format it):

Public Function exportSC65(ByRef MMYY() As String, ByVal dbName As String) As Boolean

On Error GoTo errH

Dim x As Integer

Dim strSQL As String

For x = LBound(MMYY) To UBound(MMYY)



MsgBox MMYY(x)

strSQL = "SELECT TP85FINL.ControlNumber, TP85FINL.FullName, TP85FINL.ListCode, " _
    & "TP85FINL.ListName, TP85FINL.Prefix, TP85FINL.Fname, TP85FINL.MI, TP85FINL.LName, " _
    & "TP85FINL.Suffix, TP85FINL.Add1, TP85FINL.Add2, TP85FINL.City, TP85FINL.State, " _
    & "TP85FINL.Zip, TP85FINL.BirthMonth, TP85FINL.BirthYear, TP85FINL.TurningYear, " _
    & "TP85FINL.PhoneNumber, TP85FINL.County, " _
    & "'' AS KEYCODE, '' AS AREA, '' AS AREA_ID, '' AS KITCODE, '' AS C2, '' AS C3, '' AS MatchCode, '' AS SC65_BLUERX" _
    & " INTO " _
    & MonthName(Val(Left(MMYY(x), 2)), True) & "_" & Right(MMYY(x), 4) & " IN '" & dbName & "' " _
    & "FROM TP85FINL INNER JOIN qSC65_BRX_CN " _
    & "ON TP85FINL.ControlNumber = qSC65_BRX_CN.CN " _
    & "WHERE TP85FINL.BirthMonth='" & Left(MMYY(x), 2) & "'" _
    & " AND TP85FINL.BirthYear='" & Right(MMYY(x), 4) & "'"

DoCmd.RunSQL strSQL

Next

exportSC65 = True

errH:

exportSC65 = False

End Function

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[PHV]

And which line of code is highlighted when in debug mode at the time the eoor raises ?

Hope This Helps, PH.
FAQ219-2884
FAQ181-2886

 

[AlexCuse]

The final line in the assignment of strSQL

    & " AND TP85FINL.BirthYear='" & Right(MMYY(x), 4) & "'"

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[jsteph]

Does the error occur at the assignment of the text to the strSQL variable, or at the runsql method?

Also, is the array value guaranteed to always be at least 4 characters?

The right() will give this error if too short a string is passed (less than 4 in your example)
--Jim

 

[AlexCuse]

It is at the assignment, and each string is 6 characters.

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[jsteph]

Alex,
Try using right$(), the right() may be interpreting an integer wich has a length of 2...but this doesn't explain the reason why the first loop works. I'd try it anyway.
--Jim

 

[AlexCuse]

Thanks Jim, but I get the same error. FWIW, here are the values I am passing in the array:

081942, 091942, 101942

I'm going to try passing them as dates and see if that makes any difference

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[AlexCuse]

Ok, the problem was in the comma-separated string that I was taking from the user. If I remove the spaces or split on ", ", then it works fine. I thought the split function would handle whitespace but I guess not.

This piece

MonthName(Val(Left(MMYY(x), 2)), True)

Must have been failing when it has the value " 0"

Thanks for your help guys! Stars all around

Alex

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[VRoscioli]

Not that I think it has much to do with this problem, but you probably want to put Exit Sub before errH: so that your function doesn't go into your error handler every time.


-V

 

[AlexCuse]

Yeah that was another typo. I haven't been doing much VBA work for the past couple months, and the lack of practice is hurting me big time

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[jsteph]

Are birthmonth and year set up as strings in the table? I notice the quotes around the sql.

Also, I'd try using debug window to get the actual value of the variable in the array, and then try the right() in the debug window to see if that's the actual point in the line that causes the problem.

Usually, with line-continuation characters, the entire block is yellowed, but the point where the cursor is set and highlighted when it goes into break is not always the exact point of error.
--Jim

 

[jsteph]

...whoops, the last posts weren't there when I replied...

 

[AlexCuse]

No worries jsteph. I know all that stuff. The birthmonth is set up as a string (because it needs to retain leading zero), I believe birthyear is a string as well, but when I set up a real process for this I am sure I will make that an int. (this is a rush job now for an existing database that someone wrote long before my time here)

I really appreciate all your help!

Alex

I don't do any programming whatsoever

Ignorance of certain subjects is a great part of wisdom

 

[mikej336]

I find it very helpful to print your strSQL to the debug window. It is often easier to catch little errors...

Uncle Mike