none
Date search across tables RRS feed

  • Question

  • Hi

    Is it possible to list for all rows in all tables that have one or more columns with a date value less than January 1, 1753? All tables have an ID numeric key column.

    Thanks

    Regards

    Thursday, June 9, 2016 12:55 PM

Answers

  • Hi

    I have come up with below code and it seems to work.

    Thanks

    Regards

    Public Const CheckInvalidDate = -1
    Public Const BackEndDB = "Z:\MyData.mdb"
    
    Sub UpszingCehckAll()
    Dim Tbl As DAO.TableDef
    
      For Each Tbl In CurrentDb.TableDefs
        If (Tbl.Attributes And dbSystemObject) = 0 Then
          Call UpsizingCheck(Tbl.Name, Tbl.SourceTableName)
        End If
      Next
      
      Debug.Print ""
      Debug.Print "*** End processing all tables."
      
    End Sub
    
    Sub UpsizingCheck(Tbl As String, TblSrc As String)
    Dim rs As Recordset
    Dim x
    Dim I As Long
    Dim j As Long
    Dim JSt As String
    Dim St As String
    Dim St1 As String
    
      Set rs = CurrentDb.OpenRecordset(Tbl)
      
      If (rs.RecordCount = 0) Or (rs.EOF) Or (rs.BOF) Then Exit Sub
      
      rs.MoveLast
      
      While Not rs.BOF
        For I = 0 To rs.Fields.Count - 1
          If CheckInvalidDate = True Then
            'If its a date field and is less than 1/1/1753
            If (rs.Fields(I).Type = dbDate) Then
              If rs.Fields(I).Value < #1/1/1753# Then
                St1 = ""
                St1 = St1 & " Field: '" & rs.Fields(I).Name & "' " & rs.Fields(I).Value & " too low and possibly invalid"
                Print #1, St1
                Debug.Print St1
              End If
            End If
          End If
        Next
      Wend
      
      'Debug.Print "End processing table " & Tbl & " ..."
    
    End Sub

    Friday, June 10, 2016 3:10 AM

All replies

  • Access tables cannot store dates prior to 1/1/1900. Are you using another back-end?

    And tell us more about those tables. It is very unusual to have the same data spread out over multiple tables.


    -Tom. Microsoft Access MVP

    Thursday, June 9, 2016 1:22 PM
  • Hi Tom

    I need to list dates like below.

    Thanks

    Regards

    Thursday, June 9, 2016 2:13 PM
  • Access tables cannot store dates prior to 1/1/1900. Are you using another back-end?

    And tell us more about those tables. It is very unusual to have the same data spread out over multiple tables.


    -Tom. Microsoft Access MVP

    Hi Tom,

    You must have a very special Access, since mine (A2003) CAN store dates as way back as I need.

    Imb.

    Thursday, June 9, 2016 3:30 PM
  • Access dates post-12/31/1899 dates are stored as dates (which are numbers,
    with 1 = Jan 1, 1900, 38614 = Sep 19, 2005). "Dates" of 12/31/1899 and earlier
    are stored in Access as text. This means you have two different types of data in this
    column.

    Best regards, George

    Thursday, June 9, 2016 3:43 PM
  • Access dates post-12/31/1899 dates are stored as dates (which are numbers,
    with 1 = Jan 1, 1900, 38614 = Sep 19, 2005). "Dates" of 12/31/1899 and earlier
    are stored in Access as text. This means you have two different types of data in this
    column.

    Best regards, George

    Hi George,

    In my opinion dates before 1900 are stored as negative numbers. You can simply do the test with:   

        days = Cdbl(Cdate("1-jan-1500"))

    It returns  -146095

    Imb.

    Thursday, June 9, 2016 4:16 PM
  • Valid dates using Access DateTime datatype can range from 1/1/100 to 12/31/9999.

    -Bruce


    Thursday, June 9, 2016 4:52 PM
  • You may well be correct, and indeed this works in the Immediate window:
    ?DateSerial(1753,1,1)
    1/1/1753

    The CDbl of this value is negative, as one would expect.

    I was referring to entering a value in a date field in a form. It produces an error for anything older than 1/1/1900.


    -Tom. Microsoft Access MVP

    Friday, June 10, 2016 2:11 AM
  • Hi Y a h y a,

    you want to display date before January 1, 1753.

    as our community members had mentioned in their suggestions you will get different different results when you try to store date value before year 1900.

    so first its important that can you able to store date values correctly in your table?

    so that we can move further to think about display it.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 10, 2016 2:36 AM
    Moderator
  • Hi

    I have come up with below code and it seems to work.

    Thanks

    Regards

    Public Const CheckInvalidDate = -1
    Public Const BackEndDB = "Z:\MyData.mdb"
    
    Sub UpszingCehckAll()
    Dim Tbl As DAO.TableDef
    
      For Each Tbl In CurrentDb.TableDefs
        If (Tbl.Attributes And dbSystemObject) = 0 Then
          Call UpsizingCheck(Tbl.Name, Tbl.SourceTableName)
        End If
      Next
      
      Debug.Print ""
      Debug.Print "*** End processing all tables."
      
    End Sub
    
    Sub UpsizingCheck(Tbl As String, TblSrc As String)
    Dim rs As Recordset
    Dim x
    Dim I As Long
    Dim j As Long
    Dim JSt As String
    Dim St As String
    Dim St1 As String
    
      Set rs = CurrentDb.OpenRecordset(Tbl)
      
      If (rs.RecordCount = 0) Or (rs.EOF) Or (rs.BOF) Then Exit Sub
      
      rs.MoveLast
      
      While Not rs.BOF
        For I = 0 To rs.Fields.Count - 1
          If CheckInvalidDate = True Then
            'If its a date field and is less than 1/1/1753
            If (rs.Fields(I).Type = dbDate) Then
              If rs.Fields(I).Value < #1/1/1753# Then
                St1 = ""
                St1 = St1 & " Field: '" & rs.Fields(I).Name & "' " & rs.Fields(I).Value & " too low and possibly invalid"
                Print #1, St1
                Debug.Print St1
              End If
            End If
          End If
        Next
      Wend
      
      'Debug.Print "End processing table " & Tbl & " ..."
    
    End Sub

    Friday, June 10, 2016 3:10 AM
  • I was referring to entering a value in a date field in a form. It produces an error for anything older than 1/1/1900.

    Hi Tom,

    I have two pretty large historical/genealogical applications, but I have no problems with any historical dates. Only when the year is less than 100, Access automatically extends it to the current or previous century, e.g. 1-jan-50 will be converted to 1-jan-1950.

    Do you have any limitations in your forms or in the routines behind your forms?

    Even my calender form behaves normally (except before the year 100, but I will investigate that).

    Imb.

    Friday, June 10, 2016 5:57 AM
  • Hi Yahya,

    its good to hear from you that you got the solution for your issue by yourself and thanks for updating the status of this thread.

    Thanks for sharing the solution with our community so that if any other member have same issue like you can also get solution by your post.

    Regards

    Deepak


    We are trying to better understand customer views on social support experience, so your participation in this interview project would be greatly appreciated if you have time. Thanks for helping make community forums a great place.
    Click HERE to participate the survey.

    Friday, June 10, 2016 9:03 AM
    Moderator
  • Ah, I thought you needed to know the rows in which these values occur based on your statement implying that your tables all had primary keys. It doesn't look like your code helps you with that, but as long as you have what you need...

    It looks like you are trying to upsize your database to SQL Server.  I would recommend simply setting up a test instance of SQL Express and using the MS SQL Server Migration Assistant for Access (both available as free downloads) to make the attempt to migrate your data as-is.  SSMA will certainly let you know if you have date values that cannot be shoehorned into SQL DATETIME datatype.

    -Bruce

    Friday, June 10, 2016 2:52 PM
  • Hi Bruce

    It would have been good but once these values are highlighted I can now search and find those rows. I can then fix them beforehand to avoid any issue.

    Regards


    • Edited by Y a h y a Friday, June 10, 2016 4:45 PM
    Friday, June 10, 2016 4:44 PM