MS Access - Data Type Mismatch

问题:

I have the follow VBA code and it returns a 'data type mismatch in criteria expression' while executing. I cannot seem to find out why it is giving me this error.

Can anybody help me?

VBA:

Public Function GezaagdeOmzet(ByVal TotaalPrijs As Double, ByVal AantalArtiklesPerOrder As Double, ByVal TotaalArtiklesPerOrder As Double, ByVal AantalArtiklesVerwijderedUitZaaglijst As Double) As Double

    Dim result As Double

    On Error GoTo ErrHandler

    If IsNumeric(TotaalPrijs) = False Then
        MsgBox ("TotaalPrijs not a number")
        MsgBox (TotaalPrijs)
    End If

    If IsNumeric(AantalArtiklesPerOrder) = False Then
        MsgBox ("AantalArtiklesPerOrder not a number")
        MsgBox (AantalArtiklesPerOrder)
    End If

    If IsNumeric(TotaalArtiklesPerOrder) = False Then
        MsgBox ("TotaalArtiklesPerOrder not a number")
        MsgBox (TotaalArtiklesPerOrder)
    End If

    If IsNumeric(AantalArtiklesVerwijderedUitZaaglijst) = False Then
        MsgBox ("AantalArtiklesVerwijderedUitZaaglijst not a number")
        MsgBox (AantalArtiklesVerwijderedUitZaaglijst)
    End If

    If Not TotaalPrijs = 0 Then
        If AantalArtiklesPerOrder > 0 Then
            result = TotaalPrijs / (AantalArtiklesPerOrder * TotaalArtiklesPerOrder) * AantalArtiklesVerwijderedUitZaaglijst
            On Error GoTo ErrHandler
        Else
            MsgBox ("AantalArtiklesPerOrder is null, Cannot do calculation")
        End If
    Else
        MsgBox ("TotaalPrijs is null, cannot do division")
    End If

Exit Function
ErrHandler:
    MsgBox ("TotaalPrijs: " & TotaalPrijs & " TotaalArtiklesPerOrder: " & TotaalArtiklesPerOrder & " AantalArtiklesPerOrder: " & AantalArtiklesPerOrder & " AantalArtiklesVerwijderedUitZaaglijst: " & AantalArtiklesVerwijderedUitZaaglijst)
End Function

SQL Query in MS Access

GezaagdeOmzet: Sum(GezaagdeOmzet([TotaalPrijs],[tbl_ArtikelsPerOrder]![Aantal],[Totaal],[tbl_ArtikelVerwijderdUitZaaglijst]![Aantal]))

Is there anyway to catch the error I'm getting with VBA?

Cstr or CDec or CDbl is not handling this error.

回答1:

The above function is a little strange, as haarrrgh says.

It should look more like the code below. You do not need to check that each of the arguments (TotaalPrijs, ByVal AantalArtiklesPerOrder etc) is a number, because you pass them As Double. If you pass anything except a number, such as a letter or Null, you will get an error. If this is not what you want, consider passing the arguments As Variant, you can then check that they are numbers. However, as you are using this in a query, I suggest that you do not use message boxes, if the argument is null, make it zero, if that is what it is supposed to be.

Note also GezaagdeOmzet = , rather than result =

EDIT re Comments

Public Function GezaagdeOmzet(ByVal TotaalPrijs As Variant, _
    ByVal AantalArtiklesPerOrder As Variant, _
    ByVal TotaalArtiklesPerOrder As Variant, _
    ByVal AantalArtiklesVerwijderedUitZaaglijst As Variant) As Double

    On Error GoTo ErrHandler

    If (Nz(AantalArtiklesPerOrder,0) * Nz(TotaalArtiklesPerOrder,0)) * _
       Nz(AantalArtiklesVerwijderedUitZaaglijst,0) = 0 Then
       GezaagdeOmzet = 0
    Else
        GezaagdeOmzet = Nz(TotaalPrijs,0) / _
        (Nz(AantalArtiklesPerOrder,0) * Nz(TotaalArtiklesPerOrder,0)) * _
       Nz(AantalArtiklesVerwijderedUitZaaglijst,0)
    End If

Exit Function

ErrHandler:
   ' MsgBox ("TotaalPrijs: " & TotaalPrijs & " TotaalArtiklesPerOrder: " _
    & TotaalArtiklesPerOrder & " AantalArtiklesPerOrder: " & AantalArtiklesPerOrder _
    & " AantalArtiklesVerwijderedUitZaaglijst: " _
    & AantalArtiklesVerwijderedUitZaaglijst)
End Function

回答2:

@Remou has provided what looks to me like a working solution, but he hasn't really fully explained why he implemented it differently. Here are some of the reasons:

1. the parameters for your original function are all defined as Double. These can be neither Null nor non-numeric, so all the tests for IsNumeric() in your original code are a waste of time since they will never return FALSE.

2. "data type mismatch in criteria expression" is a very common error message from queries that pass Nulls to user-defined functions that cannot accept Nulls in their parameters. Declaring your parameters as variants is one approach, but variants can lead to all sorts of issues and you lose strong data typing. I would recommend keeping the Double data types, and passing CDbl(Nz([TotaalPrijs],0)) from the original query.

回答3:

Why do run a function which returns a double, only to convert it to a string and sum it up (even it's only a single value)?
I don't get it.

What happens if you run the function directly, not in a query?
Does it throw an error as well?

And last but not least - there seem to be two bugs in the function, as it is now:

1) If TotaalArtiklesPerOrder or AantalArtiklesVerwijderedUitZaaglijst are 0, it throws an error (which gets caught, but anyway...) because then you divide by zero in this line :

result = TotaalPrijs / (AantalArtiklesPerOrder * TotaalArtiklesPerOrder) * AantalArtiklesVerwijderedUitZaaglijst

You do check if TotaalPrijs is 0, but you're checking it the wrong way round: it's possible to divide zero by anything, but it's not possible to divide anything by zero.

2) The function will always return 0, because you calculate your result (in the variable "result"), but you don't return it. You have to do this explicitly:

GezaagdeOmzet = result