Multiplying columns with a WHERE condition

[j9070749]

Hi, I have a table called tbl_IE which has a field named frequency.



I want to multiple all of the values in frequency where the ID is 1. I want to display this on the form as the data is inputted and also on the final report.

Any help is much appreciated.

[ItsMe]

1 X 1 = 1 What exactly are you multiplying? Are you trying to get a count first?

[j9070749]

I will be calculating each field named 'frequency' (value between 0 and 1 up to 15 decimal places) where the scenarioID = 1.

I don't need to show the count on the report.

For example:
Scenario ID Frequency
1 0.001
1 0.01
1 0.0001
2 0.01
2 0.001

I would want to calculate (0.001*0.01*0.0001) to give 0.000000001.

Thanks

[ItsMe]

I am not an expert in algebra, but don't you want to prioritize which values get multiplied first? If you have a list of five values, how do you decide where to start?

This might be beyond my skillset.

[j9070749]

The values can be multiplied in any order e.g.

(0.001*0.01*0.0001) will give 0.000000001

and

(0.0001*0.001*0.01) will also give 0.000000001

2*5*6 = 60

2*6*5 = 60

[ItsMe]

Maybe something like this will work. Test it and let me know

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim dblValue As Double
Dim dblAnswer As Double
strSQL = "SELECT ID, frequency " _
        & "FROM tbl_IE " _
        & "WHERE ID = 1"
        
Set db = CurrentDb
Set rcd = db.OpenRecordset(strSQL, dbOpenSnapshot)
dblValue = 0
dblAnswer = 0
    With rst
    
            If Not .EOF Then
                .MoveFirst
                
                    If !frequency > 0 Then
                        dblAnswer = !frequency
                    Else
                        MsgBox "Frequency has no value"
                        GoTo Exit_Now
                    End If
                    
            Else
                MsgBox "No Matching Records found"
                GoTo Exit_Now
            End If
    
                While .EOF = False
                
                    dblValue = !frequency
                    
                    dblAnswer = dblAnswer * dblValue
                    .MoveNext

                Wend
    End With
Exit_Now:
MsgBox "dblAnswer = " & dblAnswer
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

[j9070749]

Thank you I will try this now and let you know.

[j9070749]

On line:
If Not .EOF Then
I got runtime error 91, Object variable or With block variable not set

[ItsMe]

On line:
If Not .EOF Then
I got runtime error 91, Object variable or With block variable not set

I had a typo with rst. I typed "rcd" when I..... set rst =

I noticed another thing too. I needed to ad another move next after "dblAnswer = !frequency"
and there will need to be some .EOF validation added to the movenext too.

I went ahead and started testing it here in a mock up. I am getting an Overflow error with the double. Let me see what I can come up with....

[j9070749]

Ok thank you for your guidance its helping me a lot!

[ItsMe]

This seems to be working. I added some more verfication into the code. Try to make sure you have enough records and also make sure that you are only multiplying values less than 1. If you include a value of 1 or greater I believe this will truncate your 15 decimal places down to fit the floating point in the double variable.

Dim db As DAO.Database
Dim rst As DAO.Recordset
Dim strSQL As String
Dim dblValue As Double
Dim dblAnswer As Double

strSQL = "SELECT ID, frequency " _
        & "FROM tbl_IE " _
        & "WHERE ID = 1"
        
Set db = CurrentDb
Set rst = db.OpenRecordset(strSQL, dbOpenSnapshot)

dblValue = 0
dblAnswer = 0

    With rst
    
            If Not .EOF Then
                .MoveFirst
                
                    If !frequency > 0 And !frequency < 1 Then
                    
                        dblAnswer = !frequency
                        .MoveNext
                        
                            If .EOF = True Then
                                MsgBox "There is only one record where ID = 1"
                                GoTo Exit_Now
                            End If
                        
                    Else
                        MsgBox "Frequency has no value"
                        GoTo Exit_Now
                    End If
                    
            Else
                MsgBox "No Matching Records found"
                GoTo Exit_Now
            End If
    
                While .EOF = False
                    dblValue = !frequency
                    
                    dblAnswer = dblAnswer * dblValue
                    
                    .MoveNext
                    
                Wend
    End With

Exit_Now:
MsgBox "dblAnswer = " & dblAnswer
rst.Close
Set rst = Nothing
Set db = Nothing
Exit Sub

[j9070749]

This works great thank you for your help

[ItsMe]

I don't know how often or when you will call this procedure. You could possibly speed things up a little by placing the declarations at the top of the module, just below the header. Then, if all you need to do is add another frequency value to the equation (because you are working in a form and added a record) , you could just assign the value to dblValue and calculate "dblAnswer = dblAnswer * dblValue"....... In theory anyway.

I think it should work bug free though.

[j9070749]

Instead of showing a messegebox if there is only one record, how would I get the value of that one record.

Thanks

[ItsMe]

Instead of showing a messegebox if there is only one record, how would I get the value of that one record.

Thanks

I think it already does. If there is only one record found, the value of frequency is in dblAnswer and displayed in the Msgbox after the handler "Exit_Now:"