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

On Fri, 27 Aug 2010 22:16:20 +0100, Linda Fox <(E-Mail Removed)> wrote:

>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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

On 28/08/2010 01:29, John W. Vinson wrote:
>
> 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

"Linda Fox" <(E-Mail Removed)> wrote in message
news:Cs3eo.25594$Vh3.6985@hurricane...
> On 28/08/2010 01:29, John W. Vinson wrote:
>>
>> 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.

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.

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/djsteele
Co-author: "Access 2010 Solutions", published by Wiley
(no e-mails, please!)

On Sat, 28 Aug 2010 09:06:53 +0100, Linda Fox <(E-Mail Removed)> wrote:

>On 28/08/2010 01:29, John W. Vinson wrote:
>>
>> 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

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/For...-US/accessdev/
http://social.answers.microsoft.com/.../en-US/addbuz/
and see also http://www.utteraccess.com

On 28/08/2010 13:32, Douglas J. Steele wrote:
>
> 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

On 28/08/2010 13:32, Douglas J. Steele wrote:
>
> 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

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in
news:i5avk1$69l$(E-Mail Removed):

> 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.

--
David W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/

"David W. Fenton" <(E-Mail Removed)> wrote in message
news:Xns9DE29B06F82E1f99a49ed1d0c49c5bbb2@74.209.136.91...
> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in
> news:i5avk1$69l$(E-Mail Removed):
>
>> 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.

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?

--
Doug Steele, Microsoft Access MVP
http://www.AccessMVP.com/DJSteele/AccessIndex.html
Co-author: "Access 2010 Solutions", published by Wiley
(no private e-mails, please)

"Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in
news:i5bsh1$em$(E-Mail Removed):

> "David W. Fenton" <(E-Mail Removed)> wrote in message
> news:Xns9DE29B06F82E1f99a49ed1d0c49c5bbb2@74.209.136.91...
>> "Douglas J. Steele" <NOSPAM_djsteele@NOSPAM_gmail.com> wrote in
>> news:i5avk1$69l$(E-Mail Removed):
>>
>>> 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.
>
> 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 W. Fenton http://www.dfenton.com/
contact via website only http://www.dfenton.com/DFA/