Run-time error 3063: Too few parameters, expected 4.

The following function work in one db but not in another:

Function UpdateWkInPropAndMistCode()
'On Error GoTo ErrorHandler
    Dim db As DAO.Database, tdf As DAO.TableDef
    Dim sSql As String
   
    Set db = CurrentDb
   
    '--Check to see if table "tblFITMPROP" exist, if it does then delete it.
    For Each tdf In db.TableDefs
    If tdf.Name = "tblFITMPROP" Then
        DoCmd.DeleteObject acTable, tdf.Name
    End If
    Next tdf
   
    '--Create a table call "tblFitmprop" for up dating table "tblWklyFDFORRD'
    db.Execute " SELECT Val([IMITEM]) AS ItemNo, Val([IMSIZE]) AS [Size], QS36F_FITMPROP.IMSRC, " _
        & " QS36F_FITMPROP.IMBDP1, QS36F_FITMPROP.IMEDP1, BegDat([IMBDP1],[IMEDP1]) AS BegS1, " _
        & " EndDat([IMBDP1],[IMEDP1]) AS EndS1, QS36F_FITMPROP.IMWP1, QS36F_FITMPROP.IMMC1, QS36F_FITMPROP.IMBDP2, " _
        & " QS36F_FITMPROP.IMEDP2, BegDat([IMBDP2],[IMEDP2]) AS BegS2, " _
        & " EndDat([IMBDP2],[IMEDP2]) AS EndS2, QS36F_FITMPROP.IMWP2, QS36F_FITMPROP.IMMC2, QS36F_FITMPROP.IMBDP3, " _
        & " QS36F_FITMPROP.IMEDP3, BegDat([IMBDP3],[IMEDP3]) AS BegS3, EndDat([IMBDP3],[IMEDP3]) AS EndS3, " _
        & " QS36F_FITMPROP.IMWP3, QS36F_FITMPROP.IMMC3, QS36F_FITMPROP.IMBDP4, QS36F_FITMPROP.IMEDP4, " _
        & " BegDat([IMBDP4],[IMEDP4]) AS BegS4, EndDat([IMBDP4],[IMEDP4]) AS EndS4, QS36F_FITMPROP.IMWP4, " _
        & " QS36F_FITMPROP.IMMC4 INTO tblFITMPROP " _
        & " FROM QS36F_FITMPROP " _
        & " ORDER BY Val([IMITEM]), Val([IMSIZE]);"
       
       
    '--refreshing the database window
    Application.RefreshDatabaseWindow
   
    '--Updating the field 'WkInProp' in table "tblWklyFDFORRD" from table "tblFITMPROP".
    db.Execute " UPDATE tblFITMPROP INNER JOIN tblWklyFDFORRD ON (tblFITMPROP.Size = tblWklyFDFORRD.Size) " _
        & " AND (tblFITMPROP.ItemNo = tblWklyFDFORRD.ItemNo) " _
        & " SET tblWklyFDFORRD.WkInProp = " _
        & " IIf([DatSowd] Between [BegS1] And [EndS1],[IMWP1]," _
        & " IIf([DatSowd] Between [BegS2] And [EndS2],[IMWP2]," _
        & " IIf([DatSowd] Between [BegS3] And [EndS3],[IMWP3]," _
        & " IIf([DatSowd] Between [BegS4] And [EndS4],[IMWP4]))));"
       
    '--Updating the field 'WkInProp' in table "tblWklyFDFORRD" from table "tblFITMPROP".
    db.Execute " UPDATE tblFITMPROP INNER JOIN tblWklyFDFORRD ON (tblFITMPROP.Size = tblWklyFDFORRD.Size) " _
        & " AND (tblFITMPROP.ItemNo = tblWklyFDFORRD.ItemNo) " _
        & " SET tblWklyFDFORRD.MisCode = " _
        & " IIf([DatSowd] Between [BegS1] And [EndS1],[IMMC1]," _
        & " IIf([DatSowd] Between [BegS2] And [EndS2],[IMMC2]," _
        & " IIf([DatSowd] Between [BegS3] And [EndS3],[IMMC3]," _
        & " IIf([DatSowd] Between [BegS4] And [EndS4],[IMMC4]))));"

   
   
    db.Close
    Set db = Nothing
   
   
'Exit Function
'
'
'ErrorHandler:
'    MsgBox "Error number " & Err.Number & " occurred in UpdateWkInPropAndMistCode()." _
'        & vbCrLf & vbCrLf & Err.Description, vbExclamation
'    Err.Clear


End Function

The following is the sql that is highlight in yellow:

    '--Create a table call "tblFitmprop" for up dating table "tblWklyFDFORRD'
    db.Execute " SELECT Val([IMITEM]) AS ItemNo, Val([IMSIZE]) AS [Size], QS36F_FITMPROP.IMSRC, " _
        & " QS36F_FITMPROP.IMBDP1, QS36F_FITMPROP.IMEDP1, BegDat([IMBDP1],[IMEDP1]) AS BegS1, " _
        & " EndDat([IMBDP1],[IMEDP1]) AS EndS1, QS36F_FITMPROP.IMWP1, QS36F_FITMPROP.IMMC1, QS36F_FITMPROP.IMBDP2, " _
        & " QS36F_FITMPROP.IMEDP2, BegDat([IMBDP2],[IMEDP2]) AS BegS2, " _
        & " EndDat([IMBDP2],[IMEDP2]) AS EndS2, QS36F_FITMPROP.IMWP2, QS36F_FITMPROP.IMMC2, QS36F_FITMPROP.IMBDP3, " _
        & " QS36F_FITMPROP.IMEDP3, BegDat([IMBDP3],[IMEDP3]) AS BegS3, EndDat([IMBDP3],[IMEDP3]) AS EndS3, " _
        & " QS36F_FITMPROP.IMWP3, QS36F_FITMPROP.IMMC3, QS36F_FITMPROP.IMBDP4, QS36F_FITMPROP.IMEDP4, " _
        & " BegDat([IMBDP4],[IMEDP4]) AS BegS4, EndDat([IMBDP4],[IMEDP4]) AS EndS4, QS36F_FITMPROP.IMWP4, " _
        & " QS36F_FITMPROP.IMMC4 INTO tblFITMPROP " _
        & " FROM QS36F_FITMPROP " _
        & " ORDER BY Val([IMITEM]), Val([IMSIZE]);"

I created a fresh db and just paste the code and it's working, I have the same reference check in both db.

Maybe I am looking at it for too long.

perennial
LVL 1
perennialAsked:
Who is Participating?
 
flavoCommented:
Hi perennial,
You have mis-spelt 4 fields in your query.. Which ones, only you'll be able to see.

You could add a Debug.Print "Query SQL" and copy that to the query builder, it should prompt you which ones are incorrect.
Dave :-)
0
 
flavoCommented:
> I created a fresh db and just paste the code and it's working, I have
> the same reference check in both db.
hmm.. Sounds strange.. Have you tired importing all the objects into a fresh container?  Maybe it's corupted itself somehow..
Dave
0
 
perennialAuthor Commented:
I see my mistake.  I link the table and since it's been link there are 4 additional fields in the table.

Thank you very much  for your help.
perennial
0
 
flavoCommented:
Glad to help!

Good luck with the rest of your project(s)
Dave :^)
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.