Jump to content

Text string containing date to date


keesey
 Share

Recommended Posts

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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 :)
Link to comment
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now
 Share

×
×
  • Create New...