Converting to date

[Kenln]

I have a table that stores dates like this: 200902015 i.e. 2/15/2009

In a query I am trying to convert that to a date.

I am using

Week_Ending: 
IIf(
Nz(view_Last_Labor!Max_Week_Ending)<>"",
CDate(
Trim(Str(Val(Mid$(view_Last_Labor!Max_Week_Ending,5,2)))) 
& "/" & 
Trim(Str(Val(Right$(view_Last_Labor!Max_Week_Ending,2)))) 
& "/" & 
Left$(view_Last_Labor!Max_Week_Ending,4)
)
,""
)

It looks good,
It looks like it is working
In the next field I even have a DataAdd to this Expression and it seems to work.

But when I sort it the order is all messed up.

Any ideas?


Thank you,

 

[Kenln]

I found another piece of the puzzle. it is sorting as a string rather that a date.
i.e.
1/15/2008
1/24/2009
10/16/2009
11/22/2008
12/18/2005
2/15/2009

So... even though I used CDate() it still seems to view it as a string and sorts it that way.

 

[JohnLee]

Hi,

Is the field in which you are converting your date of date type, if not then that would be why it's treating it as a string and not a date.

John

 

[pbaldy]

I suspect the zero length string in the false argument of the IIf is causing the problem. Try changing that to 0 or Null.

 

[Kenln]

Cool,

Null worked first time.

Thank you very very much!!!

 

[pbaldy]

No problem; been there, done that, got the tee-shirt.

 

[raskew]

Where in this world do folks store date like: 200902015? Seems about as bright as this one http://www.access-programmers.co.uk/forums/showthread.php?t=166264&highlight=century. But then, what do I know?

Just asking - Bob

 

[Kenln]

The data comes from a business system on an iSeries (AS-400 Main Frame) running DB2.

Some of the date fields have been updated. Most are still YYYYMMDD format.

 

[raskew]

Out of curiosity, how does that business system deal with its dates?

Bob

 

[Kenln]

The Business System we are on is a purchased system for construction companies. I am not sure how in deals with dates under the hood.

I know that most of the older tables use dates like YYYYMMDD for Posting, Journal Enteries, etc....

I'm on the other end. Access front end reteriving and displaying data.

Sorry, that's the best I can do.