小大公式转换为接取VBA计算字段查询 [英] Small Large Formula Convert to Acess VBA Calculated Field Query

问题描述

我想下面的Excel公式转换为计算字段的访问。使用防爆pressions或用户定义的EX pression使用VBA code组合。任何帮助AP preciated！和AP preciate有关数据规范化的任何想法，但是这不是问题。 THX

I am trying to convert the following excel formula to a Calculated Field in Access. Using Expressions or combination of a user defined expression with VBA code. any help appreciated! And appreciate any thoughts about data normalization, however this is not the issue. Thx

<$c$c>IF(SMALL(T319:V319,2)>6,SMALL(T319:V319,1),IF(SMALL(T319:V319,2)=0,LARGE(T319:V319,1),SMALL(T319:V319,2)

细胞T319 - V319是通过生成的数据查询生成的每个记录的字段。称他们为A，B，C用于这一目的。

Cells T319 - V319 are fields in each record generated by a data query generated. call them A,B,C for this purpose.

推荐答案

假设我preSORT数据，以便从最大到最小，这三个值是用字母I，J和K psented重新$ P $这意味着J是小（T319：V319,2），K是小（T319：V319,1）我是大。（T319：V319,1）

Suppose I presort the data so that from largest to smallest, the three values are represented by the letters I, J, and K. This means J is SMALL(T319:V319,2), K is SMALL(T319:V319,1) and I is LARGE(T319:V319,1).

更新：固定伪code和功能的最后一节。我误读了Excel公式的嵌套IFS：P

伪code将是：

If J > 6 Then
    return K
ElseIf J = 0 Then
    return I
Else
    return J

您确定这是你想要的吗？

Are you sure this is what you want?

如果是这样，你可以做一个VBA函数如下：

If so, you could make a VBA function as follows:

Public Function GetVal(A As Variant, B As Variant, C As Variant)
    Dim I As Variant
    Dim J As Variant
    Dim K As Variant

    'Sort the three values so A, B, C becomes sorted largest to smallest as I, J, K
    If A > B Then
        If A > C Then
            I = A
            If B > C Then
                J = B
                K = C
            Else 'C > B
                J = C
                K = B
            End If
        Else 'C > A
            I = C
            J = A
            K = B
        End If
    Else 'B > A
        If B > C Then
            I = B
            If A > C Then
                J = A
                K = C
            Else 'C > A
                J = C
                K = A
            End If
        Else 'C > B
            I = C
            J = B
            K = A
        End If
    End If

    'Use I, J, K to find the result
    If J > 6 Then
        GetVal = K
    ElseIf J = 0 Then
        GetVal = I
    Else
        GetVal = J
    End If
End Function

这篇关于小大公式转换为接取VBA计算字段查询的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！