Beginner's question about formatting date/time

[Linda Fox]

I wonder if someone can help with a question from a beginner?

I'm trying to build a database for my business as a private piano
teacher. I need to be able to keep all the personal data, plus the
attendance records and when they paid me, so that I can make a bill or
an account sheet from it. I used to keep a simple database years ago
when I used M$ Works, and later I was using Lotus Approach. There was
something I used to be able to do easily with Lotus and I can't find out
how to do it with Access. I'm sure it must be possible, but I can't see
it anywhere.

I want to be able to input the date of the last or of the current
lesson, by just typing the day number (7, 28, whatever) and it will
assume it's the current month unless I slash and add a month, BUT it
would display it as Thursday 26 August 2010. Then I also want to put in
four digits for the time, as per 24 hour clock, and have it display it
as a 12-hour clock with AM or PM. So for a lesson at a quarter to five
in the afternoon I want to be able to type 1645 and have it display
4.45PM with or without space.

I'm just funny that way; the reason for wanting to express the time in
that way is because I want to be absolutely sure when talking to pupils
and their families, who will undoubtedly express it in 12-hour terms,
that there's no slip-ups (your lesson is at 15:45 and in a hurry one or
other of us thinks it's at a quarter the six) The date thing is not so
difficult because it has a date finder (pop up calendar) and you can
format the way it displays the date; but typing just "26" for today's
date would be even quicker.

I've been through all sorts of stuff about the input mask but I can't
find anything about these two particular styles (the "predictive" month
and year thing and the 12-hour vs 24-hour time thing) I'm sure if Lotus
can do it then Microsoft can also.

If anyone knows of a pre-existing database which is suitable for this
sort of private (one-to-one) tutoring and includes scheduling,
attendance and accounting - and as a bonus, maybe even facility for
reporting on the lessons too - it could save me quite a bit of time and
effort! At the moment I'm keeping all of these records on different
programs; meanwhile, a little information on how I get to format the
date and time the way I want them by typing in what I want to.

Living in hope
Linda ff

 

[John W. Vinson]

I wonder if someone can help with a question from a beginner?

Just bear in mind that the *storage* of dates, the *input* of dates, and the
*format* of dates are three almost independent features.

Date/Time values are not stored formatted. A Date is actually stored as a
number, a count of days and fractions of a day since midnight, December 30,
1899.

You can display that value using any format you please - e.g.in order to see
"Thursday 26 August 2010" you could display the date/time field in a textbox
with a Format property of

"dddd dd mmmm yyyy hh:nn am/pm"

There are lots of other options for formatting, dig down in the Help for
"custom date formats".

There's nothing builtin in Access to autofill the month; typing (e.g.) 8/26
will autocomplete the year. Input Masks will probably hinder more than help
here; all they do is restrict what keystrokes you're allowed to type, and they
often require more typing not less. You could try a mask of

00/00 00:00

to let you type 08261500 to get "Thursday 26 August 2010 03:00 pm" in the
display.

It wouldn't be hard to write some VBA to replicate the functionality you
describe, but it's not clear how you want to combine that with entering a
time: should typing 2615 automagically enter 3:00pm on 8/26?
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Linda Fox]

There are lots of other options for formatting, dig down in the Help for
"custom date formats".

There's nothing builtin in Access to autofill the month; typing (e.g.) 8/26
will autocomplete the year. Input Masks will probably hinder more than help
here; all they do is restrict what keystrokes you're allowed to type, and they
often require more typing not less. You could try a mask of

00/00 00:00

to let you type 08261500 to get "Thursday 26 August 2010 03:00 pm" in the
display.

It wouldn't be hard to write some VBA to replicate the functionality you
describe, but it's not clear how you want to combine that with entering a
time: should typing 2615 automagically enter 3:00pm on 8/26?

Thanks, John. But is it really necessary to combine the date and the
time? I don't really want to do that. I'm quite happy just to use the
date finder, but I want to input 1545 and get 3:45PM (or 3.45PM, I'm not
fussy about the punctuation) on the display.

Linda ff

 

[Douglas J. Steele]

Thanks, John. But is it really necessary to combine the date and the time?
I don't really want to do that. I'm quite happy just to use the date
finder, but I want to input 1545 and get 3:45PM (or 3.45PM, I'm not fussy
about the punctuation) on the display.

Depends on what you're trying to do with the dates and times, but in
general, combining them into a single fields makes things MUCH simpler. You
can always use the DateValue and TmeValue functions when you only want to
refer to part of the value.

 

[John W. Vinson]

Thanks, John. But is it really necessary to combine the date and the
time? I don't really want to do that. I'm quite happy just to use the
date finder, but I want to input 1545 and get 3:45PM (or 3.45PM, I'm not
fussy about the punctuation) on the display.

Linda ff

I agree with Douglas, but yes, you can use separate fields; and you can use an
input mask of 00:00 to automatically fill in the colon, with a Format of
hh:nnam/pm.
--

John W. Vinson [MVP]
Microsoft's replacements for these newsgroups:
http://social.msdn.microsoft.com/Forums/en-US/accessdev/
http://social.answers.microsoft.com/Forums/en-US/addbuz/
and see also http://www.utteraccess.com

 

[Linda Fox]

Depends on what you're trying to do with the dates and times, but in
general, combining them into a single fields makes things MUCH simpler.
You can always use the DateValue and TmeValue functions when you only
want to refer to part of the value.

If I combine them into a single field but I really only want to look at
the date, or at the time, won't that make it simpler?

I'm not using them to record the time of a particular occurrence, I want
to be able to list dates and times of recurring lessons for private
pupils, Even having the day of the week show up separately is an
advantage there. I really don't want to have to see a whole string every
time.

My earlier efforts on other programs had resulted in a form view which
was actually a nice pale yellow full screen with the pupil's name at the
top of the page in bold red, the date of their last lesson, a record of
what was done in the lesson, suggestions for the next lesson, and then
the date and time of the following lesson. Each was duplicated as it was
used, so that one was overwritten and the other kept as a past record,
and they were sorted at the end of the day by "next lesson" so that when
I started it, or went to "next record", the work came up in the order it
was going to be happening over the following seven days, and the lesson
I was about to give was always at the top of the pile. I'm hoping to be
able to recreate this situation, and the requirements I mentioned would
have made the operation smoother; the first thing I did in the lesson
was to update the date at the top to the present date - one or two
strokes was usually enough. Even M$ Works on Windows 3.11 could do this!

Linda ff

 

[Linda Fox]

Depends on what you're trying to do with the dates and times, but in
general, combining them into a single fields makes things MUCH simpler.
You can always use the DateValue and TmeValue functions when you only
want to refer to part of the value.

If I combine them into a single field but I really only want to look at
the date, or at the time, won't that make it simpler?

I'm not using them to record the time of a particular occurrence, I want
to be able to list dates and times of recurring lessons for private
pupils, Even having the day of the week show up separately is an
advantage there. I really don't want to have to see a whole string every
time.

My earlier efforts on other programs had resulted in a form view which
was actually a nice pale yellow full screen with the pupil's name at the
top of the page in bold red, the date of their last lesson, a record of
what was done in the lesson, suggestions for the next lesson, and then
the date and time of the following lesson. Each was duplicated as it was
used, so that one was overwritten and the other kept as a past record,
and they were sorted at the end of the day by "next lesson" so that when
I started it, or went to "next record", the work came up in the order it
was going to be happening over the following seven days, and the lesson
I was about to give was always at the top of the pile. I'm hoping to be
able to recreate this situation, and the requirements I mentioned would
have made the operation smoother; the first thing I did in the lesson
was to update the date at the top to the present date - one or two
strokes was usually enough. Even M$ Works on Windows 3.11 could do this!

Linda ff

 

[David W. Fenton]

Depends on what you're trying to do with the dates and times, but
in general, combining them into a single fields makes things MUCH
simpler.

I respectfully disagree. I tend to keep dates and times separate so
that querying on dates does not require me to account for times.

 

[Douglas J. Steele]

I respectfully disagree. I tend to keep dates and times separate so
that querying on dates does not require me to account for times.

As I said, it depends on what you're trying to do. Certainly if you're
dealing with, say, pricing, so that you want to know what the price of an
object was/is/will be at a particular point in time, you want them combined.
Okay, you could simply add the date and time fields, but why bother with the
overhead?

 

[David W. Fenton]

As I said, it depends on what you're trying to do. Certainly if
you're dealing with, say, pricing, so that you want to know what
the price of an object was/is/will be at a particular point in
time, you want them combined. Okay, you could simply add the date
and time fields, but why bother with the overhead?

Because it's worth it to make querying more reliable and less fussy.

 

[David Kaye]

Because it's worth it to make querying more reliable and less fussy.

Eh? Many VBA functions work in Access SQL. So, a date/time field can be
queried as the DATEVALUE function of the date. For instance, in a database I
work with:

"select * from songs where datevalue(filedate) = #5/24/2010#" -- this will
select 5/24/2010 6:15:49 PM, 5/24/2010 10:30:04 AM, etc.

The query is very reliable and not fussy at all.

 

[John Spencer]

AND with large data sets (I have some sets with millions of records that get
searched by date) using DateValue will slow down the search immensely as the
function gets called for every record in the database and indexes cannot be
used when you do as you suggested.

With small datasets, the performance hit is not all that significant.

There are times when having a separate field for date and another for time is
a good idea and there are times when having the combined field with both date
and time is good.

As stated elsewhere, it depends on what you need to do and how large the
dataset is. If I needed to get all actions that occurred between 9 and 10 in
the morning over a two year period on a frequent basis, I very well might want
to split the date and time into two fields.

John Spencer
Access MVP 2002-2005, 2007-2010
The Hilltop Institute
University of Maryland Baltimore County

 

[David Kaye]

AND with large data sets (I have some sets with millions of records that get
searched by date) [....]

If you have millions of records to search then Access is not for you, but as
an MSMVP you already know that, don't you? You should have switched to SQL
Server, SQL Anywhere, or something else more robust . Access was always
intended as a small database engine. It happens to be excellent at what it
does, but it's no replacement for a more robust database designed for millions
of records.

 

[David W. Fenton]

(e-mail address removed) (David Kaye) wrote in

Eh? Many VBA functions work in Access SQL. So, a date/time field
can be queried as the DATEVALUE function of the date. For
instance, in a database I work with:

"select * from songs where datevalue(filedate) = #5/24/2010#" --
this will select 5/24/2010 6:15:49 PM, 5/24/2010 10:30:04 AM, etc.


The query is very reliable and not fussy at all.

It doesn't use the indexes, which makes it pretty much unacceptable
from my point of view. Maybe your data tables have so few records
that this doesn't matter, and it wouldn't for many of mine, but I
don't want to design an app for present conditions, but such that it
will always be fast.

 

[David Kaye]

It doesn't use the indexes, which makes it pretty much unacceptable
from my point of view. Maybe your data tables have so few records
that this doesn't matter, and it wouldn't for many of mine, but I
don't want to design an app for present conditions, but such that it
will always be fast.

What's wrong with indexing a date field? When I was writing medical records
software using Access 2000 on the back end, date indexing worked just fine.
Access soon showed its weaknesses, though, when we began getting 5 to 10
million test results and had to compact and rebuild indexes nightly. But that
was a problem with the fact that Access/Jet was designed for smaller datasets,
not the fault of the design in general (in my opinion).

 

[Banana]

What's wrong with indexing a date field? When I was writing medical records
software using Access 2000 on the back end, date indexing worked just fine.
Access soon showed its weaknesses, though, when we began getting 5 to 10
million test results and had to compact and rebuild indexes nightly. But that
was a problem with the fact that Access/Jet was designed for smaller datasets,
not the fault of the design in general (in my opinion).

As David Fenton mentioned, if you nest a criteria using a function in
left-hand side referencing a column like this:

WHERE TimeValue([MyDateColumn]) = #9:00 AM#

or

WHERE Left([MyStringColumn, 2]) = "BO"

or

WHERE Year([MyDateColumn]) = 1999

or any similar variants, there is no way for database engine to optimize
because it has to first evaluate the expression and thus it can't use
the index. It's one of reason why several optimization articles say to
not do those criteria (Year() and IsNull() is especially a common
mistake to make) and use different alternatives such as

WHERE [MyStringColumn] LIKE "BO%"

WHERE [MyDateColumn] BETWEEN #1999-01-01# AND #1999-12-31 23:59:59#

WHERE [SomeColumn] IS NULL

But with a combined Date/Time column we can't do this:

WHERE [MyDateColumn] BETWEEN #9:00 AM# #10:00 AM#

especially not if the MyDateColumn contains dates other than 1899-12-30.

Note this is not an Jet-specific thing - same principle applies to SQL
Server, PostgreSQL, MySQL, Oracle and several more RDBMS. To use index,
the left-hand side has to be in same form as what index is storing, and
for most RDBMS, indexes doesn't index expressions in general. If this is
important (and this is not without controversy), then there wouldd be
special steps taken; in SQL Server, we would created a persisted
calculated column and index upon that. In PostgreSQL, we would create a
functional index and make sure the left-hand side use the exact same
expression. Other RDBMS may have a different approach or in case of
Access, does not support this at all. But at the end of day, the values
of left-hand side has to match what is represented in index and for most
part that's just unmodified value. Using any functions or nesting it as
an expression and consequently changing the resultant value deny any
opportunities for optimization.

 

[David Kaye]

WHERE [MyStringColumn] LIKE "BO%"

But the LIKE operator only works decently when the wildcard is not at the
beginning of the string. So, your example is fine, but LIKE "%BO" (for
instance) would be terrible.

WHERE [MyDateColumn] BETWEEN #1999-01-01# AND #1999-12-31 23:59:59#
[....]

But with a combined Date/Time column we can't do this:

Actually you've just shown that it can be done by your example directly above.
In the example the resultset would be all times within the date range of 1/1
through 12/31, not merely those that happen to have no time (aka exactly
midnight) contained in the date.

David is right about not being able to optimize the SQL in my example due to
the presence of the function on the left. I guess that in my experience it's
been trivial enough time-wise that it hadn't mattered much to me.

 

[David W. Fenton]

(e-mail address removed) (David Kaye) wrote in

What's wrong with indexing a date field?

Um, what? I didn't say anything about *not* indexing date fields.

As Banana explains at length, if your WHERE clause uses an
expression to select on, the index won't be used, and a full table
scan will be required, as Access has to pull all the records, run
the value in each row through the expression and then see if the
results of the expression match the sought-after criterion.

In terms of results returned, this:

WHERE DateValue(MyDateField)=Date()

....is the same as:

WHERE MyDateField>=Date() AND MyDateField<Date()+1

But the former will cause the full table to be retrieved, each row
processed through the DateValue() function and the result for each
row compared to Date().

The latter will retrieve the index, run the index through the two
tests and then retrieve only the matching records (or their data
pages).

It makes no difference if this is a Jet/ACE back end or a server
database. Using expressions to process the stored values into some
form that's convenient for the right side of the comparison is going
to fail to use any available index no matter the database platform.

When I was writing medical records
software using Access 2000 on the back end, date indexing worked
just fine. Access soon showed its weaknesses, though, when we
began getting 5 to 10 million test results and had to compact and
rebuild indexes nightly. But that was a problem with the fact
that Access/Jet was designed for smaller datasets, not the fault
of the design in general (in my opinion).

Maybe your problem was that you were using expressions in your
criteria and thus not using the indexes at all.

 

[Banana]

But the LIKE operator only works decently when the wildcard is not at the
beginning of the string. So, your example is fine, but LIKE "%BO" (for
instance) would be terrible.
Correct.

Actually you've just shown that it can be done by your example directly above.
In the example the resultset would be all times within the date range of 1/1
through 12/31, not merely those that happen to have no time (aka exactly
midnight) contained in the date.

But if the question being asked was: "Get all events that occurred
between 9 AM to 10 AM for a given day during the year of 1999.", that
would be hard to express and certainly not without a reference to
TimeValue() which will negate the opportunity for optimization.

It can be at least partially optimized by doing something like this:

WHERE MyDateColumn BETWEEN #1999-01-01# AND #1999-12-31 23:59:59# AND
TimeValue(MyDateColumn) BETWEEN #9:00 AM# AND #10:00 AM#

which for most cases at least enable the optimizer to perform a range
scan which is certainly much faster than a full table scan but it still
has to evaluate every and each row to decide if it's actually within 9
AM and 10 AM for this year.

Whether this partial optimization is good enough, will ultimately depend
on the data set, the business requirement, etc. etc.

David is right about not being able to optimize the SQL in my example due to
the presence of the function on the left. I guess that in my experience it's
been trivial enough time-wise that it hadn't mattered much to me.

Well, my view is that a query that runs 10 millisecond is preferable to
a query that runs 20 millisecond. The end users won't even notice the
difference of 10 extra millisecond, but it's not always about what you
can see on the screen but also what is going behind the curtain; 10 less
milliseconds means just as less chance for lock contention (and
deadlock), and enables the server (in case of Access & other serverless
database engine, the running application) to respond faster to
concurrent requests among the users which ultimately translate into more
scalability for same stack. It's not as if the only alternative is to go
out and buy a large pile of iron and a million-dollar-plus SLA with
Oracle, after all. That's why David Fenton was talking about
designing the applications to always perform at its best by being
judicious with criteria (and not just in the WHERE clause) so that it
always can be optimized. It's sometime necessary to revisit some old
queries as the dataset evolve, but for a good portion of queries out
there, it should be generally possible to write once and be done with it.

 

[David W. Fenton]

(e-mail address removed) (David Kaye) wrote in

WHERE [MyStringColumn] LIKE "BO%"

But the LIKE operator only works decently when the wildcard is not
at the beginning of the string. So, your example is fine, but
LIKE "%BO" (for instance) would be terrible.

WHERE [MyDateColumn] BETWEEN #1999-01-01# AND #1999-12-31
23:59:59#
[....]

But with a combined Date/Time column we can't do this:

Actually you've just shown that it can be done by your example
directly above. In the example the resultset would be all times
within the date range of 1/1 through 12/31, not merely those that
happen to have no time (aka exactly midnight) contained in the
date.

LIKE with dates won't use the index, since the representation in the
form "12/31/2009" has nothing to do with the actual storage. LIKE is
for string comparisons, and date values are stored as doubles, so
using LIKE would require converting every date representation into a
string for comparison. This would obviously not use the index!

David is right about not being able to optimize the SQL in my
example due to the presence of the function on the left. I guess
that in my experience it's been trivial enough time-wise that it
hadn't mattered much to me.

It will very often be trivial if the number of rows being tested is
small. That's particularly the case if there are other criteria, as
those will be processed first to winnow the resultset down to the
fewest possible rows that have to be scanned for the function.

But if your date criterion is the only one and the number of rows
are large, it will be much slower to test an expression. Whether
this matters will depend on how many rows are being tested, but, as
I said, because my apps are written for the long haul (this week I'm
upsizing the back end of an app that went in production in late
1999, for example, and has code that dates back to the original
release), I would avoid any criteria that won't utilize the index.