First-time array user

[fredfortsonsr]

Having an ARRAY problem and data mismatch - one may be causing the other.

This is my first venture into dealing with an array so I may have misinterpreted something in the things I've read.

My goal is simple (I think). I have a query that contains all the info I need to produce some calculations. I need to step thruough the records in the query and come up with two answers.

Answer one: The sum of a number and a running total based on a field in the query.

Answer two: I need to divide the running total by a set value obtained earlier in the program and store that in an array for later evaluation.

INFO:
[ConcentrationA] is data type TEXT
rawmatTTL is the recordcount of the query

Declarations:
Dim moleCounter as Single
Dim calcResult() as Variant
Dim dpDEGREE as Single

moleCounter = 0
With RstDewPoint
Do While True
For i = 1 To .rawmatTTL
If dpDEGREE <> 0 Then
moleCounter = moleCounter + [ConcentrationA]
ReDim Preserve calcResult(1 To rawmatTTL) As Variant
calcResult(i) = dpDEGREE / moleCounter
End If
.MoveNext
Next i
Exit Do
End With

As a result I am getting an endless loop that virtually locks up Access and I have to force the program closed. It appears to occur at the calcResult (i) section of the code.

So, my first attempt at an array has failed and I need someoen to please pull me from the mire.

Many Thanks

Fred

 

[KDg]

hi Fred,

there's a couple of problems with your loop. Firstly the "Do While True". True is always true so you'll never get out of the loop, you just get and EOF error. You could use "Do While not rstDewPoint.EOF" or "Do Until rstDewPoint.EOF" instead - as soon as you hit the last record, it'll jump out of the loop.

Next, using ReDim Preserve can be hideously expensive - every call copies the whole array into a new piece of memory - the bigger the array, the worse it gets. Instead it may be quicker to get the recordcount of rstxxx and use that in a one-off redim. eg

rstDewPoint.movelast
ReDim CalcResult(rstDewpoint.recordcount)
rstDewPoint.MoveFirst

Moving like that can be pretty expensive too, but is probably gonna be quicker than recreating the array for every record. There's no need to state a lower bound for the array, if you don't want CalcResult(0) you can just ignore it, it really won't be using much space relative to everything else. <aside> I believe .NET does away with non 0 based arrays anyhow </aside>

I hope that helps, I'm guessing that your maths is sound and you're happy moving data in and out of an array. If I've missed the point please post back

Drew

 

[fredfortsonsr]

Drew,

I saw some mis-thinking I had done and noticed I told you something in
error. The dpDegree is a String used to determine which field value to use.
That part I corrected, however, I NEED to process the recordset in the order
that I originally sorted them. The following gets the correct results yet it
keeps repeating the first row of data. Commenting out the MoveFirst
statement and it doesn't start at the first record as needed. I checked the
WITH statement but saw nothing about processing the records in a certain
order. Why, since the recordset is sorted correctly originally, does the
code below without MOVEFIRST change the order in which it processes records?

If moleCounter >= 0 Then
i = 1
With RstDewPoint
.MoveFirst
If dpDEGREE = "[ZDegPt]" Then
GasDpNum = ![ZDegPt]
Else
GasDpNum = ![40DegPt]
End If
Debug.Print "Current dpDEGREE = "; GasDpNum
moleCounter = moleCounter + (RstDewPoint![ConcentrationA] / 100)
Debug.Print "current moleCounter = "; moleCounter
Debug.Print "Current Gas being evaluated "; RstDewPoint![Main Gas
Name]
calcResult(i) = GasDpNum / moleCounter
Debug.Print "Current calcResult = "; calcResult(i)
i = i + 1
.MoveNext
End With
End If

Thanks once again.

Fred

 

[KDg]

Hi Again Fred,

I'm just working my way thru your code at the moment - the first bit of this is just picky, feel free to ignore it - I only include it because it may help

When comparing 2 strings strcomp(StringOne,StringTwo)=0 is faster than StringOne=StringTwo

As there's no loop in the code above i guess that this bit sits within a loop. In which case you're moving back to the first record on every loop (.MoveFirst) hence the repeating record! Just move it above the loop and it'll be fine. As to why not moving first should affect the records...no idea. Every recordset i've ever made has started on the first record ( if there is one ) and moving thru them has never affected the order. Are you sure you're not manipulating them somewhere else in your code?

HTH

Drew

 

[fredfortsonsr]

I think there a two questions you have here. Am I manipulating the data elsewhere? This is the command that creates rstDewPoint. I don't change it anywhere, but I do refer to it frequently in various parts of the code.

Set RstDewPoint = WorkDB.OpenRecordset("SELECT * FROM qryFindDewPoint " & _
"WHERE ([Mixture Id] = " & MixtureId & ") " & _
"AND [ORDER NUMBER] <100 ORDER BY [ZDegPt]", dbOpenDynaset)

Should I have a loop? Wel, basically the form I am running this code from has the option of 2 checkboxes - one is 60 degrees and one is 32 degrees. Each point to a different value based on the gas in each record. However, once the degree is selected I will just be looking at the value associated with the degree selected. For instace, Nitrogen has one dew point value for 60 degrees and another for 32 degrees. So, once I know which option has been selected on the screen I can focus in on just one field to retrieve the dew point value. There is more than one gas to a batch, though, and in orer to calculate properly, I must sort the gases by the gas with the smallest dew point first and then begin calc' on that gas and proceed to the next smallest gas. As you can see from the code above, I ORDER BY [ZDegPt]. Actually, the code resides within an IF statement that picks the ORDER BY based on the dew point option picked on the form.
DO I go overboard on the reply?

Fred

 

[KDg]

okay, i'm now very confused about what you're trying to do. The first code section had a loop and looked as though you were using a recordset independently of any form, now you're using each record seperately?

I'm trying to see how you will get a running total/average from one record. Are you going through a form and building the array from records that you select? Is the form you are using based on a bound recordset and then you are creating another in code? When you say that you refer to the recordset elsewhere in code, if you move off the first record then that will explain why you are no longer starting at the first record in your code. If you can clarify what you're trying to achieve i'll try again,

Cheers

Drew

 

[fredfortsonsr]

Thanks, Drew. I finally worked through it last night. If you would like a copy of the code and to see how I did it I will be glad to forward it to you and provide answers to your questions. Believe me, my struggle was greatly simplified by the input of you and a couple of others.

Fred