Date Conversion Problem

[traceytr]

Hello. We have changed string data to look like a short date format as follows, but when we add parameters to look for dates between a BeginDate and EndDate, we find this field is not being treated like a date and returns the wrong values. We have looked and looked for a solution. Can someone explain the error of our ways? We have set the property of this field as Short Date, but still it doesn't work. We have also used DateValue in place of CDate. No luck with that either.

Expr2: CDate(Left([Next Maturity Date],Len([Next Maturity Date])-4) & "/" & Left(Right([Next Maturity Date],4),2) & "/" & Right([Next Maturity Date],2))

Thanks in advance.

Tracey

 

[PHV]

To play with dates nothing is better than real DateTime fields and real Date variables.

Hope This Helps, PH.
Want to get great answers to your Tek-Tips questions? Have a look at FAQ219-2884 or FAQ181-2886

 

[enuss]

What is in the field [Next Maturity Date]? What data type is it? I am assuming a string. What does a typical record in this field look like?

 

[traceytr]

Next Maturity Date is a text field. Here are some examples of the values:

121203
100304
22604

 

[traceytr]

How do I convert this to a DateTime field? Thanks.

 

[enuss]

I just tested your code in my own test database and it works. It is returning this:

Expr2
12/12/2003
10/03/2004

What is your's returning?

By the way, it would be best to simply store actual dates, rather than text. But you probably already know that.

 

[traceytr]

She is pulling these text "dates" in from another source so we're kind of stuck with the text. The conversion does make these text "dates" look like dates. 12/12/2003, etc. It is when we add the parameters BETWEEN [StartDate] and [EndDate] that the field does not act like a date field. It doesn't return the correct records. Also, if I enter one date as the criteria and arrow off of the criteria row, Access does not add the # around the outside of the date as it normally would with a date field. Strange.

Tracey

 

[enuss]

Ok, I see your problem. Try using the following Criteria, instead of the "BETWEEN" function.

>=[StartDate] And <=[EndDate]

This should work.

 

[traceytr]

I tried this, and it does not work. I don't think it's the BETWEEN function that is causing the problem. If I enter in the Criteria row the following value 12/04/04 in a valid date field and move off of the row, Access will change the date I just entered to look like #12/4/2004#.

In this CDate field if I enter the same date 12/04/04 in the Criteria row and then move off of the row, Access changes the date I entered to look like 12/4/4. Doesn't add these # in either.