Convert Number Field to Date using query

[tecampbell]

I have search the forum and found several people asking this question,
but my situation is a little different. I have a database that I need
to convert a number field to a date, but the number fields have the
date as "08/07/2007". I have not been able to find a way to convert
this number field to a date.

Any suggestion/comments would be greatly appreciated!

Thanks

 

[Douglas J. Steele]

You sure it's a numeric field? Numeric fields wouldn't have slashes in them.

If it's text, you can use the CDate function.

 

[tecampbell]

Yes I am sure it is a number field...so I don't know why it is
allowing the '/'? I have tried the CDate function, but I receive the
error "Data type mismatch in criteria expression." When I remove the
column (master date id) from the express and add a 1, for example, it
will run the query? Below is the criteria and am trying to
accomplish:

DateDiff("m",CDate("master date id"),Date()) And Between -18 and 0

Basically I am trying create a query that will only display the rows
that match the "master date id" 18 months prior to today's date. I
work with SQL more than Access, so I am not sure my syntax is correct
for Access???

Thanks for the suggestion.

 

[Douglas J. Steele]

Where are you doing this: in code, or in a query?

If it's in a query, try DateDiff("m",CDate([master date id]),Date())

You can't put the DateDiff statement together with "And Between -18 and 0".

If you trying to compare all values of master date id to today's date and
only return those that less than or equal to 18 months old. you need to put
the DateDiff statement as a computed field, and then put "Between -18 and 0"
as the criteria under that computed field.

 

[John W. Vinson]

I have search the forum and found several people asking this question,
but my situation is a little different. I have a database that I need
to convert a number field to a date, but the number fields have the
date as "08/07/2007". I have not been able to find a way to convert
this number field to a date.

Any suggestion/comments would be greatly appreciated!

Thanks

As Douglas says, that doesn't look like a Number field. What's the Format
property of the field in table design view? How about its Input Mask? If it is
a number datatype, what kind of number - Long Integer?

John W. Vinson [MVP]

 

[tecampbell]

After digging further I found a relationship with another table and
that table has the date/time field I am looking for.

Doug - thanks for the advice on the adding a the expression in the
Field: of the query along with the criteria below. I have the results
I am looking for!

Thanks again!