InStr Update Query

[MTSPEER]

Hello,

I am wondering why this update query won't work for me?

UPDATE Producer_World SET Producer_World.Body = " "


WHERE (((Producer_World.Body)=Left([Body],InStr([Body],"Date Request"))));


Thanks

[ranman256]

does the SELECT query return values based on:
WHERE (((Producer_World.Body)=Left([Body],InStr([Body],"Date Request"))));

if not, nor will the update query.

[JoeM]

Perhaps you could explain exactly what it is supposed to do and post some of your data?

[MTSPEER]

Yes it does. It returns everything left of "Date Request"

[MTSPEER]

I want it to delete everything left of "Date Request" in the field "Body"

[JoeM]

I am confused by your criteria.
You are comparing the Body field to itself?
I think we really need to see some examples of what you data looks like, and what you want your expected result to look like.

[June7]

Your SQL is attempting to set Body to a single space if string contains "Date Request", perhaps you meant an empty string?

UPDATE Producer_World SET Producer_World.Body = "" WHERE InStr([Body], "Date Request")>0;

I don't allow empty string in fields. I would set to Null.

[JoeM]

I want it to delete everything left of "Date Request" in the field "Body"

Your SET statement is setting it equal to a single space, not cutting part of the string out.

If you truly just wanted to drop everything before the phrase "Date Request", try this:

UPDATE Producer_World SET Producer_World.Body = Mid([Body],InStr([Body],"Date Request"),Len([Body]))
WHERE (((InStr([Body],"Date Request"))>0));

[MTSPEER]

JoeM you da man! That worked! Thanks!

[JoeM]

You are welcome.