Re: Date confusion

Matt: Read my reply elsewhere in this string. Much of what you're suggesting
is incorrect.

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


"matt -`;'-" <mbox1@xxxxxxxxxx> wrote in message
news:1b2de$43228bbf$97d50759$18274@xxxxxxxxxxxxx
>
> "Ace9x" <Ace9x@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
> news:AB5E3A45-94AD-4D46-8E23-9CA35AC56FED@xxxxxxxxxxxxxxxx
>> I have created a function in the Main module in Access, which when called
>> with a Financial Year parameter creates 52 weekly entries in a table with
>> period no as 1 to 52, start and end date. This seems okay until I view
>> the
>> new entries in the table. Some of the dates are displayed in dd/mm/yyyy
>> format whilst others appear in mm/dd/yyyy format. There are no format or
>> input masks present on the table itself.
>> For the sql insert statement of the 2 dates I used :-
>>
>> "#" & format(dteStart,"dd/mm/yyyy") ",#" & format(dteEnd,"dd/mm/yyyy") &
>> "#"
>>
>> Obviously something is wrong either with the insert SQL or the display of
>> the dates in the table, can someone please advise appropriately.
>>
>> Cheers,
>> Steve
>
> The dates you are probably working with are a numeric value that the
> program displays in a date format like m/d/y. The actual date
> value is a 5 digit Long Integer.

Actually, the date value is an 8 byte floating point number, where the
integer portion of the value represents the date as the number of days
relative to 30 Dec, 1899, and the decimal portion represents time as a
fraction of a day.

> If you are working with a numeric date and you want the format DDMMYYYY,
> then you will need to store the dates you use as String
> representations of a date in the Table. (The data type in the table will
> be text instead of date.) Visually this will look the
> same to you, but to the program it will see a Text String and will keep it
> properly formatted to dd/mm/yyyy. If you need to perform
> math on the dates then you will need to convert them back to date values.
> See below for both methods.

Why? Store the date as a date, and use a format statement to display it how
you want. Remember: the date is stored as a number, not in a specific
format.

> '--Convert a numeric date value to a string date value
> Format(CStr(dteStart),"dd/mm/yyyy")
> '--Convert a string date value formatted as DD/MM/YYYY to a numeric date
> value
> DateValue(Mid(strDate,4,2) & "/" & Left(strDate,2) & "/" &
> Right(strDate,4))

These are both iffy, since you're relying on Access to coerce the value from
a string into a date before it applies the format. Unless you can be certain
of what the user's Short Date setting is, it may not coerce the way you want
it to.



.

Relevant Pages

  • Re: Another VBA bug
    ... to your Format example, ie an exact .5 always rounds up. ... rather coerce to a valid number. ... string, which it does however surprising at first it may seem. ... dbl = CDate' OK' ...
    (microsoft.public.excel.programming)
  • Re: Multiple date formats in a Table
    ... a date field could not store just the year 2004 - it would have ... display it as 1 Jan 2004, or you would have to just display the year for all ... The conversion of your existing text format date should be done in a query, ... Public Function TextToDate(strDate As String) As Date ...
    (microsoft.public.access.tablesdbdesign)
  • Re: Displaying dates in dd/mm/yy format
    ... the short date also contains time information. ... You can however format the string to shortformat. ... Can I somehow force Access to store the short ...
    (microsoft.public.dotnet.framework.adonet)
  • Re: Date format detection
    ... Specifies the locale for which the date string is to be formatted. ... date format for this locale. ... the system default-date format for the specified locale. ... be enclosed within single quotation marks in the date format picture. ...
    (borland.public.delphi.thirdpartytools.general)
  • Re: Date format detection
    ... > Specifies the locale for which the date string is to be formatted. ... > date format for this locale. ... > the system default-date format for the specified locale. ... > be enclosed within single quotation marks in the date format picture. ...
    (borland.public.delphi.thirdpartytools.general)
Loading