Results 1 to 2 of 2
  1. #1
    3 Star Lounger MacroAlan's Avatar
    Join Date
    Feb 2003
    Location
    St Louis, Missouri, USA
    Posts
    254
    Thanks
    6
    Thanked 1 Time in 1 Post

    Thumbs up

    I wrote a function in Access 2007 that pulls the values from a swarm of queries and displays in my report. This part is working OK except when the Institution Account Number is not found in the Recordset. Then I get a #Error.

    Code:
    Function getPrevPeriodAmt(instAcct As String)
    	Dim PrevDB  As dao.Recordset
    	Set PrevDB = CurrentDb.OpenRecordset("qryPreMasterSummariesPREV", dbOpenDynaset, dbSeeChanges)
    	On Error GoTo ErrThing
    	
    	Do Until PrevDB.EOF
    	With PrevDB
    		If ![institution account number] = instAcct Then
    			If ![market value amount] = 0 Then
    				Exit Do
    			Else
    				getPrevPeriodAmt = ![market value amount]
    			Exit Do
    			End If
    		End If
    	End With
    	PrevDB.MoveNext
    	Loop
    PrevDB.Close
    
    Exit Function
    ErrThing:
    	getPrevPeriodAmt = 0	   'Needs a tweak 09/23/09  ARB
    End Function
    Is there an instruction I can give for a value if it gets to EOF and have not found the Acct? In most of these cases the Acct Number is blank
    Alan

  2. #2
    Plutonium Lounger
    Join Date
    Mar 2002
    Posts
    84,353
    Thanks
    0
    Thanked 29 Times in 29 Posts
    Instead of looping, I'd search for the value:

    Code:
    Function getPrevPeriodAmt(instAcct As String)
      Dim PrevDB  As DAO.Recordset
      Set PrevDB = CurrentDb.OpenRecordset("qryPreMasterSummariesPREV", dbOpenDynaset, dbSeeChanges)
      PrevDB.FindFirst "[institution account number]='" & instAcct & "'"
      If PrevDB.NoMatch Then
    	' Not found, provide return value
    	getPrevPeriodAmount = ...
      ElseIf ![market value amount] = 0 Then
    	' Which value here?
    	getPrevPeriodAmount = ...
      Else
    	getPrevPeriodAmt = ![market value amount]
      End If
      PrevDB.Close
      Set PrevDB = Nothing
    End Function

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •