Calculate SinceDate when having to UntilDate

[capsula4]

I'm trying to create a module that returns a SinceDate when having more than one UntilDate per ID, for example:

REL_ID, UNTIL, fSince
1, 10/10/08, 0
2, 09/10/08, 0
2, 30/10/08, 10/10/08
3, 10/10/08, 0

So far it works okay, the problem is that fSince returns the value that corresponds to the last entry for all of em, in this case, last value is:
3, 10/10/08, 0

So it returns:
REL_ID, UNTIL, fSince
1, 10/10/08, 0
2, 09/10/08, 0
2, 30/10/08, 0
3, 10/10/08, 0

If last value would have been "2, 30/10/08, 10/10/08", would have returned 10/10/08 for all the records. What's wrong with this module? Shall I use some criteria thing in the SQL of it?

Option Compare Database
Option Explicit

Public Function fSince()

Dim strSQL As String
Dim strText As String
Dim datePrev As Long
Dim flag As Integer
Dim IDEM2 As Integer

flag = 0


strSQL = "SELECT REL_ID, HASTA FROM tblAcuerdos GROUP BY REL_ID, HASTA ORDER BY REL_ID, HASTA"


    'Open a Recordset and loop through it to fill the text box txtTest with
    'the faults Repaired >>>reprorted by the customer<<<<
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
    
    flag = 0
    
    Do Until rs.EOF
            If (flag > 0) Then
                If (IDEM2 = rs!REL_ID) Then
                    fSince = datePrev + 1
                Else
                    fSince = 0
                End If
            End If
            datePrev = rs!HASTA
            IDEM2 = rs!REL_ID
            flag = flag + 1
        rs.MoveNext
    Loop
    
    rs.close
    Set rs = Nothing
    Set db = Nothing
    
End Function

 

[ajetrumpet]

Here are some things that you might not be aware of:

Option Compare Database
Option Explicit

Public Function fSince()

Dim strSQL As String
Dim strText As String
Dim datePrev As Long
Dim flag As Integer
Dim IDEM2 As Integer

flag = 0


strSQL = "SELECT REL_ID, HASTA FROM tblAcuerdos GROUP BY REL_ID, HASTA ORDER BY REL_ID, HASTA"


    'Open a Recordset and loop through it to fill the text box txtTest with
    'the faults Repaired >>>reprorted by the customer<<<<
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)

[COLOR="Red"]You just opened the recordset, you didn't navigate to the first record, which provides a point at which to start looping[/COLOR]
  
    flag = 0
    
    Do Until rs.EOF
            If [COLOR="Red"](flag > 0)[/COLOR] Then [COLOR="Red"]<--- you have already set flag = 0 here.  Why check this condition the first time through?[/COLOR]
                If (IDEM2 = rs!REL_ID) Then
                    fSince = [COLOR="Red"]datePrev [/COLOR]+ 1 [COLOR="Red"]<--- same problem here[/COLOR]
                Else
                    fSince = 0
                End If
            End If
            [COLOR="Red"]datePrev = rs!HASTA <---You are setting this value on the current record, and then immediately moving to the next record (you are not using the value in a condition that applies to its own record)[/COLOR]
            IDEM2 = rs!REL_ID
            flag = flag + 1
        rs.MoveNext
    Loop
    
    rs.close
    Set rs = Nothing
    Set db = Nothing
    
End Function

So, what is the outcome again? You are getting all zero's instead of the values you are wanting?

If that's what's happening, it is probably because you never gave the variable called prevDate a value before you started looping. It was just declared an integer, and that's all...

 

[capsula4]

Thanks a lot for the reply tho I'm guessing that what you are telling me isn't the mistake.

What I'm trying to do is to compare if REL_ID field of the current record is the same as REL_ID of the previous record.

So I use the "flag" variable to store the first loop, if it is the first record then I cannot compare it with any previous one.

So I store the first record, datePrev and REL_ID

Then, in the second loop flag=1 so starts the comparation. It checks if REL_ID of current record is the same REL_ID of the previous record. If they are the same, then it makes fSince=datePrev+1 or if dif, fSince=0

 

[Brianwarnock]

Its confusing for simple minds like mine to have a field called HASTA to appear from nowhere, what happened to UNTIL ?

Adam has not followed the logic through, but is he correct that you have to move.first to ensure that you start at the first record?

Brian

 

[capsula4]

Sorry a lot for the confusion Brianwarnock, HASTA is the field I'm using which is the spanish word for UNTIL!!

Okay, so I should add
Move.First

Before starting the loop, tho... would this solve my problem?
I find weird the fact that fSince() returns the value of the last entry only to all the records, seems like it assigns the value of the last loop to fSince() for all the records. I'm pretty new to VBA, I could make that VBA by using others' examples.

 

[Brianwarnock]

Sorry for my ignorance, after all my Spanish Holidays you would think the penny would drop, ah well Hasta la vista!

I don't know whether it is an issue, I would have programmed it by move.first, set up my base values and then gone into the loop, but assuming it starts at the 1st your logic seems fine.
How do you know it takes the last value and not the first , in your example both are the same?

Brian

 

[ajetrumpet]

Thanks for the confidence in me Brian, ya jerk.

Hey capsula, I just noticed something here that may be relevant. Take a look at the code notations now:

So it returns:
REL_ID, UNTIL, fSince
1, 10/10/08, 0
2, 09/10/08, 0
2, 30/10/08, 0
3, 10/10/08, 0

This is not a surprise. Actually, what I've noticed is that you've got 1 function, returning 1 value. So, when your function (fSince) is assigned a value from the loop, the next time through, that value is replaced with the current loop's assigned value. Thus, you are going to end up assigning 1 value to all of the records. That's probably why you see all zero's when you're supposed to be seeing something else. I wonder, if by chance, that is the problem here. Logically (just for you Brian! ), it makes sense.

See these notes:

Option Compare Database
Option Explicit

Public Function fSince()

Dim strSQL As String
Dim strText As String
Dim datePrev As Long
Dim flag As Integer
Dim IDEM2 As Integer

flag = 0


strSQL = "SELECT REL_ID, HASTA FROM tblAcuerdos GROUP BY REL_ID, HASTA ORDER BY REL_ID, HASTA"


    'Open a Recordset and loop through it to fill the text box txtTest with
    'the faults Repaired >>>reprorted by the customer<<<<
Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenForwardOnly)
    
    flag = 0
    
    Do Until rs.EOF
            If (flag > 0) Then
                If (IDEM2 = rs!REL_ID) Then
                    fSince = datePrev + 1 [COLOR="Red"]on the last loop through, the function's value takes whatever [B][U]this expression[/U][/B] gives it.
[indent][indent][indent]None of the other loops matter, and those previous values ("fSince" values) will have been lost.[/indent][/indent][/indent][/COLOR]
                Else
                    fSince = 0 [COLOR="Red"]the function is assigned a value of '0' the first time through.[/COLOR]
                End If
            End If
            datePrev = rs!HASTA
            IDEM2 = rs!REL_ID
            flag = flag + 1
        rs.MoveNext
    Loop
    
    rs.close
    Set rs = Nothing
    Set db = Nothing
    
End Function

 

[Brianwarnock]

Game set and match to Adam.
This was a case of not seeing the wood for the trees, we were so busy with the internal logic of the function we ignored whether the logic of how it was being used was correct.
I was right tho' it was the last value that was propagated.

Brian

 

[Brianwarnock]

I just realised neither of us suggested a solution, I'm no expert at file handling in VBA but I think you need to lookup the Update method. There is an example using the Northwind DB in VBA help.

Brian

 

[capsula4]

I just realised neither of us suggested a solution, I'm no expert at file handling in VBA but I think you need to lookup the Update method. There is an example using the Northwind DB in VBA help.

Brian

Thanks Brian and Adam! I also think that's the problem but I don't know how to solve it. What rings a bell to me is that once I "made" another function (personalized one I found there) and it was one function but returned dif values for each record. It didn't use any array or weird thing, except a WHERE sql condition in the SQL of the module. I will give a try on my own and see what can I do.

( this is the other module I'm speakin about: http://www.access-programmers.co.uk/forums/showthread.php?t=142343 )

 

[ajetrumpet]

The bottom line here is: You need a way to store (or pass) each value that results from the conditional loop sequence. Do that in a way that compliments the end result, weather it be with an array, or through some other process. Good luck with it...

 

[Brianwarnock]

Had a bit of time on my hands and was curious to see if I could do this so knocked up the attached.
It has poor naming conventions and I don't like the way I had to handle EOF but it met the first commandment of real world programming, it worked.

I've no doubt that you will have done a better job, but I'll post just incase it helps.

Brian

 

[ajetrumpet]

so knocked up the attached.

You knocked up the database? When is it due? Are they twins?

 

[Brianwarnock]

probably put down at birth.

Brian

 

[capsula4]

Thanks a Lot Brian!

I liked how you made it, actually If I understood okay what you make is store the value in a database field through a form instead of assigning a value to the function?

I guess I'm gonna go that way

 

[Brianwarnock]

I wrote a Sub not a Function and worked directly on the file running the sub from a command button on the form.
The file must be sorted correctly before hand and I wondered a bit about how to ensure that, but I suspect that there are several ways.

Brian

Just checked, easiest way is to write a query to do sort and change the Set rst to pick up the query

 

[capsula4]

Thanks a lot Brian!!

I could make it!

Here is the code I used, there are some lil differences with ur code, since I'm using a SQL query instead of opening the whole file/table.

Some lil spanish for ur help
Desde = Since
Hasta = Until

Private Sub CalcularDesde_Click()
Dim strSQL As String
Dim datePrev As Long
Dim intIdem As Integer

strSQL = "SELECT REL_ID, HASTA, DESDE, ID_ACUERDO FROM tblAcuerdos ORDER BY REL_ID, HASTA"

Dim db As DAO.Database
Dim rs As DAO.Recordset
Set db = CurrentDb
Set rs = db.OpenRecordset(strSQL, dbOpenDynaset)

rs.MoveFirst
intIdem = rs!REL_ID
datePrev = rs!HASTA
rs.Edit
rs!DESDE = 0
rs.Update
MsgBox "Fechas 'Desde' Actualizadas"

Do
rs.MoveNext
    If rs.EOF Then
    GoTo Endofrst
    End If

    If (intIdem = rs!REL_ID) Then
        rs.Edit
        rs!DESDE = datePrev + 1
        rs.Update
    Else
        rs.Edit
        rs!DESDE = 0
        rs.Update
    End If
    datePrev = rs!HASTA
    intIdem = rs!REL_ID
Loop

Endofrst:
rs.close
Set rs = Nothing
Set db = Nothing

DoCmd.DoMenuItem acFormBar, acRecordsMenu, 5, , acMenuVer70 // Updates the form info

End Sub

 

[Brianwarnock]

Thanks for the feedback, I'm happy we got there.

Brian

 

[ajetrumpet]

Congrats Capsula.