Results 1 to 14 of 14
  1. #1
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Unanswered: Too Few Parameters, expected 1 Access 2003

    I've tried researching this here and on Google, however most of the post are far more complex and over my head.

    I did read one from a Google search that said the most occurrence with this error is incorrect field names.

    I've tried really hard but I can't seem to discover whats wrong.

    Field Names are correct to the letter.

    My code goes straight across the page.

    The yellow highlighted error is on the first occurrence of CurrentDb.Execute strSQL

    Code:
    Private Sub Command140_Click()
    MsgBox "Normailze San Diego Office Names"
    Dim strSQL As String
    strSQL = "UPDATE [tblSandicorData] INNER JOIN [SandicorRoster] ON [tblSandicorData].[ListOfficeID] = [SandicorRoster].[Office Identifire] Set [tblSandicorData].[ListOfficeName] = [SandicorRoster].[Office Name]"
    CurrentDb.Execute strSQL
    strSQL = "UPDATE [tblSandicorData] INNER JOIN [SandicorRoster] ON [tblSandicorData].[SellOfficeID] = [SandicorRoster].[Office Identifier] Set [tblSandicorData].[SellOfficeName] = [SandicorRoster].[Office Name]"
    CurrentDb.Execute strSQL
    MsgBox "FINISHED SAN DIEGO OFFICE NORMALIZATION"
    End Sub
    Thanks much . . . Rick

  2. #2
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Code:
    UPDATE [tblSandicorData] INNER JOIN [SandicorRoster] ON [tblSandicorData].[ListOfficeID] = [SandicorRoster].[Office Identifire] Set [tblSandicorData].[ListOfficeName] = [SandicorRoster].[Office Name]
    It should probably be:
    Code:
    UPDATE [tblSandicorData] INNER JOIN [SandicorRoster] ON [tblSandicorData].[ListOfficeID] = [SandicorRoster].[Office Identifier] Set [tblSandicorData].[ListOfficeName] = [SandicorRoster].[Office Name]
    Have a nice day!

  3. #3
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    And the difference is . . ?

    I can see a single difference from my code to your suggested code.


    What am I missing?

    Rick

  4. #4
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    It's [Office Identifire] instead of [Office Identifier]
    Have a nice day!

  5. #5
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Yes the spelling was incorrect.

    However the code is still not fully working.

    I just don't' get it!

    it's working fine in my other database which is where i copied the code over to this database.

  6. #6
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So youve demonstrated that you didnt check the typing of one column. Id suggest you go through each and every table and column name and make certain they are right.

    then check the data... if you have an unescaped quote or speech mark in you data this can cause problems

    id also expect the set clause to be immediately after the update table clause.
    I'd rather be riding on the Tiger 800 or the Norton

  7. #7
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Carefully check the names in the tables towards the names in the queries. What you can also do is copy the contents of strSQL:
    Code:
    Debug.Print strSQL
    then copy/paste the SQL expression from the Debug window to a new query in SQL view and see what happens when you switch it to Datasheet view. Usually, the SQL interpretor highlights the offending part of the expression and the error message is often more explicit.
    Have a nice day!

  8. #8
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    Im expecting something like

    update mytable set acolumn = anothertable.bcolumn join anothertable on mytable.thatcolumn = anothertable.thiscolumn
    I'd rather be riding on the Tiger 800 or the Norton

  9. #9
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Comment out the last two lines of code

    If I comment out the last two lines of code the first update portion works great!

    The highlighted error is the second CurrentDb.Execute strSQL

    Maybe this will help you point me in the right direction ? ? ?

    Code:
    Private Sub Command140_Click()
    MsgBox "Normailze San Diego Office Names"
    Dim strSQL As String
    strSQL = "UPDATE [tblSandicorData] INNER JOIN [SandicorRoster] ON [tblSandicorData].[ListOfficeID] = [SandicorRoster].[Office Identifire] Set [tblSandicorData].[ListOfficeName] = [SandicorRoster].[Office Name]"
    CurrentDb.Execute strSQL
    'strSQL = "UPDATE [tblSandicorData] INNER JOIN [SandicorRoster] ON [tblSandicorData].[SellOfficeID] = [SandicorRoster].[Office Identifire] Set [tblSandicorData].[SaleOfficeName] = [SandicorRoster].[Office Name]"
    'CurrentDb.Execute strSQL

    Thanks much . . . Rick

  10. #10
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    So what is different between the two statements?

    are you certain that there is a columns called salesofficename and salesofficeid in table tblsandicordata
    I'd rather be riding on the Tiger 800 or the Norton

  11. #11
    Join Date
    Mar 2004
    Location
    California
    Posts
    502

    Aaaarrrrrggggg . . . . .

    I stare at this so long I can't see the minor error in front of me causing the problem.

    Healdem, (and others) were correct.

    I had [SaleOfficeName] is lieu of SellOfficeName.

    Yikes . . . two days for that.

    Thanks everyone, really.

    Rick

  12. #12
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    Did you try to copy/paste the SQL expression in a new query? If yes, what where the results? From the beginning I have the feeling that something is misspelled (and Healdem seems to share that feeling).
    Have a nice day!

  13. #13
    Join Date
    Mar 2009
    Posts
    5,442
    Provided Answers: 14
    You're welcome!
    Have a nice day!

  14. #14
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    The lesson from this is be more diligent when checking stuff. Dont just blindly assume you havent made a mistake. Be consistent in naming your columns. Ut doesnt matter if you prefer to sell or sale... but be consistent.

    Im not even going to go near why you have an id column for an office and then stire the name in ibdivudual rows. It smacks of flaky design, of a non normalised design
    I'd rather be riding on the Tiger 800 or the Norton

Posting Permissions

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