Dealing with #error in query

[Matt_at_Target]

I am using the following to extract date information from a text field as part of a query.

Narrr_Date:CDate(Left(Mid([si_narr_t],Instr([si_narr_t]," ")+1), Instr(Mid([si_narr_t],Instr([si_narr_t]," ")+1)," ")-1))

Due to users not putting the correct information in I will get the #Error in the results of the query.

I need to do a DateDiff on the results so I need to get rid of any values that come up with #Error.

Since I cannot ensure the date extracted by the query is correct, how do I deal with the error?

 

[DJkarl]

How is the wrong data ending up in the table to begin with? I'd start there and correct the data entry method to prevent these user mistakes, then your query will work.

I only suggest this because you may come up with a method to solve you current problem, but then a user will enter something you haven't anticipated and you will get the Error again.

 

[Matt_at_Target]

Alas, this is a situation where the date is part of a narritive in a text field. And while the date appears in the same place, and I can extract it, if someone puts in say 3/12/200Q by accident, I have no way to account for it.

 

[Rabbie]

Alas, this is a situation where the date is part of a narritive in a text field. And while the date appears in the same place, and I can extract it, if someone puts in say 3/12/200Q by accident, I have no way to account for it.

If there is always a date embedded as part of the narrative in that text field you need to check it at input so you don't get 3/12/200Q. I am sure you will remember the old computer Maxim GIGO Garbage in Garbage out.