Change time format to ZULU in Access 2000

  • Thread starter Thread starter Joe
  • Start date Start date
J

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
 
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 said:
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
Click to expand...

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. 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
 
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
 
Joe said:
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
Click to expand...

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
 
Use the help. Put your cursor in the Format property box and press the
F1 key to go to the help for that property.

'====================================================
John Spencer
Access MVP 2002-2005, 2007-2009
The Hilltop Institute
University of Maryland Baltimore County
'====================================================
 
I definitely have a long way to go. Im a long term Excel user
Click to expand...

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,

Im already noticing this! It stinks but I will learn, it just might take me
longer :)!
Click to expand...

If you haven't seen these already, there are some good learning resources
available:

Jeff Conrad's resources page:
http://www.accessmvp.com/JConrad/accessjunkie/resources.html

The Access Web resources page:
http://www.mvps.org/access/resources/index.html

Roger Carlson's tutorials, samples and tips:
http://www.rogersaccesslibrary.com/

A free tutorial written by Crystal:
http://allenbrowne.com/casu-22.html

A video how-to series by Crystal:
http://www.YouTube.com/user/LearnAccessByCrystal

MVP Allen Browne's tutorials:
http://allenbrowne.com/links.html#Tutorials
 
Back
Top