Text string containing date to date

[keesey]

Hi,I currently have a MS Access database that has a column with contains and an events description and also the date on which the event will occur. ie "My Event 21st June 2008". From the description I would like to extract the date.I have tried using:SELECTProductref,(CASE WHEN description like '%1st%' THEN 1, ....., ELSE 0 END) AS 'Day'FROM Product;..... repeats it over and over for each dayThis works when done in sql server 2005 on a different datebase I have access to.I have also tried:SELECTproductref,Switch (description LIKE '* 1ST*', "1",....)FROMPRODUCT;This works until the 14th and then after that it will fail as it is too complex an expression.Does anyone have any suggestions?Thanks in advance

[justsomeguy]

Access isn't designed for this type of thing. You will either need to split up your expression across several sub-expressions with ORs between them, or the good solution would be to add a date field with the date you're looking for instead of using substring searching. It's going to take much longer to do it this way than it would if you had a normal date field.

[keesey]

Access isn't designed for this type of thing. You will either need to split up your expression across several sub-expressions with ORs between them, or the good solution would be to add a date field with the date you're looking for instead of using substring searching. It's going to take much longer to do it this way than it would if you had a normal date field.

The only problem is that the database design needs to be left alone as it is from a piece of e-commerce software. There would be no way to enter the date within the application when a new event is added and so they would rely on me entering data into the database manually. Currently 6000 events in the database and so I was trying to find a quick way of what whas happening each day.I am going to try your suggestion and use several sub-expressions. Thank you