Adding 1 day to existing database date field

borderraux

Forums

Hi all,
I need to create additional fields (in existing database) where I add 1 day (1st new field),2 days( 2nd new field) and so on to the date in my reference field (lets call it REGISTARE DATE) so the result will be as per below:
REGISTER DATE           REGISTER DATE +1 DAY        REGISTER DAY + 2DAYS
20/05/2017                       21/05/2017                                    22/05/2017
Is there any easy way of doing it?
I am joining 2 databases based on 2 parameters that are the same in both databases: DATE and VALUE but for the second database I want to check if VALUE x is entered for the DATE (1) and DATE + 1DAY (2) and DATE +2DAYS (3) as it may happen in a real world.
I want to use the newly created fields in the bespoke  join function I created:
Function JoinDatabases(TargetDb As Database,TaskName As String, File_to_Join As String, MatchType As String)
Dim JoinTask As Task
Dim dbName As String
Dim FinalMatchType As String
          
Set JoinTask = TargetDb.JoinDatabase
JoinTask.FileToJoin File_to_Join
JoinTask.IncludeAllPFields
JoinTask.IncludeAllSFields
 
JoinTask.AddMatchKey "" & UserSelection(1) & "", "" & UserSelection(3) & "", "A"
JoinTask.AddMatchKey "" & UserSelection(2) & "", "REFERENCE_OF_" & "" & UserSelection(4) & "" , "A"
JoinTask.CreateVirtualDatabase = False
dbName = TaskName & ".IMD"
 
If MatchType = "A" Then
                                        FinalMatchType = WI_JOIN_MATCH_ONLY
ElseIf MatchType = "B" Then
                                        FinalMatchType = WI_JOIN_ALL_IN_PRIM
ElseIf MatchType = "C"  Then
                                        FinalMatchType = WI_JOIN_ALL_REC
ElseIf MatchType = "D"  Then
                                        FinalMatchType=WI_JOIN_NOC_SEC_MATCH 
Else                                       
                                        FinalMatchType=WI_JOIN_NOC_PRI_MATCH  
                                        
End If
                                        
JoinTask.PerformTask dbName, "", FinalMatchType
Set JoinTask = Nothing
Set TargetDb = Nothing
Client.OpenDatabase (dbName)
 
End Function
 
 where in line JoinTask.AddMatchKey "" & UserSelection(1) & "", "" & UserSelection(3) & "", "A" 
   UserSelection (1) is my REGISTER DATE and UserSelection(3) will be replaced with REGISTER DATE +1 DAY  date and so on:
JoinTask.AddMatchKey "" & UserSelection(1) & "", REGISTER DATE +1DAY, "A"
JoinTask.AddMatchKey "" & UserSelection(1) & "", REGISTER DATE +2DAYs, "A"
 
 
Thank you in advance for any help (i can upload my whole code if it makes easier to show what i am trying to achieve)
 
 

Steven Luciani Mon, 10/29/2018 - 16:01

I can't comment on the code but the equations you need to create the additional fields are:

Add one day

@DaysToD(@dtodays(register_date)+1)

add two days

@DaysToD(@dtodays(register_date)+2)

The fields you create can be virtual date or date fields.

Hope that helps.

Steven

borderraux Mon, 10/29/2018 - 19:15

Fantastic. This is exactly the result I was looking for. Many thanks
I wonder if similar result is possible to achieve with the use of Recordset object and simple loop for each record in the database. The process would look as per below:
-append field ("NEW FIELD PLUS 1 DAY") with editable date that essentially takes the date value from my REGISTER DATE field
- then perform loop for each row in recordset and increase the date from the REGISTER DATE field by 1 day 
I tried to write some code for it (below) but it fails:
 
Option Base 1
 
 
Sub Main
 
Dim List_of_Records() As String
Dim table As Object
Dim FieldsinSourceFile As Integer
Dim TargetDb As Object
Dim ActiveDatabase As String
 
 
                              'Set the database and recordset
                                Set TargetDb = Client.CurrentDatabase
                                ActiveDatabase = Client.CurrentDatabase.Name
                                Set rs = TargetDb.RecordSet
                                
                                RecordsinSourceFile = rs.Count
                                                                                       
                                ReDim List_of_Records(RecordsinSourceFile)
                                
                                    
 
                                    For i = 1 To (RecordsinSourceFile)
              rs.GetAt(i)
              Set CurrentRec = rs.ActiveRecord                   
               List_of_Records(i) =  CurrentRec.GetDATEValue("REGISTER_DATE")
                                              
                                              NewDate = List_of_Records(i)+1
                                              
                                              CurrentRec.SetDATEValue("NEW_FIELD_1_DAY") = NewDate
 
                                              
                        Next i
 
 
End Sub

Brian Element Tue, 10/30/2018 - 07:46

Hi Tom, I am on the road right now giving some training but I will try and reply back to you tonight on this as it is doable.

Brian

borderraux Tue, 10/30/2018 - 12:54

Thank you Brian (my name is Paul :-))
What i am trying to achieve is to compare 2 databases where 2 common parameters in both databases is DATE and VALUE. Essentially for each record in DATE field (in first database) i want to pick up that date and corresponding value from the VALUE field and find if the same combination of DATE/VALUE exists in the second database (this is CHECK1) - thats the easy part. 
Now i want to create a permutation where I am adding to my initial DATE value from 1st database additional 1 day (and keeping the same value from VALUE field) and find if that combination exists in the 2nd database (CHECK 2)  - this is because the payment for VALUE can appear in the second database with 1,2 or 3 days of delay.
Then I need to perform CHECK3 and CHECK4 which is essentially +2 days and +3 days added to the initial DATE.
Below may make it easier to understand:
   1st database                                                                                                                2nd database
 DATE                       VALUE                                                                                     DATE                    VALUE
CHECK1 
  20/05/2017            200                                                                                          20/05/2017          150
  CHECK2 
  21/05/2017             200                                                                                          21/05/2017           200
 CHECK3 
  22/05/2017             200                                                                                          22/05/2017           55
 
 In the above example the programme should pick up line for CHECK2.
  I uploaded my code so far where  I am struggling with final loop (commented section).

Brian Element Mon, 11/05/2018 - 07:17

Hi Paul, sorry about getting your name wrong and sorry for not getting back to you sooner.  I have been thinking about this and I think the easiest way is intead of trying to do this by script and using the RecordSet to do it, instead in IDEA create virtual fields for each of the dates as Steve showed above.  Then do a join on each permutation and finally append all the files together.  I think this is much easier to do then doing this directly by script.

Generally I try to do as much as I can using IDEA native commands as they are faster then doing it in a script.  If this is something you will do often then you could script the creation of the virtual fields, the joins and the final append.

borderraux Mon, 02/11/2019 - 07:47

Thanks Brian,
I tried to do it programmatically but after a couple of hours had to stop due to time pressure to finish my project I resorted to appending additional fields with increased/decreased by required number of days.
I attached my code (the section discussed in this topic can be found in the code part: '3 DAYS GAP FROM A GIVEN DATE).
Hope it helps others with similar issue.