What is a more efficient way to write this code in a text?

I haven't done any extensive testing or anything, but if I recall correctly, the "D" functions are known to not perform very well in some scenarios.  You could, most likely, accomplish the same thing through code like the following:

Function fcnCalculateSomething() As Double

    '*** Add Description of what this is calculating here ***
    
    'Turn on error handling
    On Error GoTo Error_Handler
    
    'Declare variables
    Dim objPercentagesRS As DAO.Recordset2
    Dim strSQL As String
    Dim lng100Count As Long
    Dim dblNot100Total As Double
    
    'Construct SQL string
    strSQL = "SELECT * FROM qryPercentages WHERE " & _
             "Unit='2-4ID' AND " & _
             "InShopDate IS NOT NULL;"
             
    'Instantiate objPercentagesRS
    Set objPercentagesRS = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    
    'Check for EOF
    If objPercentagesRS.EOF = True Then
    
        'No Records... return0
        fcnCalculateSomething = 0
        
    Else
    
        'Move to first record
        objPercentagesRS.MoveFirst
        
        'Loop through records
        Do While objPercentagesRS.EOF = False
        
            'Check field "Percentages" for "100"
            If objPercentagesRS.Fields("Percentages") = "100" Then
            
                'Increment lng100Count
                lng100Count = lng100Count + 1
                
            Else
            
                'Add the value as a percent to dblNot100Total
                dblNot100Total = dblNot100Total + (CDbl(objPercentagesRS.Fields("Percentages") / 100))
                
            End If
            
            'Next record
            objPercentagesRS.MoveNext
            
        Loop
        
    End If
    
    'Close RS
    objPercentagesRS.Close
    
    'Clean  Up
    Set objPercentagesRS = Nothing
    
    'Return value
    fcnCalculateSomething = CDbl(lng100Count) + dblNot100Total
    
    'Exit before error handling
    Exit Function
    
Error_Handler:

    'Return -1
    fcnCalculateSomething = -1
    
End Function

Select allOpen in new window

FYI... I called it "fcnCalculateSomething()" because I don't really know what we're calculating here... please do name it something appropriate if you go this route :).

I'm not certain this is EXACTLY like your DCount() function... you might want to do some testing with it.  Also, it's important to note that you could add parameters so that you could pass in, for example, a string for "Unit".  This would make the function more reusable for you.

Why not use DSUM("Val(percentages]", "qryPercentages", "unit = '2-4ID' and nz(InshopDate, "") <> "")

I tried that and I get a error message saying it needs a = sign

Access Guy

How would that be viewed in a text box? I need the answer to be for each unit .

examble

1ID    65%
2ID    75%

The function would look like this:

Function fcnCalculateSomething(inUnit As String) As Double

    '*** Add Description of what this is calculating here ***
    
    'Turn on error handling
    On Error GoTo Error_Handler
    
    'Declare variables
    Dim objPercentagesRS As DAO.Recordset2
    Dim strSQL As String
    Dim lng100Count As Long
    Dim dblNot100Total As Double
    
    'Construct SQL string
    strSQL = "SELECT * FROM qryPercentages WHERE " & _
             "Unit='" & inUnit & "' AND " & _
             "InShopDate IS NOT NULL;"
             
    'Instantiate objPercentagesRS
    Set objPercentagesRS = CurrentDb.OpenRecordset(strSQL, dbOpenDynaset)
    
    'Check for EOF
    If objPercentagesRS.EOF = True Then
    
        'No Records... return0
        fcnCalculateSomething = 0
        
    Else
    
        'Move to first record
        objPercentagesRS.MoveFirst
        
        'Loop through records
        Do While objPercentagesRS.EOF = False
        
            'Check field "Percentages" for "100"
            If objPercentagesRS.Fields("Percentages") = "100" Then
            
                'Increment lng100Count
                lng100Count = lng100Count + 1
                
            Else
            
                'Add the value as a percent to dblNot100Total
                dblNot100Total = dblNot100Total + (CDbl(objPercentagesRS.Fields("Percentages") / 100))
                
            End If
            
            'Next record
            objPercentagesRS.MoveNext
            
        Loop
        
    End If
    
    'Close RS
    objPercentagesRS.Close
    
    'Clean  Up
    Set objPercentagesRS = Nothing
    
    'Return value
    fcnCalculateSomething = CDbl(lng100Count) + dblNot100Total
    
    'Exit before error handling
    Exit Function
    
Error_Handler:

    'Return -1
    fcnCalculateSomething = -1
    
End Function

Select allOpen in new window

Then enter the following for the Control Sources:

=fcnCalculateSomething("2I-4D")
=fcnCalculateSomething("2ID")
=fcnCalculateSomething("1ID")
etc...

Select allOpen in new window

Thank you for your time.  I get -1 answer.



 when I put the function

and I took a text box and put =fcnCalculateSomething("2I-4D")

Maybe it would help if I give you a example copy of my database.

I have it from before... that code does work in your sample on my PC... you should try breakpoints to track down the error... I'm out of the office now but I'll check back tomorrow. BTW -1 is what the function returns when there's an error if you didn't know that.

Thank you will read this I dont really use error message break points. this wil help me solving a error message faster.