Results 1 to 6 of 6
  1. #1
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Insert Into Error- 'too few parameters' (97 SR2)

    I am running the following code to mine some data. The query worked ok until I added the last field in the insert (DNROWNERSTRING) and the expression into the select (strbasename & " as newname "). The code works fine with the DNRReFNo/(strrefno & " as test, ") combination

    The error msg I get is: Too few parameters. Expected 1

    Code:
    Do
    strbasename = rstbasename![ownerstring]
    strrefno = rstbasename![refno]
    strtransfer = "INSERT INTO datascreen ( 3HSystemName, 3HPWSID, DNRReFNo, DNROWNERSTRING ) " & _
    "SELECT [3H ALL].SYSTEM_NAM, [3H ALL].PWSID, " & strrefno & " as test, " & strbasename & " as newname " & _
    "FROM [3H ALL] WHERE ([3H ALL].SYSTEM_NAM Like ""*" & strbasename & "*"");"
    MsgBox strtransfer
    dbs.Execute strtransfer
    rstbasename.MoveNext
    intcount = intcount + 1
    Loop Until intcount > intnorecords

    Cut & Paste from Debug Window:

    "INSERT INTO datascreen ( 3HSystemName, 3HPWSID, DNRReFNo, DNROWNERSTRING ) SELECT [3H ALL].SYSTEM_NAM, [3H ALL].PWSID, 16 as test, MAX as newname FROM [3H ALL] WHERE ([3H ALL].SYSTEM_NAM Like "*MAX*");"

  2. #2
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Into Error- 'too few parameters' (97 SR2)

    Not sure if this is the answer, but do you need to wrap in quotes:-

    Like ""*" & Chr(34) & strbasename & Chr(34) & "*"");"

  3. #3
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Insert Into Error- 'too few parameters' (97 SR2)

    The Like part of the SQL is ok, unless Access is treating the second expression as a parameter instead of a passed value

  4. #4
    Silver Lounger
    Join Date
    Jun 2002
    Location
    Cheadle, Staffordshire
    Posts
    2,177
    Thanks
    0
    Thanked 0 Times in 0 Posts

    Re: Insert Into Error- 'too few parameters' (97 SR2)

    I'm sorry, I thought the confusion was with the string element of the query.
    Perhaps another lounger can help here.

  5. #5
    Plutonium Lounger
    Join Date
    Dec 2000
    Location
    Sacramento, California, USA
    Posts
    16,775
    Thanks
    0
    Thanked 1 Time in 1 Post

    Re: Insert Into Error- 'too few parameters' (97 SR2)

    Is there a comma in the strbasename value? That can confuse things because it might be interpreted as multiple fields.
    Charlotte

  6. #6
    Star Lounger
    Join Date
    Sep 2002
    Location
    Indianapolis, Indiana, USA
    Posts
    80
    Thanks
    1
    Thanked 0 Times in 0 Posts

    Re: Insert Into Error- 'too few parameters' (97 SR2)

    The chr(34) in the select part of the SQL seems to work. I did add quotes bracketing the select before I posted (strrefno & " as test, """ & strbasename & """ as newname ") and it did not work originally. However I reran this version since using the chr(34) variant and now I am not getting an error. Somewhere I must have corrected something in that version and didn't rerun the correct code version. For some reason the addition of quotes did not work when I originally recompiled and tried it

    Here is the de###### output for the final version that works:

    "INSERT INTO datascreen ( 3HSystemName, 3HPWSID, DNRReFNo, DNROWNERSTRING ) SELECT [3H ALL].SYSTEM_NAM, [3H ALL].PWSID, 16 as test, "MAX" as newname FROM [3H ALL] WHERE ([3H ALL].SYSTEM_NAM Like "*MAX*");"''

    both ways below give the SQL string above:

    "INSERT INTO datascreen ( 3HSystemName, 3HPWSID, DNRReFNo, DNROWNERSTRING ) " & _
    "SELECT [3H ALL].SYSTEM_NAM, [3H ALL].PWSID, " & strrefno & " as test, " & Chr(34) & strbasename & Chr(34) & " as newname " & _
    "FROM [3H ALL] WHERE ([3H ALL].SYSTEM_NAM Like ""*" & strbasename & "*"");"
    OR

    "INSERT INTO datascreen ( 3HSystemName, 3HPWSID, DNRReFNo, DNROWNERSTRING ) " & _
    "SELECT [3H ALL].SYSTEM_NAM, [3H ALL].PWSID, " & strrefno & " as test, """ & strbasename & """ as newname " & _
    "FROM [3H ALL] WHERE ([3H ALL].SYSTEM_NAM Like ""*" & strbasename & "*"");"

Posting Permissions

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