Results 1 to 12 of 12
  1. #1
    joshynaresh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2013
    Posts
    131

    convert AD Date TO BS Date and BS to AD Date

    Hello Sir,

    I copyed this code from other site.
    Code:
    Option Compare Database
    
    '==========================================================
    'created by Anil Shrestha
    'http://blog.anilz.net
    'contact email : me@anilz.net
    '==========================================================
    
    
    
    
    
    
    
    
    Public Starting_Range_AD As Date 'Public variable declration for database starting date in AD i.e. 2000-01-01 is 1943-04-14 AD
    Public BS(91) 'Declaring BS database array variable
    Public Array_Size As Double 'array size variable for array size calcuation and storing
    
    
    Sub BS_Database()
        Starting_Range_AD = "1943-04-14" 'database starting date in AD i.e. 2000-01-01 is 1943-04-14 AD
        
        '========================================================================
        'all the BS database array starts with year and total days for each subsequent 12 months
        BS(0) = Array(2000, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(1) = Array(2001, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(2) = Array(2002, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(3) = Array(2003, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(4) = Array(2004, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(5) = Array(2005, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(6) = Array(2006, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(7) = Array(2007, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(8) = Array(2008, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31)
        BS(9) = Array(2009, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(10) = Array(2010, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(11) = Array(2011, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(12) = Array(2012, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
        BS(13) = Array(2013, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(14) = Array(2014, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(15) = Array(2015, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(16) = Array(2016, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
        BS(17) = Array(2017, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(18) = Array(2018, 31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(19) = Array(2019, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(20) = Array(2020, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(21) = Array(2021, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(22) = Array(2022, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
        BS(23) = Array(2023, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(24) = Array(2024, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(25) = Array(2025, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(26) = Array(2026, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(27) = Array(2027, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(28) = Array(2028, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(29) = Array(2029, 31, 31, 32, 31, 32, 30, 30, 29, 30, 29, 30, 30)
        BS(30) = Array(2030, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(31) = Array(2031, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(32) = Array(2032, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(33) = Array(2033, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(34) = Array(2034, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(35) = Array(2035, 30, 32, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31)
        BS(36) = Array(2036, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(37) = Array(2037, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(38) = Array(2038, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(39) = Array(2039, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
        BS(40) = Array(2040, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(41) = Array(2041, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(42) = Array(2042, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(43) = Array(2043, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
        BS(44) = Array(2044, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(45) = Array(2045, 31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(46) = Array(2046, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(47) = Array(2047, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(48) = Array(2048, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(49) = Array(2049, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
        BS(50) = Array(2050, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(51) = Array(2051, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(52) = Array(2052, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(53) = Array(2053, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
        BS(54) = Array(2054, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(55) = Array(2055, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(56) = Array(2056, 31, 31, 32, 31, 32, 30, 30, 29, 30, 29, 30, 30)
        BS(57) = Array(2057, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(58) = Array(2058, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(59) = Array(2059, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(60) = Array(2060, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(61) = Array(2061, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(62) = Array(2062, 30, 32, 31, 32, 31, 31, 29, 30, 29, 30, 29, 31)
        BS(63) = Array(2063, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(64) = Array(2064, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(65) = Array(2065, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(66) = Array(2066, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31)
        BS(67) = Array(2067, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(68) = Array(2068, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(69) = Array(2069, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(70) = Array(2070, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
        BS(71) = Array(2071, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(72) = Array(2072, 31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(73) = Array(2073, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(74) = Array(2074, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(75) = Array(2075, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(76) = Array(2076, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
        BS(77) = Array(2077, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(78) = Array(2078, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(79) = Array(2079, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(80) = Array(2080, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
        BS(81) = Array(2081, 31, 31, 32, 32, 31, 30, 30, 30, 29, 30, 30, 30)
        BS(82) = Array(2082, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30)
        BS(83) = Array(2083, 31, 31, 32, 31, 31, 30, 30, 30, 29, 30, 30, 30)
        BS(84) = Array(2084, 31, 31, 32, 31, 31, 30, 30, 30, 29, 30, 30, 30)
        BS(85) = Array(2085, 31, 32, 31, 32, 30, 31, 30, 30, 29, 30, 30, 30)
        BS(86) = Array(2086, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30)
        BS(87) = Array(2087, 31, 31, 32, 31, 31, 31, 30, 30, 29, 30, 30, 30)
        BS(88) = Array(2088, 30, 31, 32, 32, 30, 31, 30, 30, 29, 30, 30, 30)
        BS(89) = Array(2089, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30)
        BS(90) = Array(2090, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30)
        BS(91) = Array(2091, 31, 31, 32, 31, 31, 31, 30, 30, 29, 30, 30, 30)
        
        Array_Size = UBound(BS) 'storing the value of array size
    End Sub
    
    
    Function ad2bs(AD As Date) As String 'Function for converting AD to BS
        Call BS_Database 'Calling the database in memory
    
    
        DD_Gap = DateDiff("d", Starting_Range_AD, AD) 'calculating the days difference between entered AD date and starting database AD date
        
        If DD_Gap < 0 Then GoTo Out_Of_Range 'condition for checking whether the entered AD date is befor the starting AD date in the database
        
        DD_total = 0 'creating variable for cummulative days in the database
        
        For i = 0 To Array_Size 'Loop for each year
            For ii = 1 To 12 'Loop for each month
                DD_total = DD_total + BS(i)(ii) 'calculating cummulative days by adding days in each months
                
                If DD_total > DD_Gap Then 'checking whether the cummulative days is greater than DD_Gap variable
                    DD_total = DD_total - BS(i)(ii) 'reversing the last addition of days
                    GoTo Return_Value
                End If
                
            Next
        Next
    
    
    Out_Of_Range:
    
    
        ad2bs = "Error! Out of range..." 'returning error
    
    
    Exit Function
    
    
    Return_Value:
    
    
        ad2bs = BS(i)(0) & "-" & ii & "-" & (DD_Gap - DD_total + 1) 'returning BS value
    
    
    End Function
    
    
    Function bs2ad(BS_Date As String) As String 'Function for converting BS to AD
        Call BS_Database 'Calling the database in memory
        Out_Of_Range = True 'Boolean variable for checking whether entered BS falls within the database range
        
        For BS_Index = 0 To Array_Size 'Finding the BS array index in which the BS year lies
            If BS_Year(BS_Date) = BS(BS_Index)(0) Then 'checking whether BS index for the BS year is found or not
                Out_Of_Range = False
                Exit For
            End If
        Next
        
        If Out_Of_Range = True Then GoTo Out_Of_Range
    
    
        DD_total = 0 'creating variable for cummulative days in the database
        For i = 0 To BS_Index 'loop for each year until the desired BS index year is reached
            For ii = 1 To 12 'loop for each month
                If i = BS_Index And ii = BS_Month(BS_Date) Then 'checking whether we reached the entered BS date year and month
                    bs2ad = DateAdd("d", DD_total + BS_Day(BS_Date) - 1, Starting_Range_AD) 'adding cummulative days+days entered in BS date with starting database AD date
                    
                    If BS_Year(BS_Date) <> BS_Year(ad2bs(DateValue(bs2ad))) Or _
                        BS_Month(BS_Date) <> BS_Month(ad2bs(DateValue(bs2ad))) Or _
                        BS_Day(BS_Date) <> BS_Day(ad2bs(DateValue(bs2ad))) Then 'checking whether reverse conversion is same as the orginal BS date or not
                       
                       GoTo Out_Of_Range
                    End If
                    bs2ad = Year(bs2ad) & "-" & Month(bs2ad) & "-" & Day(bs2ad)
                    Exit Function
                End If
                DD_total = DD_total + BS(i)(ii) 'cummulative days calculation
            Next
        Next
    
    
    Out_Of_Range:
    bs2ad = "Error! Out of range..."
    
    
    End Function
    Function BS_Date_Diff(BS_Date1 As String, BS_Date2 As String) As Double 'Function for differecning two BS dates
    
    
    BS_Date_Diff = DateDiff("d", bs2ad(BS_Date1), bs2ad(BS_Date2))
    
    
    End Function
    
    
    Function BS_Date_Day_Add(BS_Date As String, Days_To_Add As Double) As String 'Function for adding days to BS date
    
    
    BS_Date_Day_Add = ad2bs(DateAdd("d", Days_To_Add, bs2ad(BS_Date)))
    
    
    End Function
    Function BS_Day_Of_Week(BS_Date As String) As Integer 'Function for returning day of week in BS Date
    
    
    BS_Day_Of_Week = Weekday(bs2ad(BS_Date))
    
    
    End Function
    Function BS_Month(BS As String) As Double 'Function for returning BS Month in BS Date
    
    
    For i = 1 To Len(BS) 'creating loop for each character in BS Date
        If IsNumeric(Mid(BS, i, 1)) = False Then 'checking whether year and month separator is reached or not
        
            If IsNumeric(Mid(BS, i + 2, 1)) = False Then 'checking whether there is one or two digit month
                BS_Month = Mid(BS, i + 1, 1) 'returning month value if there is one digit month
                Exit Function
            Else
                BS_Month = Mid(BS, i + 1, 2) 'returning month value if there is two digit month
                Exit Function
            End If
        
        End If
    Next
    
    
    End Function
    
    
    Function BS_Year(BS As String) As Double 'Funcation for returning year of the BS date
    
    
    BS_Year = Mid(BS, 1, 4)
    
    
    End Function
    
    
    Function BS_Day(BS As String) As Double 'Function for returning day of the BS date
    
    
    If IsNumeric(Mid(BS, Len(BS) - 1, 1)) = True Then 'checking whether one or two digit day
        BS_Day = Right(BS, 2) 'returning two digit day
    Else
        BS_Day = Right(BS, 1) 'returning one digit day
    End If
    
    
    End Function

    Where I want to store BS Database into the table Name DateTbl having Fields BS_Year, BS_1,BS_2,BS_3,BS_4,BS_5,BS_6,BS_7,BS_8,BS_9,BS_10 ,BS_11 and BS_12.

    After storing BS Database into the table where I have to change in code? What should I have to write?

    I want to store



    Code:
     'all the BS database array starts with year and total days for each subsequent 12 months    BS(0) = Array(2000, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(1) = Array(2001, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(2) = Array(2002, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(3) = Array(2003, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(4) = Array(2004, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(5) = Array(2005, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(6) = Array(2006, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(7) = Array(2007, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(8) = Array(2008, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31)
        BS(9) = Array(2009, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(10) = Array(2010, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(11) = Array(2011, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(12) = Array(2012, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
        BS(13) = Array(2013, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(14) = Array(2014, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(15) = Array(2015, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(16) = Array(2016, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
        BS(17) = Array(2017, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(18) = Array(2018, 31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(19) = Array(2019, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(20) = Array(2020, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(21) = Array(2021, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(22) = Array(2022, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
        BS(23) = Array(2023, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(24) = Array(2024, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(25) = Array(2025, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(26) = Array(2026, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(27) = Array(2027, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(28) = Array(2028, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(29) = Array(2029, 31, 31, 32, 31, 32, 30, 30, 29, 30, 29, 30, 30)
        BS(30) = Array(2030, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(31) = Array(2031, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(32) = Array(2032, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(33) = Array(2033, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(34) = Array(2034, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(35) = Array(2035, 30, 32, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31)
        BS(36) = Array(2036, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(37) = Array(2037, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(38) = Array(2038, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(39) = Array(2039, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
        BS(40) = Array(2040, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(41) = Array(2041, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(42) = Array(2042, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(43) = Array(2043, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
        BS(44) = Array(2044, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(45) = Array(2045, 31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(46) = Array(2046, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(47) = Array(2047, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(48) = Array(2048, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(49) = Array(2049, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
        BS(50) = Array(2050, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(51) = Array(2051, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(52) = Array(2052, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(53) = Array(2053, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
        BS(54) = Array(2054, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(55) = Array(2055, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(56) = Array(2056, 31, 31, 32, 31, 32, 30, 30, 29, 30, 29, 30, 30)
        BS(57) = Array(2057, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(58) = Array(2058, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(59) = Array(2059, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(60) = Array(2060, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(61) = Array(2061, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(62) = Array(2062, 30, 32, 31, 32, 31, 31, 29, 30, 29, 30, 29, 31)
        BS(63) = Array(2063, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(64) = Array(2064, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(65) = Array(2065, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(66) = Array(2066, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 29, 31)
        BS(67) = Array(2067, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(68) = Array(2068, 31, 31, 32, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(69) = Array(2069, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(70) = Array(2070, 31, 31, 31, 32, 31, 31, 29, 30, 30, 29, 30, 30)
        BS(71) = Array(2071, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(72) = Array(2072, 31, 32, 31, 32, 31, 30, 30, 29, 30, 29, 30, 30)
        BS(73) = Array(2073, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 31)
        BS(74) = Array(2074, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(75) = Array(2075, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(76) = Array(2076, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
        BS(77) = Array(2077, 31, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31)
        BS(78) = Array(2078, 31, 31, 31, 32, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(79) = Array(2079, 31, 31, 32, 31, 31, 31, 30, 29, 30, 29, 30, 30)
        BS(80) = Array(2080, 31, 32, 31, 32, 31, 30, 30, 30, 29, 29, 30, 30)
        BS(81) = Array(2081, 31, 31, 32, 32, 31, 30, 30, 30, 29, 30, 30, 30)
        BS(82) = Array(2082, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30)
        BS(83) = Array(2083, 31, 31, 32, 31, 31, 30, 30, 30, 29, 30, 30, 30)
        BS(84) = Array(2084, 31, 31, 32, 31, 31, 30, 30, 30, 29, 30, 30, 30)
        BS(85) = Array(2085, 31, 32, 31, 32, 30, 31, 30, 30, 29, 30, 30, 30)
        BS(86) = Array(2086, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30)
        BS(87) = Array(2087, 31, 31, 32, 31, 31, 31, 30, 30, 29, 30, 30, 30)
        BS(88) = Array(2088, 30, 31, 32, 32, 30, 31, 30, 30, 29, 30, 30, 30)
        BS(89) = Array(2089, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30)
        BS(90) = Array(2090, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 30, 30)
        BS(91) = Array(2091, 31, 31, 32, 31, 31, 31, 30, 30, 29, 30, 30, 30)
    this section in table.

    thanks in advance

  2. #2
    ranman256's Avatar
    ranman256 is offline VIP
    Windows Vista Access 2010 32bit
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    9,521
    cant you just use: DateDiff("d",date1,date2)

  3. #3
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I presume by BS you are talking about the Bikram Sambat calendar?

    I would store your data in a one field table and store the whole year as a single string e.g.

    "2000, 30, 32, 31, 32, 31, 30, 30, 30, 29, 30, 29, 31"

    then to populate the array, something like

    Code:
    dim rs as dao.recordset
    dim I as integer
    
    set rs=currentdb.openrecordset("SELECT yearArray FROM tblBSCal ORDER BY yearArray")
    I=0
    while not rs.EOF
        BS(I)=split(rs!yearArray,", ")
        I=I+1
        rs.movenext
    wend

  4. #4
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    Don't understand why you would want to use a table - the array would be faster.
    I took some time to search what BS meant. Would be nice if you explain terms in your first post......


    I only converted the code for "Function ad2bs()". You should be able to fix the other functions......



    Good luck.............
    Attached Files Attached Files

  5. #5
    joshynaresh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2013
    Posts
    131
    Thanks to Ajex and ssanfu. both code is working properly. But it occur error and goes to debug screen when i try to convert date which is not in database(BS2AD function). I want to solve this problem by getting result ""Error! Out of range..." i.e. date: 2091-12-31, but it work when input is 2092-01-01. I also want to make this date database flexible. I mean to say, when I enter date for year 2092 in table, my function should start converting year 2092 also. this function do not convert date of year 2092 even entring year 2092 data in table.

    I tried this funcition my changing "Public BS(91)" to "Public BS(92)" then it convert year 2092 date. so I want to change 91 to 92, 93, 94 so on as row count increases. Can it be possible.

  6. #6
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    .
    so I want to change 91 to 92, 93, 94 so on as row count increases. Can it be possible.
    the code I provided to load the data to the array should keep going. What is causing the issue is your declaring of BS which limits to 91 elements. Either change the 91 to a higher number (but not so high you use all available memory) or remove the 91 and include code to increase the dimension as required using redim

    do restate your array declaration
    Public BS()

    and include

    redim BS(I+1)

    as the first line of the loop

    Not tested but should be along those lines

  7. #7
    joshynaresh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2013
    Posts
    131
    Dear ajex,

    I'm unable to fix. would you mind to send me samle code.

  8. #8
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    I'll take a look if you send me your db.

  9. #9
    joshynaresh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2013
    Posts
    131
    Thank you ajax. My problem has been solved. Thanks for you help

    I was out so i was unable to reply.

    Now I want to create One New Function from above database.

    I want to find Find Number of Days in Given Year and Month. for i.e, If I enter 2075/11 then result should be 30 because there are 30 days in 11th month of 2075. Same as if i enter 2076/01 then result should be 31 because there are 31 days in 1st month of 2076.

    Thank you in advance.

  10. #10
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    perhaps something like

    myMonth="2075/11"
    noOfDaysinMonth=BS(val(left(myMonth,4))-2000,val(right(myMonth,2)))

  11. #11
    joshynaresh is offline Competent Performer
    Windows XP Access 2007
    Join Date
    Aug 2013
    Posts
    131
    I have used function
    Code:
    Function noOfDaysinMonth(myMonth As String) As Double
    'myMonth = "2075/11"
    noOfDaysinMonth = BS(Val(Left(myMonth, 4)) - 2000, Val(Right(myMonth, 2)))
    End Function
     But error shows as wrong number of dimensions by highlighting "BS"

  12. #12
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    wasn't sure how you had set up your data

    noOfDaysinMonth = split(BS(Val(Left(myMonth, 4)) - 2000), ",")(Val(Right(myMonth, 2)))

Please reply to this thread with any new information or opinions.

Similar Threads

  1. Replies: 3
    Last Post: 10-16-2017, 11:47 AM
  2. Replies: 3
    Last Post: 06-10-2015, 10:12 AM
  3. Replies: 3
    Last Post: 09-11-2013, 09:49 AM
  4. Replies: 3
    Last Post: 08-21-2012, 03:05 PM
  5. Convert text date to date value
    By unslog in forum Access
    Replies: 8
    Last Post: 02-01-2012, 12:22 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums