Error "Data type mismatch in criteria expression"

Question

This code have error "Data type mismatch in criteria expression", I think it because the strDate and data from my query QryStockRinci.

Public Function ReturnAmountSaleRev(strDate As Date, strProductID As String, curAmount As Currency, curAmountSale As Currency) As Variant
Dim curAmountSaleUpToCurrentPO As Integer
Dim varAmountSalePriorToCurrentPO As Variant
'Get the total Amount for the current ProductID up to and including given PO.
curAmountSaleUpToCurrentPO = DSum("Stock", "QryStockRinci", "[Tanggal] <= '" & strDate & "' AND [kode_barcode] = '" & strProductID & "'")
'If there is enough in SalesAmount to cover the whole cost, return the whole Amount.
If curAmountSale - curAmountSaleUpToCurrentPO >= 0 Then
ReturnAmountSaleRev = Format(curAmount, "0.00")
Else
'Get the the total Amount in ProductID prior to current PO.
varAmountSalePriorToCurrentPO = DSum("Stock", "QryStockRinci", "[Tanggal] < '" & strDate & "' AND [kode_barcode] = '" & strProductID & "'")
'If current PO is first in ProductID, varAmountSalePriorToCurrentPO will be null;
'determine covered amount.
If IsNull(varAmountSalePriorToCurrentPO) = True Then
If curAmount <= curAmountSale Then
ReturnAmountSaleRev = Format(curAmount, "0.00")
Else
ReturnAmountSaleRev = Format(curAmountSale, "0.00")
End If
Else
'If current PO is not first in ProductID, varAmountSalePriorToCurrentPO
'will have a value; determine the covered amount.
varAmountSalePriorToCurrentPO = curAmountSale - varAmountSalePriorToCurrentPO
If varAmountSalePriorToCurrentPO <= 0 Then
ReturnAmountSaleRev = 0
Else
ReturnAmountSaleRev = Format(varAmountSalePriorToCurrentPO, "0.00")
End If
End If
End If

End Function

Answer 1

You need to treat Dates as Dates and not strings, also try to enclose Domain functions with Nz to avoid assigning Null values to Integer or any other Data Type that is other than Variant.

Public Function ReturnAmountSaleRev(strDate As Date, strProductID As String, curAmount As Currency, curAmountSale As Currency) As Variant
    Dim curAmountSaleUpToCurrentPO As Long
    Dim varAmountSalePriorToCurrentPO As Variant

    'Get the total Amount for the current ProductID up to and including given PO.'
    curAmountSaleUpToCurrentPO = Nz(DSum("Stock", "QryStockRinci", "[Tanggal] <= " & Format(strDate, "\#mm\/dd\/yyyy\#") & " AND [kode_barcode] = '" & strProductID & "'"), 0)

    'If there is enough in SalesAmount to cover the whole cost, return the whole Amount.'
    If curAmountSale - curAmountSaleUpToCurrentPO >= 0 Then
        ReturnAmountSaleRev = Format(curAmount, "0.00")
    Else
        'Get the the total Amount in ProductID prior to current PO.'
        varAmountSalePriorToCurrentPO = Nz(DSum("Stock", "QryStockRinci", "[Tanggal] < " & Format(strDate, "\#mm\/dd\/yyyy\#") & "' AND [kode_barcode] = '" & strProductID & "'"), 0)

        'If current PO is first in ProductID, varAmountSalePriorToCurrentPO will be null;'
        'determine covered amount.'
        If IsNull(varAmountSalePriorToCurrentPO) = True Then
            If curAmount <= curAmountSale Then
                ReturnAmountSaleRev = Format(curAmount, "0.00")
            Else
                ReturnAmountSaleRev = Format(curAmountSale, "0.00")
            End If
        Else
            'If current PO is not first in ProductID, varAmountSalePriorToCurrentPO
            'will have a value; determine the covered amount.'
            varAmountSalePriorToCurrentPO = curAmountSale - varAmountSalePriorToCurrentPO

            If varAmountSalePriorToCurrentPO <= 0 Then
                ReturnAmountSaleRev = 0
            Else
                ReturnAmountSaleRev = Format(varAmountSalePriorToCurrentPO, "0.00")
            End If
        End If
    End If
End Function