Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion GroupsFormsForms ProgrammingQueriesModules / DAO / VBAReports / PrintingMacrosDatabase DesignSecurityConversionImporting / LinkingSQL Server / ADPMultiuser / NetworkingReplicationSetup / ConfigurationDeveloper ToolkitsActiveX ControlsNew UsersGeneral 1General 2
Access DirectoryToolsTutorialsUser Groups
Related Topics
SQL ServerOther DB ProductsMS OfficeMore Topics ...

MS Access Forum / Database Design / February 2004

Tip: Looking for answers? Try searching our database.

Date/Time custom format

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
Pat Voght - 08 Feb 2004 15:47 GMT
I need to retrieve records based on a selected General
Ledger Accounting period of month and year.  Can I store
the date in the format of mm/yy in a table field?  If so,
how do you create that format?  If I cannot store data in
that format, can I retrieve records based on the month
and year of a date?  I have attempted to do so using the
datePart function in a query on a date/time field but
with no success.   I appreciate any help you can give me.
Pat
Douglas J. Steele - 08 Feb 2004 17:06 GMT
The Date data type in Access is intended to store complete dates: it's an 8
byte floating point number, where the integer part represents the date as
the number of days relative to 30 Dec, 1899, and the decimal part represents
the time as a fraction of a day. In other words, it's not possible to store
only mm/yy in a date field.

If all you want is month and year, consider storing the month and the year
as separate integer fields (unless you want to store a text version of the
month name)

If you've got complete dates in the field, though, you can easily retrieve
by month and year using something like the following:

WHERE Format([DateEntry], "yyyymm") = 200402

Signature

Doug Steele, Microsoft Access MVP
http://I.Am/DougSteele
(No private e-mails, please)

> I need to retrieve records based on a selected General
> Ledger Accounting period of month and year.  Can I store
[quoted text clipped - 5 lines]
> with no success.   I appreciate any help you can give me.
> Pat
Pat Voght - 08 Feb 2004 23:21 GMT
Thanks,
I now have a better understanding of how Access handles dates.  I tried format() and it solves my problem
Thanks again
Pat
Bas Cost Budde - 09 Feb 2004 09:42 GMT
> The Date data type in Access is intended to store complete dates: it's an 8
> byte floating point number, where the integer part represents the date as
> the number of days relative to 30 Dec, 1899,

Luckily there is no Macintosh version of Access, as there is Excel--Mac
has a different date starting point.

> If all you want is month and year, consider storing the month and the year
> as separate integer fields (unless you want to store a text version of the
> month name)

I don't really believe that is a solution, unless you never need math on
the dates. Even deriving the next value will be a pain (exaggerating
slightly here, bounds checking for 12 is all really) and finding the
interval requires help. Let alone that you have to have extra validation.

Signature

Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

Bas Cost Budde - 08 Feb 2004 17:29 GMT
> Can I store the date in the format of mm/yy in a table field?

Maybe; but there is no need. You can use a normal date (allowing date
calculations by using standard functions) and if you don't need the day,
set it to 1.

> can I retrieve records based on the month
> and year of a date?

Most certainly. Create an expression in the query like

format(yourdatefield, "mm/yy")

and put appropriate criteria below.

Signature

Bas Cost Budde
http://www.heuveltop.org/BasCB
but the domain is nl

- 16 Feb 2004 20:01 GMT
If you don't care about the day, just save it as the first
of the month (e.g., 3/1/2004). Then use the Month and Year
functions to retrieve the parts you want. e.g.

MyDate = Month(Date) & "/" & Year(Date)

Results in MyDate = 3/2004
John Vinson - 17 Feb 2004 02:09 GMT
On Mon, 16 Feb 2004 12:01:13 -0800,

>If you don't care about the day, just save it as the first
>of the month (e.g., 3/1/2004). Then use the Month and Year
[quoted text clipped - 3 lines]
>
>Results in MyDate = 3/2004

or even simply set the Format property of the field to

m/yyyy

                 John W. Vinson[MVP]    
   Come for live chats every Tuesday and Thursday
http://go.compuserve.com/msdevapps?loc=us&access=public
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2008 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.