DateValue stopped working?

[bb29829]

I have a text string that starts with a date. I am attempting to extract the date portion and then have the query see that text as a date in order to perform a calculation. This was working a week ago so I am not sure what happened.

Left([SRS_Degrees],10) gives me 12/15/2001

Field 1: DateValue(Left([SRS_Degrees],10)) I get "Data type mismatch in criteria
expression"

Purpose of this is to run the calculation in Field 2.

Field 2: (Date()-([Field 2]))/365)

Any advice? Thanks in advance!

 

[plog]

I have a text string that starts with a date

Turns out that's not true. Replace DateValue with IsDate and run again. The one that produces false is the culprit.

 

[bb29829]

Yes I have a lot of -1's but what is the right function to turn that into a date. If DateValue isn't working, what should I use?

 

[plog]

Date value is the correct function. You need to fix your data:

OrderDate: DateValue("February 30, 2011")
OrderDate: DateValue("Aprli 10, 2016")
OrderDate: DateValue("12/12")
OrderDate: DateValue("This Is Definitely Not A Date")

You have bad data that DateValue doesn't know what to do with. Additionally, you are extracting your date from a longer string in the process you assume its 10 characters. Suppose its not:

"1/1/16 David Ordered More"

The first 10 characters of that is "1/1/16 Dav" which DateValue can't parse.

 

[bb29829]

Thanks for your help. I re-wrote the query and it works fine. Don't know what happened. I guess it was corrupt in some way. Thanks again for your help. You guys never let me down!