Update Query IIF not updating one combination of characters. The Nested part works.

[AccessHack]

I have an update query that converted a field location from a LETTER NUMBER NUMBER LETTER entry to the word "STORES". This worked perfectly, until the plant added new locations. The new locations are LETTER NUMBER NUMBER NUMBER LETTER entries. This seems simple enough.

IIf([FROM LOCATION] Like "?##?","STORES",IIf([FROM LOCATION] Like "?###?","STORES",[FROM LOCATION]))

No matter WHAT I have tried, the X###X with three digits in the middle will NOT update. I have tested the IIF with it in both locations, the 2 digit works no matter where, the 3 digit does not. I went back to just the original query, and tried to do just the 3 digit entries - no luck.

I have tried replacing the "?" with [a-z], using underscores, using nothing but "?" - and it HATES ME!

This has to be simpler than it is letting on, but I cannot find the solution. I have spent hours making sure the IIF works properly, and thanks to many entries on this forum, I think it does. It's just the 3 digit update that does not.

[June7]

I tested that expression and it works.

Also, a simpler version:

IIf([FROM LOCATION] Like "?##?" Or [FROM LOCATION] Like "?###?", "STORES", [FROM LOCATION])

Post your actual SQL statement.

Or provide db for analysis.

[AccessHack]

I hope I have the zipped attachment for you. I have included on the query and table in question. If that isn't enough, please let me know. Thank you.

[AccessHack]

I forgot to metntion that the data in Jan 6 - 9 has examples of the numbers to fix - makes it quicker to check. Thank you.

[rpeare]

how about:

iif(instr([from location], "##") >0, "STORES", [FROM LOCATION])

assuming of course the ## appears only in records you want to update

[rpeare]

By the by, there's nothing with those strings in your sample data, is there a reason you're putting a date criteria in your update query? wouldn't it be important to update all of your data that string exists? I've never quite seen criteria in an update done the way you have it so it made me curious, especially since it's asking to enter a date on a field that's text.

[AccessHack]

Thanks, but I have locations also that I DON'T convert, and they exist as ##, ###, and text that varies from 3 - 6 characters. The ONLY ones I have to change are those with the LETTER NUMBERS LETTER combo. I probably should have said that, but I'm going to use my frustration and first time posting as an excuse. I appreciate the feedback tho!

[AccessHack]

Acutally, the first time I made the Update Query, it was just from a select query with all the table fields. I normally only wanted to update the new records, and not mess with history. When I turned it into the Update, that is what Access did with my date range, and since the query always worked up until this 3 digit issue, I never questioned it. My history with Access is that it will do lots of things I may not quite understand.

[rpeare]

so use this:

iif(left([from location], 2) = "?#", "STORES", [FROM LOCATION])

and take the criteria out of there altogether

this is saying any from location that starts with ?# change to "STORES" otherwise use the existing from location

[rpeare]

So this:

UPDATE [Scrap Ticket] SET [Scrap Ticket].[FROM LOCATION] = IIf(Left([from location],2)="?#","STORES",[From Location]);

or this:

SELECT [Scrap Ticket].[FROM LOCATION]
FROM [Scrap Ticket]
WHERE ((([Scrap Ticket].[FROM LOCATION]) Like "?##?" Or ([Scrap Ticket].[FROM LOCATION]) Like "?###?"));

[June7]

Criteria for a date range should not be under the [FROM LOCATION] field. Should be under [TRANS DATE] field. Seems to me the filter criteria is unnecessary. I removed it and ran the UPDATE and it worked. Even the ?###? value changed.

UPDATE [Scrap Ticket] SET [Scrap Ticket].[FROM LOCATION] = IIf([FROM LOCATION] Like "?##?" Or [FROM LOCATION] Like "?###?","STORES",[FROM LOCATION]);

[AccessHack]

Criteria for a date range should not be under the [FROM LOCATION] field. Should be under [TRANS DATE] field. Seems to me the filter criteria is unnecessary. I removed it and ran the UPDATE and it worked. Even the ?###? value changed.

UPDATE [Scrap Ticket] SET [Scrap Ticket].[FROM LOCATION] = IIf([FROM LOCATION] Like "?##?" Or [FROM LOCATION] Like "?###?","STORES",[FROM LOCATION]);

Hello again - got taken away from this, and made some changes, and did some review. I copied all the tables and queries and such to an Access 2010 database and started again. This one originally got built with 2007, and I didn't remember that. Since I found that helped another problem in another database, I figured that could not hurt. Then, I started using your solutions one by one, and kept eliminating things until I ended up with this - which actually works:

IIf([FROM LOCATION] Like "[A-Z]##[A-Z]" Or [FROM LOCATION] Like "[A-Z]###[A-Z]","STORES",[FROM LOCATION]

It seems that the "?##? worked for so long because the ONLY data that was a location and four CHARACTERS always happened to be the X##X pattern. The "?" is any CHARACTER, and I only got lucky and found that because they also added a four DIGIT location. Just one. So my query that worked perfectly for 1 1/2 year was simple luck.

But thanks to your help and input, I was able to keep breaking it down until I finally found the way to express the rule for what I really have. Thank you all so much. OH, and that date range was moved out of the way first off. Thanks.