Databases
StackOverflow2013
Postsdbo
POlooping in 3d array access vba not returning all results
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POlooping in 3d array access vba not returning all results
    primarykey
    data
    text
    <p>I have a project with 4 scheduled payments associated with 4 "contract" dates, stored in tblPaySch However, sometimes we receive the payments (stored in tblTrans), in different amounts on different dates (the "Actual") date</p> <p>I am attempting an array that defines the 4 dates (identified by ID 1 - 4) and the expected amount, then compares it against the tblTrans to to see if the expected payment has been exceeded, and if so, mark that transaction date as the "Actual" date.</p> <p>There's either something wrong with my array or something wrong with my loop, as I can get the results for ID1, (i.e, the associate expected pay and the transaction date that met it), but can't get the other 3 IDs.</p> <p>I'm calling it in a query with GetDate(prjID) to pass the prjId to the function.</p> <p>Here is my code:</p> <pre><code>'This function is a multidimensional array that can hold multiple values Public Function GetDate(intID As Long) As Variant Dim intTot As Long Dim i As Integer Dim i2 As Integer 'Define recordset to get expected payment data Dim rsPrj As DAO.Recordset Set rsPrj = CurrentDb.OpenRecordset("SELECT * FROM tblPaySch WHERE PrjID =" &amp; intID, dbOpenSnapshot) 'Define recordset to get transaction data Dim rs As DAO.Recordset Set rs = CurrentDb.OpenRecordset("Select * from tblTrans where PrjID=" &amp; intID, dbOpenSnapshot) 'Store milestone payments in RA Dim RA(0 To 4, 0 To 4, 0 To 4) As Variant RA(0, 0, 0) = rsPrj!MSCdbID 'payment Id, 4 of which are associated with each PrjID RA(0, 1, 0) = rsPrj!PayIncGST 'expected payment amount, of 4 different totals RA(0, 0, 1) = rs!RefDate 'Actual date from tblTrans intTot = 0 Do While rs.EOF intTot = intTot + rs!Amt 'refers to the amount of the transaction '-----Check for milestone exceeded For i = 0 To 4 For i2 = 0 To 4 If IsNull(RA(i, i2, 1) And RA(i, i2, 0) &lt;= intTot) Then RA(i, i2, 1) = rs!RefDate End If Next i2 Next i Loop GetDate = RA(0, 0, 1) Debug.Print RA(1, 0, 0) Debug.Print RA(0, 1, 0) Debug.Print RA(0, 0, 1) End Function </code></pre> <p>Thank you in advance, and please excuse any obvious noobie mistakes, this is my first ever array function.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USOlle Sjögren
    1. USKDJ
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. POlooping in 3d array access vba not returning all results
      1. This table or related slice is empty.
      1. VTApproveEditSuggestion
    1. COshould you be using `IsEmpty()` instead of `IsNull()` function?, and I think you mean `If IsEmpty(RA(i, i2, 1)) And RA(i, i2, 0) <= intTot Then` The () is in wrong location, I think the condition will never be matched. You should try to debug by stepping into each line.
      singulars
      1. POlooping in 3d array access vba not returning all results
      1. USLarry
    2. CODo you mean: Do While rs.EOF? On reading this, I must admit that my first thought is that it's such a complicated approach that I'd give in and take an easier one. Multi-dimensional arrays are rarely the answer to anything in VBA.
      singulars
      1. POlooping in 3d array access vba not returning all results
      1. USAndy Brown
    3. COThank you. I tried the changes you suggested, but I'm still getting the same result - I have 90 records and Debug.print (1,0,0) is returning 1 for all 90, instead of 1,2,3,4 for each prjid. I'm retrying as a 1 dimensional function and going to try building up from there.
      singulars
      1. POlooping in 3d array access vba not returning all results
      1. USKDJ
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload