Re: Date Format

From: Tim Ferguson (FergusonTG_at_softhome.net)
Date: 08/12/04 

Date: Thu, 12 Aug 2004 15:01:45 -0700

"=?Utf-8?B?RGF0ZSBGb3JtYXRpbmcgUHJvYmxlbQ==?=" <Date Formating
Problem@discussions.microsoft.com> wrote in
news:68A26459-A83E-4C37-B9F2-FABC04505C4E@microsoft.com:

>
> I have an import file (from Excel) that has a date in the following
> format: yyyymmdd

Importing from Excel is a notoriously cross-your-fingers-and-maybe-it'll-
work kind of affair. Is the field data type definitely a DateTime?
 
> I have created an input mask that adds a forward slash / to the date
> when I do the import and thus creating yyyy/mm/dd

Huh? Input mask only constrains user keystrokes, and does not affect data
values at all.
 
> I would like to have it as mm/dd/yyyy either in the table (preferred)
> or when I open the form and pull the data through a query have the
> date format as mm/dd/yyyy.

It's stored as a double-precision floating point serial number, but that
should not matter to you (as long as it really is a DateTime value). You
can have any output format you want.
 
> Any suggestions on an expression I can use in the query?

How about Format(MyDateField, "mm/dd/yyyy")

> On the form I have a combo box that the user selects a number and then
> a list box is populated with all of the data (6 different fields)
> related to that particular number.

Don't sound very Normalised... :-(
 
> Even though the input mask on the table improves the date format, when
> I open the form the date reverts back to the yyyymmdd format, no
> slashes.

I strongly suspect that this is a text or a numeric field rather than a
DateTime. There is no legal date format in the world that looks like
yyyymmdd so I would not be surprised that it was not parsed. You probably
need a conversion like

  DateSerial(CInt(Mid(DateString,1,4)), _
        CInt(Mid(DateString,5,2)), _
        CInt(Mid(DateString,7,2)))

or if it's a numeric, do the same thing with Div and Mod operators. If you
can get a reliable and safe conversion you can do lots of useful things
with a proper date value.

On the other hand, the yyyymmdd still sorts, compares, and selects in the
right way, so do you really need to massage it further?

Best wishes

Tim F

Relevant Pages

  • Re: Input Mask on Date for Year or Month Only
    ... Can I use an input mask to caputre only the year or only the month? ... If you wish to use the DateTime functions, ... A DateTime datatype value must be a valid date, ... format, the stored value must contain the day as well. ...
    (microsoft.public.access.gettingstarted)
  • Re: Input Mask on Date for Year or Month Only
    ... Can I use an input mask to caputre only the year or only the month? ... If you wish to use the DateTime functions, then the field's datatype ... format, the stored value must contain the day as well. ...
    (microsoft.public.access.gettingstarted)
  • Re: Wildcards in einem Parameterwert
    ... > abgesegnet wurde und nur von Amis erfunden werden konnte. ... Bei denen ist es bewusst Text im Format ... YYYYMMDD, genauer "Text der nur Ziffern enthalten darf" (Datentyp ...
    (microsoft.public.de.access)
  • Re: Storing dates
    ... If you must use a text field, you can use Format, "yyyymmdd") to ... get today's date in that format. ... Estoy utilizando la versión gratuita de SPAMfighter para usuarios ... Ha eliminado 454 correos spam hasta la fecha. ...
    (microsoft.public.access.forms)
  • Re: Access - ODBC to SQL - Incorrect Results
    ... holding the same format of data are acting differently in ACCESS. ... BTW. X-Base programs translated dates to yyyymmdd for sorting purposes ... So there should be no unsolvable problem in your using this ... and give it regular gas ... ...
    (comp.databases.ms-access)