Solved Formatting a field on a form

[camerontaylor]

I have a subform in datasheet view on my main form. The table that I have stores dates as UNIX timestamps. I then have a query, which takes the UNIX timestamps and converts them into readable dates. There are 2 number fields on the table: UNIXDateCreated and UNIXDateDeprecated. On the query, these fields are echoed as [Date Created] and [Date Deprecated]. My issue is with the formatting on Date Deprecated.

I have this SQL behind the query:

SELECT "SME-" & Format(tblArtifacts.ArtifactNumber,"00000") AS [SME-Number], tblArtifacts.ArtifactName AS [Artifact Name], tblArtifacts.ArtifactStatus AS [Artifact Status], tblArtifacts.ArtifactCreator AS Creator, tblArtifacts.ArtifactDescription AS [Artifact Description], Format(DateFromUNIXTime(tblArtifacts.UNIXDateCreated),"yyyy-mm-dd") AS [Date Created], IIf(DateFromUNIXTime(tblArtifacts.UNIXDateDeprecated) = #1/1/1970#, "N/A", Format(DateFromUNIXTime(tblArtifacts.UNIXDateDeprecated), "yyyy-mm-dd")) AS [Date Deprecated]
FROM tblArtifacts;

The relevant part of this select statement has been highlighted. I have some VBA behind a "Create Artifact" button which sets the date deprecated = UNIX start date (1970-01-01). This is going off of the knowledge that we would never have an artifact deprecated more than 10 years ago, so it is a safe date to use.

The query formats the Date Deprecated correctly, this being N/A if the date registered is Jan 1, 1970, or the actual date deprecated if not. This formatting is shown correctly on the query. However, it is not shown correctly on my datasheet subform. It shows as 1970-01-01, which is not what I want. Is there a way to take the EXACT formatting from the query over to the subform, or a way to format the subform such that the data will take the same format as I have in the query?

Here is the function DateFromUNIXTime:

Public Function DateFromUNIXTime(UNIXTime As Long) As Date
    If IsNull(UNIXTime) Then
        DateFromUNIXTime = UNIXTimeZero
    Else
        DateFromUNIXTime = DateAdd("s", UNIXTime, UNIXTimeZero)
    End If
End Function

Any help is greatly appreciated!

 

[camerontaylor]

Nevermind, I seem to have fixed the problem! I had to delete and replace the subform, but now the formatting is carrying over!