DateTime options limited in tables

K

Karl H

I want to format a Date to a 10 character format, like 01/01/2005, but just
the short date or others are available. That makes it 1/1/2005. When I use
the input mask to 00/00/0000, it will only accept the full date, but it is
displayed still as the short date format when leaving the field on the table.
If I remove the Short date format from the format box of the design area of
the table, and I leave the input mask, it still saves it as the short date.
Is there a solution to this?
Thank you,
Karl
 
S

Steve Schapel

Karl,

You can set the Format property of the field in your tablke design like
this...
dd\/mm\/yyyy

Having said this, it is worth noting that for normal database use, the
purpose of a table is data storage, and as such you don't normally look
at it, so the appearance of the data is not usually important.
 
K

Karl H

Hi Steve, Thank you for writing. I don't actually care how it looks in the
table. I'm most interested in having the 10-character format for a report I
am trying to generate. I thought the report could only get the data as
originally stored in the table.
I tried the format suggestion you gave in your reply and it works fine.
Thank you very much. Am I not looking things up well enough somehow? I've got
a Developers Handbook, an Intro to Access Programming book, and the Access
Cookbook and I couldn't find the solution. If there's better references yet
that would be great to know.

I appreciate your help!
Karl
 
F

fredg

Hi Steve, Thank you for writing. I don't actually care how it looks in the
table. I'm most interested in having the 10-character format for a report I
am trying to generate. I thought the report could only get the data as
originally stored in the table.
I tried the format suggestion you gave in your reply and it works fine.
Thank you very much. Am I not looking things up well enough somehow? I've got
a Developers Handbook, an Intro to Access Programming book, and the Access
Cookbook and I couldn't find the solution. If there's better references yet
that would be great to know.

I appreciate your help!
Karl
Click to expand...

You are confusing an Input Mask with a Format.
If the field in the table is a Date datatype, it doesn't matter what
the format you want is. It is stored as a number (today is 38447), so
neither an Input Mask (which only forces data entry in a [particular
manner) nor a format (which only displays the data in a particular
manner) is relevant.

To show the field as 04/05/2005 in your report, simply set the report
control's format property to
mm/dd/yyyy

If the control's control source is an expression, you need to use a
Format() function within the expression itself, i.e.
= "Payment due on " & Format([DateField],"mm/dd/yyyy")
 
S

Steve Schapel

Karl,

As Fred points out, a date is stored as a number. In case you're
interested, it is the number of days since 31-Dec-1899.

I believe that if you set the Format property of the field in the table,
then Access will use this by default as the Format for any controls
bound to this field which you subsequently create on a form or a report.
On the other hand, you have control over the format (i.e. appearance)
of the way the date is shown for each instance of its presentation on
form or report, and it could be that you want the same date displayed in
different formats in different places throughout your application.

I would imagine most Access books would mention something about this
topic, but I agree that it might be difficult to find. Still, I just
looked at the index of my copy of "Microsoft Access 2003 Inside Out" by
John Viescas, and found an entry for "Date data type - Format property
setting", which points to a comprehensive outline. Anyway, as you have
discovered, the newsgroups are a great source of info :)
 
K

Karl H

Very helpful, thank you again,
Karl

Steve Schapel said:
Karl,

As Fred points out, a date is stored as a number. In case you're
interested, it is the number of days since 31-Dec-1899.

I believe that if you set the Format property of the field in the table,
then Access will use this by default as the Format for any controls
bound to this field which you subsequently create on a form or a report.
On the other hand, you have control over the format (i.e. appearance)
of the way the date is shown for each instance of its presentation on
form or report, and it could be that you want the same date displayed in
different formats in different places throughout your application.

I would imagine most Access books would mention something about this
topic, but I agree that it might be difficult to find. Still, I just
looked at the index of my copy of "Microsoft Access 2003 Inside Out" by
John Viescas, and found an entry for "Date data type - Format property
setting", which points to a comprehensive outline. Anyway, as you have
discovered, the newsgroups are a great source of info :)
Click to expand...
 
K

Karl H

Thank you Fred-well said, clarifying the differences between data storage,
input mask & format.--it clears things up for me.
Karl

fredg said:
Hi Steve, Thank you for writing. I don't actually care how it looks in the
table. I'm most interested in having the 10-character format for a report I
am trying to generate. I thought the report could only get the data as
originally stored in the table.
I tried the format suggestion you gave in your reply and it works fine.
Thank you very much. Am I not looking things up well enough somehow? I've got
a Developers Handbook, an Intro to Access Programming book, and the Access
Cookbook and I couldn't find the solution. If there's better references yet
that would be great to know.

I appreciate your help!
Karl
Click to expand...

You are confusing an Input Mask with a Format.
If the field in the table is a Date datatype, it doesn't matter what
the format you want is. It is stored as a number (today is 38447), so
neither an Input Mask (which only forces data entry in a [particular
manner) nor a format (which only displays the data in a particular
manner) is relevant.

To show the field as 04/05/2005 in your report, simply set the report
control's format property to
mm/dd/yyyy

If the control's control source is an expression, you need to use a
Format() function within the expression itself, i.e.
= "Payment due on " & Format([DateField],"mm/dd/yyyy")
Click to expand...
 
Top