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

curAmountSaleUpToCurrentPO = DSum("Stock", "QryStockRinci", "[Tanggal] <= '" & strDate & "' AND [kode_barcode] = '" & strProductID & "'") — Daniel Fersbeanto

PaulFrancis PaulFrancis · Accepted Answer · 2014-07-24T16:35:30

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

Error "Data type mismatch in criteria expression"

1 Answers