Date Format MMDDYYYY

The date format mmddyyyy is common but I realize it's not a standard
format in Access. There must be a simple way to provide this format
to Access so it treats it as a date? In past, I play with it and do a
(Left MM, Mid, and Right Function) and add "/" so it's recognized.
There has to be a better way than this?? What I'd want to do is be
able to import the data directly into a date field, vs import into a
text and then transfer to a date field.

Re: Date Format MMDDYYYY

Sub DateStuff()
Dim d1 As Date
d1 = #11/19/2007#
Debug.Print d1 '--this prints 11/19/2007
Debug.Print Format(d1, "mmddyyyy")
'-- this prints 11192007
End Sub


Rich

*** Sent via Developersdex http://www.developersdex.com ***

Re: Date Format MMDDYYYY

On Mon, 19 Nov 2007 11:02:23 -0800 (PST), veaux@aol.com wrote:

> The date format mmddyyyy is common but I realize it's not a standard
> format in Access. There must be a simple way to provide this format
> to Access so it treats it as a date? In past, I play with it and do a
> (Left MM, Mid, and Right Function) and add "/" so it's recognized.
> There has to be a better way than this?? What I'd want to do is be
> able to import the data directly into a date field, vs import into a
> text and then transfer to a date field.

If the field is a Date Datatype, simply set the Format property of the
control on your form to:
mmddyyyy
--
Fred
Please respond only to this newsgroup.
I do not reply to personal e-mail

Re: Date Format MMDDYYYY

On Nov 19, 4:35 pm, fredg wrote:
> On Mon, 19 Nov 2007 11:02:23 -0800 (PST), ve...@aol.com wrote:
> > The date format mmddyyyy is common but I realize it's not a standard
> > format in Access. There must be a simple way to provide this format
> > to Access so it treats it as a date? In past, I play with it and do a
> > (Left MM, Mid, and Right Function) and add "/" so it's recognized.
> > There has to be a better way than this?? What I'd want to do is be
> > able to import the data directly into a date field, vs import into a
> > text and then transfer to a date field.
>
> If the field is a Date Datatype, simply set the Format property of the
> control on your form to:
> mmddyyyy
> --
> Fred
> Please respond only to this newsgroup.
> I do not reply to personal e-mail

Thanks for suggestions above, but I'm actually referring to importing
the data into a table vs using it in a form. I'm importing a csv
table in which that field is text 8 char. Importing it into an Access
Date field formatted as mmddyyyy does not seem to work. I don't want
to import into a text field, as I am now, because then I cannot
calculate off of the date.

Re: Date Format MMDDYYYY

On Nov 26, 8:55 am, "ve...@aol.com" wrote:
> On Nov 19, 4:35 pm, fredg wrote:
>
>
>
>
>
> > On Mon, 19 Nov 2007 11:02:23 -0800 (PST), ve...@aol.com wrote:
> > > The date format mmddyyyy is common but I realize it's not a standard
> > > format in Access. There must be a simple way to provide this format
> > > to Access so it treats it as a date? In past, I play with it and do a
> > > (Left MM, Mid, and Right Function) and add "/" so it's recognized.
> > > There has to be a better way than this?? What I'd want to do is be
> > > able to import the data directly into a date field, vs import into a
> > > text and then transfer to a date field.
>
> > If the field is a Date Datatype, simply set the Format property of the
> > control on your form to:
> > mmddyyyy
> > --
> > Fred
> > Please respond only to this newsgroup.
> > I do not reply to personal e-mail
>
> Thanks for suggestions above, but I'm actually referring to importing
> the data into a table vs using it in a form. I'm importing a csv
> table in which that field is text 8 char. Importing it into an Access
> Date field formatted as mmddyyyy does not seem to work. I don't want
> to import into a text field, as I am now, because then I cannot
> calculate off of the date.- Hide quoted text -
>
> - Show quoted text -

When you import the data, you can modify then save an import
specification which will let you properly import the data that is
formatted as mmddyyyy.

Open the Get external data dialog.
choose text type.
navigate to the desired file.
The Text import wizard will open
Select delimited, then click Next.
Set import options, (delimiter, First row contains field names, text
qualifier), click Advanced
Select your date field, then set the date order to MDY, four digit
years to yes, Date delimter to nothing, leading zeroes in dates as
required, time delimiter to nothing.
Now save the import specification with a meaningful name. click ok.
click next. choose destination then click next, add a key if desired,
click next, click next, click finish.

Re: Date Format MMDDYYYY

On Nov 26, 12:49 pm, "rquin...@sympatico.ca"
wrote:
> On Nov 26, 8:55 am, "ve...@aol.com" wrote:
>
>
>
>
>
> > On Nov 19, 4:35 pm, fredg wrote:
>
> > > On Mon, 19 Nov 2007 11:02:23 -0800 (PST), ve...@aol.com wrote:
> > > > The date format mmddyyyy is common but I realize it's not a standard
> > > > format in Access. There must be a simple way to provide this format
> > > > to Access so it treats it as a date? In past, I play with it and do a
> > > > (Left MM, Mid, and Right Function) and add "/" so it's recognized.
> > > > There has to be a better way than this?? What I'd want to do is be
> > > > able to import the data directly into a date field, vs import into a
> > > > text and then transfer to a date field.
>
> > > If the field is a Date Datatype, simply set the Format property of the
> > > control on your form to:
> > > mmddyyyy
> > > --
> > > Fred
> > > Please respond only to this newsgroup.
> > > I do not reply to personal e-mail
>
> > Thanks for suggestions above, but I'm actually referring to importing
> > the data into a table vs using it in a form. I'm importing a csv
> > table in which that field is text 8 char. Importing it into an Access
> > Date field formatted as mmddyyyy does not seem to work. I don't want
> > to import into a text field, as I am now, because then I cannot
> > calculate off of the date.- Hide quoted text -
>
> > - Show quoted text -
>
> When you import the data, you can modify then save an import
> specification which will let you properly import the data that is
> formatted as mmddyyyy.
>
> Open the Get external data dialog.
> choose text type.
> navigate to the desired file.
> The Text import wizard will open
> Select delimited, then click Next.
> Set import options, (delimiter, First row contains field names, text
> qualifier), click Advanced
> Select your date field, then set the date order to MDY, four digit
> years to yes, Date delimter to nothing, leading zeroes in dates as
> required, time delimiter to nothing.
> Now save the import specification with a meaningful name. click ok.
> click next. choose destination then click next, add a key if desired,
> click next, click next, click finish.- Hide quoted text -
>
> - Show quoted text -

Looks like it was the date delimter that I had incorrect. I was
leaving that at the default instead of making it blank. Thanks for
help!