Convert Text to Date

[mjfigur]

Ok I am pretty new to access and was given a large database to work on with a prior developer leaving the company. I have a field named: Demand Date that comes over from a linked table in text format (ex. 1152009). While working in my select query I need to convert this to a date format (mm/dd/yyyy) to subtract another date field from this date and determine if a new demand is needed.

I have read some things over the past few hours on CDate, DateSerial but not sure how to put these in my select query and I continue to get compile errors when I try..

Any help would be huge just cannot seem to figure this out.

 

[Galaxiom]

Please clarify the date text string format
1152009 is ambiguous as it could be Jan15, Nov5 or May11 depending on format

For example I would expect: 11052009 for May11

 

[mjfigur]

Sure thing sorry about that it is currently mmddyyyy but in text format

 

[Galaxiom]

This should do what you want:
DateSerial(Right([Demand Date],4),Left([Demand Date],2) Mid([Demand Date],3,2))

DateSerial returns in your local date format.

 

[raskew]

Hi -

If your example (1152009) is accurate, rather than 01152009 or 11052009, I think you've got a serious problem unless there's some other indicator in your table that would allow you to distinguish between 15-Jan-2009 and 5-Nov-2009.

Here's how the DateSerial() function could be used:

x = "1152009"
? dateserial(right(x,4), left(x,2), mid(x,3,1))
11/5/2009

...but here again, I've made an unsupported assumption that the date represents November and not January.

More info would be helpful.

Bob

 

[mjfigur]

Thank you for your help I will have to go in this morning and make sure there is consistancy is the data coming in so that it would not confuse the DateSerial.

Just one other question when I try and input this:
DateSerial(Right([Demand Date],4),Left([Demand Date],2) Mid([Demand Date],3,2))

Do I place it right in the Field section of the Select Query Design? So that it would read

Demand Date: DateSerial(Right([Demand Date],4),Left([Demand Date],2) Mid([Demand Date],3,2))

Just getting syntax errors and not sure why.

 

[namliam]

You cannot use a new name (alias) with the same name as an already existing column name.... Try changing it to:
Real Demand Date: DateSerial(Right([Demand Date],4),Left([Demand Date],2) Mid([Demand Date],3,2))