Calculation to Return a certain Value

[ang]

I am having problems entering a calculation to return a certain value. I need to be able to say that if [Field 3] is 0, then [Field 6] should return a value of 1. But if [Field 3] and [Field 4] have a value greater than zero, I want to divide [Field 4] from [Field 3] to give me a percentage. I have tried numerous ways and cannot produce the results I want. I continue to get the "!Num" message in [Field 6] if [Field3] is 0. Can someone help me??

Thanks,
Ang

 

[MichaelRed]

Ang,

Your syntax is a bit different form what I am familiar with.

When you say "... [Field6] should return a value ...", it seems to imply that the value of 1 is already in [Field6].

If You want to set the value in [Field6], then you should be able to, but it depends on wheather this refers to a field in a table/query or a field (control) on a form. Also, depending on WHAT [Field6] is, there may be some restrictions on it's use (e.g. If it is a field of a table and it's properties don't allow the value, then you may not be able to set it).

It would probably help to get a specific response if you could elaborate on the situation a little. Post some additional details.


MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[ang]

Thanks, Michael! I am trying to set up the calculation in a report which is based on a table. To give more details, I am creating a database for return audit. In the table[Field3] is the returns called in,[field4] are the acutal returns, [Field 5] is the variance (subtract [Field 4] from [Field3]). [Field 6] is the % of accuracy. So, if I enter the calculation [Field4]/[Field3], this gives me my percentage. However, if I entered 0 in [Field3] because none were called in, and 0 in [Field4] because there were no actual returns, this gives me a "!Num" message instead of a value. If it is zero across the board, then the % should be 100% accuracy -- no returns called in or sent in. Another example, I have a spreadsheet in Excel. Gives me the correct result. Ex. of formula in Excel - IF(E7=0,1,IF(C7=0,0D7/C7)) I have tried to use a simiular format in Access but it doesn't work. I know it is an IIf calculation but I can't get it to work. Any clues?

Thanks,
Ang

 

[MichaelRed]

I'm still unclear on what (or where) you are attempting to do this, however a few points may help:

Database systems generally avoid the storage of items which are (readily) calculated - especially from a single record, thus your [Field6] appears to be unnecessary.

Ms. Acccess is somewhat particular about the basic arithmatic. While your logic says Zero / Zero = 1, I believe that Ms. Access says that division by Zero is not possible (actually, it is an Overflow value).

The following trivial function shows the issue:

Public Function basDivZero()

    Dim dbs As Database
    Dim rst As Recordset

'   tblTstDivZero
'        Field3  Field4  Field6
'        1   1   1
'        4   8   0.5
'        3   6   0.5
'        7   1   7
'        18  7   2.571429
'        4   5   0.8
'        0   0   1

    Set dbs = CurrentDb
    Set rst = dbs.OpenRecordset("tblTstDivZero", dbOpenDynaset)

    While Not rst.EOF
        With rst
            .Edit
'                If (!Field2 = 0) Then               'Look at this
'                    !Field3 = 1                     'Look at this
'                 Else                               'Look at this
                    !Field3 = !Field1 / !Field2
'                End If                              'Look at this
            .Update
        End With
        rst.MoveNext
    Wend

    Set rst = Nothing
    Set dbs = Nothing

End Function

I do not know the appropiate table name, so this is not exactly your situation, BUT:

If you modify the table to suit your table and run the prog, you will see the error wherever [Field4] is zero, regardless of the value in Field3]. Obviously, removing the comment character for the three lines will run w/o error, and product the results shown in the function.



MichaelRed
mred@duvallgroup.com
There is never time to do it right but there is always time to do it over

 

[ang]

Thanks for your help, Michael. I will try it out.

Thanks,
Ang