Date column now displaying odd numbers instead of dates (1 Viewer)

vent

Registered User.
Local time
Today, 05:25
Joined
May 5, 2017
Messages
160
Hi everyone

So I'm back from a brief hiatus and upon opening access, I noticed something very odd. I have a datasheet in split-form view and it's data is based off a query which extracts a number of columns from the main table and one of the date fields called InsuranceExpiryDate is displaying odd numbers (e.g. -288175) instead of a date (e.g. 1/2/2012) whenever this is opened in datasheet view. Before leaving, I included a button for reports that would filter on the two date fields (InsuranceExpiryDate and AAFinalDate) and a check box for a yes/no field. Initially, I was trying to get the report to filter between two date ranges and whether the user checked the box for WSIB. For the most part I believe this has worked. Here's the VBA:

Code:
Option Compare Database
Option Explicit


Private Sub cmdAAReport_Click()

Dim strCriteria As String

If Not IsNull(Me.txtStartDate) Then
        strCriteria = " And AAFinalDate >= #" & _
            Format(Me.txtStartDate, "yyyy-mm-dd") & "#"
    End If
    
    If Not IsNull(Me.txtEndDate) Then
        strCriteria = strCriteria & " And AAFinalDate < #" & _
            Format(Me.txtEndDate + 1, "yyyy-mm-dd") & "#"
    End If
    
    ' remove leading ' And '
    strCriteria = Mid(strCriteria, 6)
    
    ' open report
    DoCmd.OpenReport "rptCriteriaAA", _
        View:=acViewPreview, _
        WhereCondition:=strCriteria


End Sub

Private Sub cmdreportIE_Click()

   Dim strCriteria As String
    
    If Not IsNull(Me.txtStartDate) Then
        strCriteria = " And InsuranceExpiryDate >= #" & _
            Format(Me.txtStartDate2, "yyyy-mm-dd") & "#"
    End If
    
    If Not IsNull(Me.txtEndDate) Then
        strCriteria = strCriteria & " And InsuranceExpiryDate < #" & _
            Format(Me.txtEndDate2 + 1, "yyyy-mm-dd") & "#"
    End If
    
    ' remove leading ' And '
    strCriteria = Mid(strCriteria, 6)
    
    ' open report
    DoCmd.OpenReport "rptCriteriaAA", _
        View:=acViewPreview, _
        WhereCondition:=strCriteria



End Sub

However, I think the problem might be somewhere here because the dates were displayed just fine prior. If anyone has any suggestions or possible solutions, that would be much appreciated!
 

Attachments

  • datasheetview.PNG
    datasheetview.PNG
    8.7 KB · Views: 38
  • insurancedateQUERYPNG.PNG
    insurancedateQUERYPNG.PNG
    21.9 KB · Views: 36
  • reportform.PNG
    reportform.PNG
    28.8 KB · Views: 41
Last edited:

arnelgp

..forever waiting... waiting for jellybean!
Local time
Today, 17:25
Joined
May 7, 2009
Messages
19,172
Is the column in question is calculated field?
 

vent

Registered User.
Local time
Today, 05:25
Joined
May 5, 2017
Messages
160
Hi arnelgp

It turns out the date column was not in Short Date format, I made this change and the column now displays everything properly. Thank you for your response anyway, I appreciate it!
 

Users who are viewing this thread

Top Bottom