Results 1 to 10 of 10
  1. #1
    Join Date
    Sep 2007
    Posts
    148

    Unanswered: error -- too few parameters ????

    How do I put two fields together -- I need help on to put ChangeControlFormNum and RevisedCode together in the update statement.

    Now I am getting type mismatch.

    Thanks

    ************************************************** ****

    Private Sub cmdCCFNum_Click()

    Dim LN As Variant
    LN = InputBox("Enter Change Control Form Number:")


    'Set all record's print report field = false
    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET PrintReport = False;")

    'Find our all the Revise code{s} for this Change Control Form
    Dim dbRevise As Database
    Dim rsRevise As DAO.Recordset
    Dim strRecCount As Integer
    Set dbRevise = CurrentDb()

    'Open the tblChangeControlFormDetails table
    Set rsRevise = dbRevise.OpenRecordset("Select * FROM tblChangeControlFormDetails ")

    rsRevise.MoveFirst

    If Not rsRevise.EOF Then

    Do
    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET PrintReport = True WHERE ChangeControlFormNum & RevisedCode & = " & LN & "; ")

    rsRevise.MoveNext
    Loop While rsRevise.EOF = False

    End If

    rsRevise.Close

    End Sub


    *****************************************8

    Thank you all for your help.

    Thanks
    Last edited by sweetmail; 05-02-08 at 17:08.

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    You mean you want to match LN to the combined value of the 2 fields? That's a new one, but for starters you have an extra & in there between RevisedCode and the =.
    Paul

  3. #3
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Oh, and if the resulting value is text, surround it with single quotes.
    Paul

  4. #4
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    I've also had pretty good luck with using 3 " (double-quotes) and 'Like' versus a single-quote-double-quote or a single double-quote and the = sign (although the 3 " works with = or like).
    ie..

    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET PrintReport = True WHERE ChangeControlFormNum & RevisedCode & Like """ & LN & """")
    Last edited by pkstormy; 05-02-08 at 21:13.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  5. #5
    Join Date
    Sep 2007
    Posts
    148
    I tried

    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET PrintReport = True WHERE ChangeControlFormNum & RevisedCode & Like """ & LN & """")

    But it said
    "Syntax error (missing operator) in query expression
    'ChangeControlFormNum & RevisedCode & like "430"'.


    My input is 430.

    Any help is most welcome.

    Thanks

  6. #6
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    You've got 2 & in the expression in inappropriate places. You might want to relook at the sql expression.

    I'm not sure what the expression exactly should be but it should be something like...

    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET PrintReport = True WHERE ChangeControlFormNum Like """ & Something & """ AND RevisedCode Like """ & LN & """")

    versus

    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET PrintReport = True WHERE ChangeControlFormNum & RevisedCode & Like """ & LN & """")

    (Notice the ChangeControlFormNum & RevisedCode & Like ...) with the & Like
    Last edited by pkstormy; 05-02-08 at 22:41.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  7. #7
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Sorry sweetmail. I just read the part where you're trying to combine 2 values in 1 expression. You may want to just create a variable and combine the 2 values (ChangeControlFormNum & RevisedCode) and use that in your sql.

    Hence...
    Dim SomeVariable as variant
    SomeVariable = ChangeControlFormNum & RevisedCode

    then do the Like statement...

    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET PrintReport = True WHERE SomeVariable Like """ & LN & """")

    although it still doesn't look right for an UPDATE sql statement and I don't think it'll work for what I think you're trying to accomplish.
    Last edited by pkstormy; 05-02-08 at 22:31.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  8. #8
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Scratch the above. It won't work (I'm trying to hard thinking today).
    Last edited by pkstormy; 05-02-08 at 22:50.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  9. #9
    Join Date
    Dec 2004
    Location
    Madison, WI
    Posts
    3,926
    Try something like this....

    CurrentDb.Execute("UPDATE tblChangeControlFormDetails SET tblChangeControlFormDetails.PrintReport = True WHERE ((([ChangeControlFormNum] & [RevisedCode]) Like """ & LN & """)))
    or
    CurrentDb.Execute("UPDATE tblChangeControlFormDetails SET tblChangeControlFormDetails.PrintReport = True WHERE ((([ChangeControlFormNum] & [RevisedCode]) Like """ & LN & """))")

    Sorry about my confusion sweetmail. I didn't read your first post carefully.
    Last edited by pkstormy; 05-02-08 at 22:44.
    Expert Database Programming
    MSAccess since 1.0, SQL Server since 6.5, Visual Basic (5.0, 6.0)

  10. #10
    Join Date
    Sep 2007
    Posts
    148
    Pkstormy, how can I ever say thank you. It is working!!!!! Paul, thank you for helping too. I am so very happy right now. So very happy. Thank

    CurrentDb.Execute ("UPDATE tblChangeControlFormDetails SET tblChangeControlFormDetails.PrintReport = True WHERE ((([ChangeControlFormNum] & [RevisedCode]) Like """ & LN & """))")

Posting Permissions

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