Results 1 to 10 of 10
  1. #1
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24

    Unhappy Unanswered: Two Dynamic strings in Select Query

    Okay....I can't get this dynamic "fields" portion of the query to work. Can someone please let me know what I'm getting "To Few Parameters, Expected 5"

    PHP Code:
    ... 
    " SELECT " strFieldNames ", '" strMoreFields "'" 
                
    " FROM " strShtNm "" 
                
    " WHERE (((ActivityID) IN (SELECT dbo_tActivity.ActivityID FROM dbo_tActivity WHERE UIC = '" strCriteria "')))"dbOpenDynasetdbSeeChanges
    If you're wondering why there are two strings, it's because the field names were so long that they exceeded the 255 character limit, hence two fields are being used to represent the fields. I used a MsgBox to view strFieldNames & strMoreFields and all of the comma and spaces look fine.

    I will mention that only the field names are present, as you may have noticed that the table is dynamic as well.

  2. #2
    Join Date
    Mar 2003
    Location
    The Bottom of The Barrel
    Posts
    6,102
    Provided Answers: 1
    There's an extra closing paranthesis after your select statement.

    What are you trying to do here?

    Have you tried outputting your recordsource to a msgbox or debug textbox and running it to ensure the query itself is valid?
    oh yeah... documentation... I have heard of that.

    *** What Do You Want In The MS Access Forum? ***

  3. #3
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24
    Teddy,
    The final close parens were actually openned at the beginning of the Set rst = dbITRepositoryTest.OpenRecordset( _
    I would like to know how I can test that the recordset itself is valid. It was fine with the Select statement was Select *, it's just now that the fields hae to be dynamically passed that I'm having issues.
    I also tried MsgBox strFieldNames strMoreFields and all looked great. I made sure that I Left() the additional comma at the end and everything, but when I have to pass both into the Select statement, it doesn't seem to like them. I just get Too Few Parameters.
    Any help or suggestions would be great...I will continue to trouble shoot.

    Thanks!
    Last edited by Adrianna; 09-29-04 at 08:21.

  4. #4
    Join Date
    Sep 2004
    Posts
    161
    I think you error is here :
    " SELECT " & strFieldNames & ", '" & strMoreFields & "'" & _
    a solution to test your query is a copy/paste your select statement in a new query on your access database

  5. #5
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24
    Quote Originally Posted by jepi
    I think you error is here :
    " SELECT " & strFieldNames & ", '" & strMoreFields & "'" & _
    a solution to test your query is a copy/paste your select statement in a new query on your access database
    How would I do that when strFieldNames and strMoreFields are bother String variables set in my VBA code. The GUI Queries in Access will not know where to pull the data from.

  6. #6
    Join Date
    Sep 2004
    Posts
    161
    the ' caracter before " & strMoreFields & " and the ' caracter after, affect string in your querie like this :
    if strfieldnames contains f0 and strmorefield contains f1,f2,3 the result of your querie is
    select f0,'f1,f2,f3 ' from table

    You have displayed your query string in a msgbox you can paste in GUI queries using SQL option

  7. #7
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24
    I don't know if the '" & str & "' was affecting the query as it was not passing anything back to it. I was getting To Few Parameters until I added that.

    The problem is fixed now and that code is working like a charm (dynamic where, dymanic tables, dynamic fields, all exporting each to individual worksheets in one of three determined workbooks).

    Now all I want to do is to automate the rollup data by pushing all of the queries out in the same manner. It's actually much cleaner code because the queres already exist, all I have to do is make the rs = query. This issue is that the queries have a where clause that is based off the form used to launch the export. OPenning the queries in not an issue, but when I go to Set rs = dbITRepositoryTest.OpenRecordset( _
    " SELECT * " & _
    " FROM " & strQueryName& " ", dbOpenDynaset, dbSeeChanges)
    I get the To Few Parameters error. I can only assume that the error is enerated from within the query because I have removed the string and tested the code with a queryname and the same error occurs.

  8. #8
    Join Date
    Sep 2004
    Posts
    161
    How have you resolve your first error ?
    For the last i think is
    " SELECT * " & _
    " FROM " & strQueryName& " "
    You can write like :
    " SELECT * " & _
    " FROM " & strQueryName & " "
    or " SELECT * " & _
    " FROM " & strQueryName

  9. #9
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24

    Thumbs down

    THanks jepi, but I've tried all combinations. It seems that the issue resides not with the SQL statement in VBA, but the fact that the Access Query that I'm calling upon has a WHERE clause which includes a Forms!MyForm!MyUnboundField and this is causing the To Few Parameters. I don't know how to get around it. The form with the required field is open and the data has been checked for null. I imagine that since it's only being passed to the recordset without openning, that it's not resolving the WHERE clause.

    but the first problem was solved this way
    PHP Code:
                    Do While Not rst.EOF
                        
    If intFldCnt 10 Then
                        strFieldNames 
    rst.Fields!FieldName.Value ", " strFieldNames
                        intFldCnt 
    intFldCnt 1
                        
    Else
                        
    strMoreFields rst.Fields!FieldName.Value ", " strMoreFields
                        intFldCnt 
    intFldCnt 1
                        End 
    If
                    
    rst.MoveNext
                    Loop
                
    If intFldCnt 10 Then
                strFieldNames 
    Left(strFieldNamesLen(strFieldNames) - 2)
                Else
                
    strMoreFields Left(strMoreFieldsLen(strMoreFields) - 2)
                
    End If
                
    'Create the Recordset of data to be passed into exel for the selected UIC
                Set rs = dbITRepositoryTest.OpenRecordset( _
                " SELECT " & strFieldNames & " " & strMoreFields & " " & _ 
    Last edited by Adrianna; 09-30-04 at 08:19.

  10. #10
    Join Date
    Aug 2004
    Location
    Hampton, VA
    Posts
    24

    Talking

    Okay, in the event that someone reads this with a similar issue....I'd like to mention how the parameter query was resolved without openning it. The idea (which I should have tnought of myself) is to declair the parameters prior to setting the recordSet. So:
    qdfMyQuery("[Forms]![DataRollUp]![UIC2FindActivityIDs]") = [Forms]![DataRollUp]![UIC2FindActivityIDs]

    WooHoo....possibly everyone else knows that, but it was new to me!

Posting Permissions

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