Error 2147217904

[Thedda]

I have a form that calculates percent complete and has a record counter so when the SumOfcountOfCompl qty >= 0.85 it will add a check mark. I keep getting this error code 2147217904 that states that No value given for one or more requred parameters. Here is the code. Appreciate the help....


Option Compare Database
Option Explicit

Private Sub Form_Load()
Call Compute85_Percent_Compl_Pack
DoCmd.Close
End Sub

Sub Compute85_Percent_Compl_Pack()
Dim objRec As New ADODB.Recordset
Dim sSQLCode As String
Dim lNum As Long
Dim lRec As Long
Dim lQty As Long
Dim sPO As String
Dim blHit85 As Boolean

On Error GoTo ErrorHandler

DoCmd.OpenQuery "qryDel_85%_Compl_RawData_Pack", acNormal, acEdit
DoCmd.OpenQuery "qryPack_85%Compl_1", acNormal, acEdit

DoEvents

sSQLCode = "SELECT [tbl85%ComplRawData_Pack].PORD, [tbl85%ComplRawData_Pack].[Pack Comp Date], [tbl85%ComplRawData_Pack].[SumOfCountOfCompl Qty], [tbl85%ComplRawData_Pack].[PO Qty], [tbl85%ComplRawData].[Total % Compl], [tbl85%ComplRawData_Pack].Hit85OrMore, [tbl85%ComplRawData_Pack].PercentHit FROM [tbl85%ComplRawData_Pack] ORDER BY [tbl85%ComplRawData_Pack].PORD, [tbl85%ComplRawData_Pack].[Pack Comp Date];"
objRec.Open sSQLCode, CurrentProject.Connection, adOpenKeyset, adLockOptimistic, adCmdText
lRec = objRec.RecordCount

If lRec > 0 Then
objRec.MoveFirst
sPO = ""
blHit85 = False
lQty = 0

For lNum = 1 To lRec
If sPO <> Trim(objRec!PORD & "") Then
blHit85 = False
sPO = Trim(objRec!PORD & "")
lQty = objRec![SumOfCountOfCompl Qty]
ElseIf blHit85 = False Then
lQty = lQty + objRec![SumOfCountOfCompl Qty]
End If

If (lQty / objRec![PO Qty]) >= 0.85 And blHit85 = False Then
objRec!Hit85OrMore = True
objRec!PercentHit = (lQty / objRec![PO Qty]) * 100
objRec.Update
blHit85 = True
End If

objRec.MoveNext
Next lNum
End If

objRec.Close

Set objRec = Nothing

Exit Sub
ErrorHandler:
MsgBox Err.Description, vbCritical, "Error: " & Err.Number
End Sub

 

[Atomic Shrimp]

Am I right in understanding that your table/query names contain the % character? - that's a wildcard in some contexts...

 

[Thedda]

It's working on another form that I have. Same code just different name, instead of using tbl85%ComplRawData_Pack it's using tbl85%ComplRawData. All the same fields except for one and that's the
[tbl85%ComplRawData_Pack].[Pack Comp Date]

The other one has [tbl85%ComplRawData].[Bkt Comp Date]. So I don't understand why this one won't work, I changed all the pertinent info....

 

[boblarson]

You may have null in some records. Use the NZ function to encapsulate each part to ensure that there is data that the calculation can use. A calculation cannot use null. Which line does it highlight as being the error?

 

[Thedda]

Your right, I was having null issues. Corrected my query and it runs great.
Amaizing how you can not see the things starring right at you face...

Thanks