Formatting Dates

[magmike]

Using this code:

=IIf(IsNull([nCreateDate]),"",Format([nCreateDate],"m/d/yy (dddd)") &
" at " & Format([nCreateTime],"h:nn ampm"))

I label a note record for a company which looks something like this:

5/22/08 (Thursday) at 10:32 AM

I have been trying to make it look like this if the dates are recent:

12/24/08 (Yesterday) at 6:14 PM

or

12/16/08 (Last Tuesday) at 8:41 AM

But am striking out. Does anyone have experience with this?

Thanks in advance!
magmike

 

[Arvin Meyer [MVP]]

There is nothing built in either Access or the VBA language to do this, but
JET databases can run custom VBA functions, so you may want to write one to
handle it. If you need help, post back with details of how many days you
want to include, and what to include between the parenthesis.

 

[John W. Vinson]

Using this code:

=IIf(IsNull([nCreateDate]),"",Format([nCreateDate],"m/d/yy (dddd)") &
" at " & Format([nCreateTime],"h:nn ampm"))

I label a note record for a company which looks something like this:

5/22/08 (Thursday) at 10:32 AM

I have been trying to make it look like this if the dates are recent:

12/24/08 (Yesterday) at 6:14 PM

or

12/16/08 (Last Tuesday) at 8:41 AM

But am striking out. Does anyone have experience with this?

You'll need some moderately complex logic. I'd suggest using a function - just
for fun I threw one together: you might want to add some error checking, etc.
but this should give you a start.

Public Function RecentName(dtDate As Date) As String
RecentName = Format(dtDate, "m/d/yy") & " ("
Select Case DateDiff("d", dtDate, Date)
Case 0 ' today
RecentName = RecentName & "Today"
Case 1 ' yesterday
RecentName = RecentName & "Yesterday"
Case 2 To 7 ' during the last week
RecentName = RecentName & "Last " & Format(dtDate, "dddd")
Case -1 ' tomorrow
RecentName = RecentName & "Tomorrow"
Case -7 To -2 ' during the next week
RecentName = RecentName & "Next " & Format(dtDate, "dddd")
Case Else
RecentName = RecentName & Format(dtDate, "dddd")
End Select
RecentName = RecentName & ")"
If TimeValue(dtDate) > 0 Then
RecentName = RecentName & " at " & Format(dtDate, "h:nn ampm")
End If
End Function

Just use RecentDate([InCreateDate]) in your expression rather than the
multiple format statements.

 

[Pete D.]

Neat idea and simple solution, kind of makes it classy and easier to read.
Although I don't have an oar in this pond thanks, Pete

Using this code:

=IIf(IsNull([nCreateDate]),"",Format([nCreateDate],"m/d/yy (dddd)") &
" at " & Format([nCreateTime],"h:nn ampm"))

I label a note record for a company which looks something like this:

5/22/08 (Thursday) at 10:32 AM

I have been trying to make it look like this if the dates are recent:

12/24/08 (Yesterday) at 6:14 PM

or

12/16/08 (Last Tuesday) at 8:41 AM

But am striking out. Does anyone have experience with this?

You'll need some moderately complex logic. I'd suggest using a function -
just
for fun I threw one together: you might want to add some error checking,
etc.
but this should give you a start.

Public Function RecentName(dtDate As Date) As String
RecentName = Format(dtDate, "m/d/yy") & " ("
Select Case DateDiff("d", dtDate, Date)
Case 0 ' today
RecentName = RecentName & "Today"
Case 1 ' yesterday
RecentName = RecentName & "Yesterday"
Case 2 To 7 ' during the last week
RecentName = RecentName & "Last " & Format(dtDate, "dddd")
Case -1 ' tomorrow
RecentName = RecentName & "Tomorrow"
Case -7 To -2 ' during the next week
RecentName = RecentName & "Next " & Format(dtDate, "dddd")
Case Else
RecentName = RecentName & Format(dtDate, "dddd")
End Select
RecentName = RecentName & ")"
If TimeValue(dtDate) > 0 Then
RecentName = RecentName & " at " & Format(dtDate, "h:nn ampm")
End If
End Function

Just use RecentDate([InCreateDate]) in your expression rather than the
multiple format statements.

 

[magmike]

Using this code:

=IIf(IsNull([nCreateDate]),"",Format([nCreateDate],"m/d/yy (dddd)") &
" at " & Format([nCreateTime],"h:nn ampm"))

I label a note record for a company which looks something like this:

5/22/08 (Thursday) at 10:32 AM

I have been trying to make it look like this if the dates are recent:

12/24/08 (Yesterday) at 6:14 PM

12/16/08 (Last Tuesday) at 8:41 AM

But am striking out. Does anyone have experience with this?

You'll need some moderately complex logic. I'd suggest using a function -just
for fun I threw one together: you might want to add some error checking, etc.
but this should give you a start.

Public Function RecentName(dtDate As Date) As String
RecentName = Format(dtDate, "m/d/yy") & " ("
Select Case DateDiff("d", dtDate, Date)
   Case 0 ' today
       RecentName = RecentName & "Today"
   Case 1 ' yesterday
       RecentName = RecentName & "Yesterday"
   Case 2 To 7  ' during the last week
       RecentName = RecentName & "Last " & Format(dtDate, "dddd")
   Case -1 ' tomorrow
       RecentName = RecentName & "Tomorrow"
   Case -7 To -2 ' during the next week
       RecentName = RecentName & "Next " & Format(dtDate, "dddd")
   Case Else
       RecentName = RecentName & Format(dtDate, "dddd")
End Select
RecentName = RecentName & ")"
If TimeValue(dtDate) > 0 Then
    RecentName = RecentName & " at " & Format(dtDate, "h:nn ampm")
End If
End Function

Just use RecentDate([InCreateDate]) in your expression rather than the
multiple format statements.
--

             John W. Vinson [MVP]- Hide quoted text -

- Show quoted text -

That's super-cool, thanks!

Of course, on the time part - I've always used two separate fields for
date and time, setting default values as Date() and Time() rather than
one field as Now(). I've never really thought about it before - I've
just always done it that way. Are there any disadvantages to doing it
the way I have been doing it (other than the obvious of having to
create two fields)?

 

[Douglas J. Steele]

The logic to query a database based on date/time criteria is far simpler to
manage when the data's in a single field.

While not really a disadvantage, be aware that Access doesn't really support
Time values very well: the Date variable type is intended to hold a complete
timestamp. Under the covers, it's an eight-byte floating point value, where
the integer portion represents the date as the number of days relative to 30
Dec,. 1899 and the decimal portion represents the time as a fraction of a
day. That means when you've got a field that holds 2008-12-26, it actually
holds a value representing that date at midnight, and when you've got a
field that holds 00:08:30, it actually holds a value representing that time
on 30 Dec, 1899.

 

[Arvin Meyer [MVP]]

Additionally, it unnecessarily uses the extra 8 bytes of space, which can
add up in a large database. And as you alluded to, it makes date math more
difficult.

 

[John W. Vinson]

Are there any disadvantages to doing it
the way I have been doing it (other than the obvious of having to
create two fields)?

Yes indeed. It makes it much harder to sort and search chronologically, e.g.
for "all records later than 8am on December 1". With a single field it's
simply > #12/1/2008 08:00#; with two fields you need to either reconstruct the
single date/time field by adding the values, or use a complex criterion with
separate logic for the date and time. In either case you lose the advantage of
any Index on the fields.

 

[Dirk Goldgar]

Additionally, it unnecessarily uses the extra 8 bytes of space, which can
add up in a large database. And as you alluded to, it makes date math more
difficult.

True. But the developer should be aware that queries using date criteria
have to be constructed to take into account the presence of the time portion
of the date/time field. For example, instead of

WHERE DateField = #12/26/2008#

one would need to use

WHERE DateField >= #12/26/2008# AND DateField < #12/27/2008#

or something to that effect.

 

[magmike]

Yes indeed. It makes it much harder to sort and search chronologically, e..g.
for "all records later than 8am on December 1". With a single field it's
simply > #12/1/2008 08:00#; with two fields you need to either reconstruct the
single date/time field by adding the values, or use a complex criterion with
separate logic for the date and time. In either case you lose the advantage of
any Index on the fields.

If I have been using Date() as the default value in that field - do I
have the time in there under the covers - or am I toast? I can see
that using one field would trim a lot of the size of my 500 MB
database.

 

[John W. Vinson]

If I have been using Date() as the default value in that field - do I
have the time in there under the covers - or am I toast? I can see
that using one field would trim a lot of the size of my 500 MB
database.

Date() will put in a time of 00:00:00 - midnight.

If you have both Date and Time fields, you can very easily run an update query
updating the desired date/time field to the [Datefield] + [Timefield]. A
Date/Time is stored as a Double Float count of days and fractions of a day
since midnight, December 30, 1899; as such a time field containing 6PM is
actually stored as 0.75, equivalent to #12/30/1899 18:00:00#. Today is 39808,
so if you add .75 to that you get 6pm this evening.