> Hi all,
>
> Im trying to learn Access the hard way - by doing.  I am creating a table
> with various data in Access 2000.  In DESIGN VIEW, Im trying to set one of
> the fields to be time only and have it in 24 hour format.  I will be the only
> one putting data into it so instead of typing a "Z" after every time stamp, I
> want fix the table so that it automatically enters the letter z after i put
> in the time. 
>
> Additionally, I do NOT want the ":" character between the Hour and Minutes. 
> Is this possible? 
>
> Here's an example of what I want to do.  While inputting data into the
> table, I want to put in 2130 and have it automatically format it to show it
> as 2130z.  The reason I want it to be time format is so that if I put in an
> incorrect character or time it will return an error.  EG - if i put in 2a30
> it will error or if i put in 2530 it will error.
>
> Help?
>
> Joe

"John Spencer MVP" wrote:

> Access treats times as a number and therefore you cannot add the designator to
> the time.  HOWEVER, if the time is always going to be Z and you only want to
> display it, you can use the format property and set that to
>    hhnn\z
> That will probably drop the leading zero for times from 00:00 to 10:59.
>
> That should DISPLAY the data the way you want it.  You can control the input
> by using an input mask that looks like
>    00:00;0;_
> During entry you will see the colon separator, but will not have to enter it.
>
> If that is not acceptable, then you are going to have to use VBA in the entry
> form to check the entry in the control to make sure it is a valid time and if
> so append the Z to the end of the value.
>
> John Spencer
> Access MVP 2002-2005, 2007-2009
> The Hilltop Institute
> University of Maryland Baltimore County
>
> Joe wrote:
> > Hi all,
> >
> > Im trying to learn Access the hard way - by doing.  I am creating a table
> > with various data in Access 2000.  In DESIGN VIEW, Im trying to set one of
> > the fields to be time only and have it in 24 hour format.  I will be the only
> > one putting data into it so instead of typing a "Z" after every time stamp, I
> > want fix the table so that it automatically enters the letter z after i put
> > in the time. 
> >
> > Additionally, I do NOT want the ":" character between the Hour and Minutes. 
> > Is this possible? 
> >
> > Here's an example of what I want to do.  While inputting data into the
> > table, I want to put in 2130 and have it automatically format it to show it
> > as 2130z.  The reason I want it to be time format is so that if I put in an
> > incorrect character or time it will return an error.  EG - if i put in 2a30
> > it will error or if i put in 2530 it will error.
> >
> > Help?
> >
> > Joe
>

> Mr. Spencer,
>
> Thanks very much!  I guess I need to learn more about data in the Formats
> section and the Input Mask.  Any recommendations on where I might learn some
> of that?  Thanks again - it works just like I envisioned!
>
> Joe
>
>
>
>
> "John Spencer MVP" wrote:
>
>> Access treats times as a number and therefore you cannot add the designator to
>> the time.  HOWEVER, if the time is always going to be Z and you only want to
>> display it, you can use the format property and set that to
>>    hhnn\z
>> That will probably drop the leading zero for times from 00:00 to 10:59.
>>
>> That should DISPLAY the data the way you want it.  You can control the input
>> by using an input mask that looks like
>>    00:00;0;_
>> During entry you will see the colon separator, but will not have to enter it.
>>
>> If that is not acceptable, then you are going to have to use VBA in the entry
>> form to check the entry in the control to make sure it is a valid time and if
>> so append the Z to the end of the value.
>>
>> John Spencer
>> Access MVP 2002-2005, 2007-2009
>> The Hilltop Institute
>> University of Maryland Baltimore County
>>
>> Joe wrote:
>>> Hi all,
>>>
>>> Im trying to learn Access the hard way - by doing.  I am creating a table
>>> with various data in Access 2000.  In DESIGN VIEW, Im trying to set one of
>>> the fields to be time only and have it in 24 hour format.  I will be the only
>>> one putting data into it so instead of typing a "Z" after every time stamp, I
>>> want fix the table so that it automatically enters the letter z after i put
>>> in the time. 
>>>
>>> Additionally, I do NOT want the ":" character between the Hour and Minutes. 
>>> Is this possible? 
>>>
>>> Here's an example of what I want to do.  While inputting data into the
>>> table, I want to put in 2130 and have it automatically format it to show it
>>> as 2130z.  The reason I want it to be time format is so that if I put in an
>>> incorrect character or time it will return an error.  EG - if i put in 2a30
>>> it will error or if i put in 2530 it will error.
>>>
>>> Help?
>>>
>>> Joe

> Hi all,
>
> Im trying to learn Access the hard way - by doing.  I am creating a table
> with various data in Access 2000.  In DESIGN VIEW, Im trying to set one of
> the fields to be time only and have it in 24 hour format.  I will be the only
> one putting data into it so instead of typing a "Z" after every time stamp, I
> want fix the table so that it automatically enters the letter z after i put
> in the time. 
>
> Additionally, I do NOT want the ":" character between the Hour and Minutes. 
> Is this possible? 
>
> Here's an example of what I want to do.  While inputting data into the
> table, I want to put in 2130 and have it automatically format it to show it
> as 2130z.  The reason I want it to be time format is so that if I put in an
> incorrect character or time it will return an error.  EG - if i put in 2a30
> it will error or if i put in 2530 it will error.
>
> Help?
>
> Joe

"Philip Herlihy" wrote:

> Joe wrote:
> > Hi all,
> >
> > Im trying to learn Access the hard way - by doing.  I am creating a table
> > with various data in Access 2000.  In DESIGN VIEW, Im trying to set one of
> > the fields to be time only and have it in 24 hour format.  I will be the only
> > one putting data into it so instead of typing a "Z" after every time stamp, I
> > want fix the table so that it automatically enters the letter z after i put
> > in the time. 
> >
> > Additionally, I do NOT want the ":" character between the Hour and Minutes. 
> > Is this possible? 
> >
> > Here's an example of what I want to do.  While inputting data into the
> > table, I want to put in 2130 and have it automatically format it to show it
> > as 2130z.  The reason I want it to be time format is so that if I put in an
> > incorrect character or time it will return an error.  EG - if i put in 2a30
> > it will error or if i put in 2530 it will error.
> >
> > Help?
> >
> > Joe
>
> There's no other way of learning Access, although books and tutorials do
> have their place.
>
> You're right to think you should store these values as a date/time
> value.  In queries, forms and reports you can use "custom formats" to
> specify precisely how you want the value _displayed_.  In the Access
> 2003 open in front of me I found the information in the Help for the VBA
> design environment (open this by holding ALT and tapping F11) and
> searching the Help there.  I found that searching for "dddddd" brought
> up the page (and only that page) describing "custom formats", which you
> can use in the "Format function" (see normal Access Help for that).  The
> Format function returns a string, rather than a Date/Time (whose
> underlying representation is a number with integer and fractional part),
> and a Custom format can specify any characters you want.
>
> Phil, London
>

> Mr. Herlihy,
>
> Thanks for replying!  your reply helped me see the error of my ways - i
> failed to use the in program help function!  I will search through that
> vigorously before posting!  Thanks again!
>
> Joe
>
> "Philip Herlihy" wrote:
>
>> Joe wrote:
>>> Hi all,
>>>
>>> Im trying to learn Access the hard way - by doing.  I am creating a table
>>> with various data in Access 2000.  In DESIGN VIEW, Im trying to set one of
>>> the fields to be time only and have it in 24 hour format.  I will be the only
>>> one putting data into it so instead of typing a "Z" after every time stamp, I
>>> want fix the table so that it automatically enters the letter z after i put
>>> in the time. 
>>>
>>> Additionally, I do NOT want the ":" character between the Hour and Minutes. 
>>> Is this possible? 
>>>
>>> Here's an example of what I want to do.  While inputting data into the
>>> table, I want to put in 2130 and have it automatically format it to show it
>>> as 2130z.  The reason I want it to be time format is so that if I put in an
>>> incorrect character or time it will return an error.  EG - if i put in 2a30
>>> it will error or if i put in 2530 it will error.
>>>
>>> Help?
>>>
>>> Joe
>> There's no other way of learning Access, although books and tutorials do
>> have their place.
>>
>> You're right to think you should store these values as a date/time
>> value.  In queries, forms and reports you can use "custom formats" to
>> specify precisely how you want the value _displayed_.  In the Access
>> 2003 open in front of me I found the information in the Help for the VBA
>> design environment (open this by holding ALT and tapping F11) and
>> searching the Help there.  I found that searching for "dddddd" brought
>> up the page (and only that page) describing "custom formats", which you
>> can use in the "Format function" (see normal Access Help for that).  The
>> Format function returns a string, rather than a Date/Time (whose
>> underlying representation is a number with integer and fractional part),
>> and a Custom format can specify any characters you want.
>>
>> Phil, London
>>

"Philip Herlihy" wrote:

> Joe wrote:
> > Mr. Herlihy,
> >
> > Thanks for replying!  your reply helped me see the error of my ways - i
> > failed to use the in program help function!  I will search through that
> > vigorously before posting!  Thanks again!
> >
> > Joe
> >
> > "Philip Herlihy" wrote:
> >
> >> Joe wrote:
> >>> Hi all,
> >>>
> >>> Im trying to learn Access the hard way - by doing.  I am creating a table
> >>> with various data in Access 2000.  In DESIGN VIEW, Im trying to set one of
> >>> the fields to be time only and have it in 24 hour format.  I will be the only
> >>> one putting data into it so instead of typing a "Z" after every time stamp, I
> >>> want fix the table so that it automatically enters the letter z after i put
> >>> in the time. 
> >>>
> >>> Additionally, I do NOT want the ":" character between the Hour and Minutes. 
> >>> Is this possible? 
> >>>
> >>> Here's an example of what I want to do.  While inputting data into the
> >>> table, I want to put in 2130 and have it automatically format it to show it
> >>> as 2130z.  The reason I want it to be time format is so that if I put in an
> >>> incorrect character or time it will return an error.  EG - if i put in 2a30
> >>> it will error or if i put in 2530 it will error.
> >>>
> >>> Help?
> >>>
> >>> Joe
> >> There's no other way of learning Access, although books and tutorials do
> >> have their place.
> >>
> >> You're right to think you should store these values as a date/time
> >> value.  In queries, forms and reports you can use "custom formats" to
> >> specify precisely how you want the value _displayed_.  In the Access
> >> 2003 open in front of me I found the information in the Help for the VBA
> >> design environment (open this by holding ALT and tapping F11) and
> >> searching the Help there.  I found that searching for "dddddd" brought
> >> up the page (and only that page) describing "custom formats", which you
> >> can use in the "Format function" (see normal Access Help for that).  The
> >> Format function returns a string, rather than a Date/Time (whose
> >> underlying representation is a number with integer and fractional part),
> >> and a Custom format can specify any characters you want.
> >>
> >> Phil, London
> >>
>
> Access Help is not for the faint-hearted, it has to be said.  It's
> usually necessary to get an overview from a book or tutorial (I like the
> ones I've used at lynda.com) first.  Access is a big subject - I've
> reached the stage where I watch postings here in order to answer the
> ones I can and follow the ones I can't!
>
> Phil
>

"John W. Vinson" wrote:

> On Wed, 17 Jun 2009 11:56:02 -0700, Joe <J**@discussions.microsoft.com> wrote:
>
> >I definitely have a long way to go.  Im a long term Excel user
>
> Not to be discouraging, but just as a word to the wise - expertise in Excel
> can actually make it a bit harder to get into the Access mindset! Tables may
> look like spreadsheets but they emphatically are NOT, and many of the things
> you would routinely do in Excel are just plain wrong in a database context.
> Prepare to "unlearn" some of the things you've "always known"...
> --
>
>              John W. Vinson [MVP]
>