Date/Time Text.

[Dan]

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

 

[Ryan]

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"

 

[Dan]

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.

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.

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

 

[Ryan]

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.

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.

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.

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

 

[Douglas J. Steele]

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)

 

[Dan]

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

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

(no private e-mails, please)

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