Variables in DLookup Not Working!!!!!! 1

[dhshaikh]

Hello,

I'm pulling all my hair out on this one... and help will be greatly appreciated!

I have an access 97 database and i want to look through a table of values and sum up those values that meet my criteria.

the table, NTable2, has the following values:

Nnumb NVal Incrementor
13 1 1
14 2 1
15 3 2
16 4 2

The code I am using is pasted below:

Private Sub Command27_Click()
Dim Alpha, Beta, Gamma, Delta As Integer
Dim SubTotal, TheTotal, GrandTotal As Integer

Alpha = 1
Beta = 3
Gamma = 13
Delta = 15
TheTotal = 0
GrandTotal = 0

Do Until Alpha = Beta + 1

    While Gamma < Delta + 1
    If IsNull(SubTotal = DLookup("[Nval]", "Ntable2", "[nnumb] = " & [Gamma] & "and" & "[incrementor]=" & [Alpha])) = True Then
    SubTotal = 0
    Else: SubTotal = DLookup("[Nval]", "Ntable2", "[nnumb] = " & [Gamma] & "and" & "[incrementor]=" & [Alpha])
    End If
    TheTotal = TheTotal + SubTotal
    Gamma = Gamma + 1
    Wend
    
GrandTotal = GrandTotal + TheTotal
    
Alpha = Alpha + 1
Loop

MsgBox (GrandTotal)
End Sub

I am expecting a result of 6, 1+2+3 from NVal, hoever the best I can get is the code adding 1 and 2, then multiplying the sum by 3 (giving a result of 9).

It seems to count the values where 'Incrementor' is 1, and then multiplies that by the number of loops till alpha=beta + 1 (i.e. 3).

Is my looping wrong, or is it where I place my code or is Dlookup the wrong function? Maybe I need to use an array?

P.S. the isnull and if statement is there to handle null values which were otherwise causing the code to fail.

I have been stuck on this for 3 days now and cant see what I'm missing.

Sorry if i'm not too clear, its difficult to explain. Thank you.
danial

 

[fabby1]

Hi

Could it be that your not resetting Alpha on each loop, Alpha always seems to have the value 1

Phil

 

[dhshaikh]

hi Phil,

What do you mean by resetting Alpha? I am incrementing it at the end of the loop so shouldn't it take the next value of Alpha as the value? Could it be a problem with the way the loop is nested?

thanks for the reply!

regards,
danial

 

[PHV]

Try to replace this:
If IsNull(SubTotal = DLookup("[Nval]", "Ntable2", "[nnumb] = " & [Gamma] & "and" & "[incrementor]=" & [Alpha])) = True Then
SubTotal = 0
Else: SubTotal = DLookup("[Nval]", "Ntable2", "[nnumb] = " & [Gamma] & "and" & "[incrementor]=" & [Alpha])
End If
By this:
SubTotal = Nz(DLookup("[Nval]", "Ntable2", "[nnumb]=" & [Gamma] & " and " & "[incrementor]=" & [Alpha]), 0)

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ222-2244

 

[dhshaikh]

PHV, thanks for the Nz statement...

My result, however, is still coming as 9!!! I think there is a problem with my loops or totalling... I just don't know what!

Thanks once again.
danial

 

[PJStephenson]

Any reason why you are not using DSum? would this give you the same results:

answer = nz(DSum("[Nval]", "Ntable2", "[nnumb] >= " & [Gamma] & " and " & "[nnumb] <= " & [Delta] & " and " & "[incrementor] >=" & [Alpha]& " and " & "[incrementor] <=" & [Beta]),0)

HTH
Peter

 

[dhshaikh]

Using the code you provided for DSum is giving me 14. Its seems to be amove in the right direction!

could me totalling be wrong!?

Thanks for the help PJStephenson!

danial

 

[PJStephenson]

If you use my dsum example on the example table shown your answer will be 6 as it will sum these rows:
Nnumb NVal Incrementor
13 1 1
14 2 1
15 3 2
You don't need to loop this, merely specify the max and min Incrementors and max and min Nnumbs and it will give you the sum of NVal.
Create a query with the same criteria and see what results you get.
HTH
Peter

 

[CajunCenturion]

It seems that the value of Gamma is being incremented inside the inner loop, but never reset to its original value. Therefore, after the initial iteration of the outer Alpha loop, the inner Gamma loop will no longer be executed because without Gamma being reset, it will never be less than Delta + 1.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[PJStephenson]

You are right about the loops Cajun, it looks like dhshaikh is trying to do an over-complicated DSum. If he wants to continue with his loops though I reccomend:

Private Sub Command27_Click()
Dim Alpha, Beta, Gamma, Delta ,intLoop1, intLoop2 ,GrandTotal 

Alpha = 1
Beta = 3
Gamma = 13
Delta = 15
GrandTotal = 0

For intLoop1 = Alpha to Beta 
    For intLoop2 = Gamma to Delta
        GrandTotal = GrandTotal + Nz(DLookup("[Nval]", "Ntable2", "[nnumb]=" & intLoop2 & " and " & "[incrementor]=" & intLoop1), 0) 
    Next
Next

MsgBox (GrandTotal)
End Sub

Peter

 

[CajunCenturion]

I agree PJStephenson, but my take on the forums is that it is far better in the long run to point out the errors, so that the poster may make the corrections and learn from the mistakes, than to simply fix their code for them.

I also fully agree that it is good to point out alternative approaches, such as using DSum, when warranted, as is true in this case.

Sometimes providing the code is necessary because of the nature of the problem, but most often, I think the entire community is better off when we help them do their job, rather than do their job for them. Just my opinion.

Good Luck
--------------
To get the most from your Tek-Tips experience, please read FAQ181-2886
As a circle of light increases so does the circumference of darkness around it. - Albert Einstein

 

[dhshaikh]

WOW!

You've done it in one line of code... I'm very grateful!

I couldn't get this working for so long and it was very difficult to get help on this... thanks PJ.

Definetly deserves more then the one star I'm giving it!

Dhshaikh