Change Data type in Make Table qry

[Guest]

I have a query that uses this; EST Date: Right('00' &
DatePart("m",[LISTMADD]),2) & "/" & Right('00' & DatePart("d",[LISTMADD]),2)
& "/" & DatePart("yyyy",[LISTMADD]) to extract a date out of the data I
have. When I make a table with the results the new field (EST Date) is
labled as "text".
Is there a way to have the field go to "date/time" so in my next query I can
use the date functions to pull data over 1 year old?

 

[John Spencer]

You could try using the DateValue function or CDate to surround the
calculated value and force it to a DateTime type.
Also, I would use EstDate or (Est_Date) not Est Space Date as the field
name. Spaces and other special characters in field and table names tend to
cause problems in queries and code.

 

[Guest]

Toolman,

What is in your LISTMADD field? Does it have a date data type? IF so, why
are you trying to parse it like you are?

You might try using the CDATE function to encapsulate your entire
combination of RIGHT(DATEPART .........), but I don't think cdate is going to
work with the potential of having a month and day of 00/00?

Can you provide some samples of what is in LISTMADD?

Dale

 

[Guest]

Here is the format of the field I change. 2/8/2006 1:35:12 PM
I was trying to query just off the date and not the time and this was the
suggestion I got a few months back when I asked.
I have a query that looks for items added since "date specified". Maybe
there is a better way but this is working.

Toolman,

What is in your LISTMADD field? Does it have a date data type? IF so, why
are you trying to parse it like you are?

You might try using the CDATE function to encapsulate your entire
combination of RIGHT(DATEPART .........), but I don't think cdate is going to
work with the potential of having a month and day of 00/00?

Can you provide some samples of what is in LISTMADD?

Dale

I have a query that uses this; EST Date: Right('00' &
DatePart("m",[LISTMADD]),2) & "/" & Right('00' & DatePart("d",[LISTMADD]),2)
& "/" & DatePart("yyyy",[LISTMADD]) to extract a date out of the data I
have. When I make a table with the results the new field (EST Date) is
labled as "text".
Is there a way to have the field go to "date/time" so in my next query I can
use the date functions to pull data over 1 year old?

 

[Guest]

DateValue worked!
Thanks for the help.

You could try using the DateValue function or CDate to surround the
calculated value and force it to a DateTime type.
Also, I would use EstDate or (Est_Date) not Est Space Date as the field
name. Spaces and other special characters in field and table names tend to
cause problems in queries and code.

I have a query that uses this; EST Date: Right('00' &
DatePart("m",[LISTMADD]),2) & "/" & Right('00' &
DatePart("d",[LISTMADD]),2)
& "/" & DatePart("yyyy",[LISTMADD]) to extract a date out of the data I
have. When I make a table with the results the new field (EST Date) is
labled as "text".
Is there a way to have the field go to "date/time" so in my next query I
can
use the date functions to pull data over 1 year old?