uHi all, I am pulling some data through ODBC and one of the feilds I pull is
a datetimestamp which comes across as text and looks like this 2006-07-03
08:45:14.571000 What I am trying to do is pull this exact info in a make
table query but have it be a Date/Time format instead of text. I have tried
CDATE which works for pulling out just the date as a date field, but I am not
able to get it to include the times also. Any help on this would be great.
Thanks

Try this in your make table query.
FormatedDate:Format$([YourDateField],'mm/dd/yyyy hh:mm:ss')
Then right click on the column in your query and set its format to "short
date"
--
Please remember to mark this post as answered if this solves your problem.


"Dan" wrote:

> uHi all, I am pulling some data through ODBC and one of the feilds I pull is
> a datetimestamp which comes across as text and looks like this 2006-07-03
> 08:45:14.571000 What I am trying to do is pull this exact info in a make
> table query but have it be a Date/Time format instead of text. I have tried
> CDATE which works for pulling out just the date as a date field, but I am not
> able to get it to include the times also. Any help on this would be great.
> Thanks

When, I use the "Format" it leaves the Data Type as Text instead of changing
it to DATE/TIME in the table. I need the data in the table to be DATE/TIME.
This is why I have used CDATE in the past because it changes the Data Type to
Date/Time.

"Ryan" wrote:

> Try this in your make table query.
> FormatedDate:Format$([YourDateField],'mm/dd/yyyy hh:mm:ss')
> Then right click on the column in your query and set its format to "short
> date"
> --
> Please remember to mark this post as answered if this solves your problem.
>
>
> "Dan" wrote:
>
> > uHi all, I am pulling some data through ODBC and one of the feilds I pull is
> > a datetimestamp which comes across as text and looks like this 2006-07-03
> > 08:45:14.571000 What I am trying to do is pull this exact info in a make
> > table query but have it be a Date/Time format instead of text. I have tried
> > CDATE which works for pulling out just the date as a date field, but I am not
> > able to get it to include the times also. Any help on this would be great.
> > Thanks

MyDate = "October 19, 1962" ' Define date.
MyShortDate = CDate(MyDate) ' Convert to Date data type.
MyTime = "4:35:47 PM" ' Define time.
MyShortTime = CDate(MyTime) ' Convert to Date data type.
--
Please remember to mark this post as answered if this solves your problem.


"Dan" wrote:

> When, I use the "Format" it leaves the Data Type as Text instead of changing
> it to DATE/TIME in the table. I need the data in the table to be DATE/TIME.
> This is why I have used CDATE in the past because it changes the Data Type to
> Date/Time.
>
> "Ryan" wrote:
>
> > Try this in your make table query.
> > FormatedDate:Format$([YourDateField],'mm/dd/yyyy hh:mm:ss')
> > Then right click on the column in your query and set its format to "short
> > date"
> > --
> > Please remember to mark this post as answered if this solves your problem.
> >
> >
> > "Dan" wrote:
> >
> > > uHi all, I am pulling some data through ODBC and one of the feilds I pull is
> > > a datetimestamp which comes across as text and looks like this 2006-07-03
> > > 08:45:14.571000 What I am trying to do is pull this exact info in a make
> > > table query but have it be a Date/Time format instead of text. I have tried
> > > CDATE which works for pulling out just the date as a date field, but I am not
> > > able to get it to include the times also. Any help on this would be great.
> > > Thanks

Access will only work with hh:mm:ss, not fractional seconds.

Try importing it as text, then adding a date field, populating it using

CDate(Left([TextDateField], InStr([TextDateField], ".") - 1)

Of course, that assumes that every field has the fraction seconds. If that's
not the case, you may need to use

CDate(IIf(InStr([TextDateField], ".") = 0, [TextDateField],
Left([TextDateField], InStr([TextDateField], ".") - 1)

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


"Dan" <Dan@discussions.microsoft.com> wrote in message
news:F7F55FD1-F54D-4D17-A493-538D4F7578E8@microsoft.com...
> uHi all, I am pulling some data through ODBC and one of the feilds I pull
> is
> a datetimestamp which comes across as text and looks like this 2006-07-03
> 08:45:14.571000 What I am trying to do is pull this exact info in a make
> table query but have it be a Date/Time format instead of text. I have
> tried
> CDATE which works for pulling out just the date as a date field, but I am
> not
> able to get it to include the times also. Any help on this would be great.
> Thanks

Doug, As always it worked like a charm. Thanks for your help.

"Douglas J. Steele" wrote:

> Access will only work with hh:mm:ss, not fractional seconds.
>
> Try importing it as text, then adding a date field, populating it using
>
> CDate(Left([TextDateField], InStr([TextDateField], ".") - 1)
>
> Of course, that assumes that every field has the fraction seconds. If that's
> not the case, you may need to use
>
> CDate(IIf(InStr([TextDateField], ".") = 0, [TextDateField],
> Left([TextDateField], InStr([TextDateField], ".") - 1)
>
> --
> Doug Steele, Microsoft Access MVP
> http://I.Am/DougSteele
> (no private e-mails, please)
>
>
> "Dan" <Dan@discussions.microsoft.com> wrote in message
> news:F7F55FD1-F54D-4D17-A493-538D4F7578E8@microsoft.com...
> > uHi all, I am pulling some data through ODBC and one of the feilds I pull
> > is
> > a datetimestamp which comes across as text and looks like this 2006-07-03
> > 08:45:14.571000 What I am trying to do is pull this exact info in a make
> > table query but have it be a Date/Time format instead of text. I have
> > tried
> > CDATE which works for pulling out just the date as a date field, but I am
> > not
> > able to get it to include the times also. Any help on this would be great.
> > Thanks
>
>
>