Update Query 6

[thirty4d]

Hello,

I have a table with about 1500 records that was originally a list of price quotes from one of our supplier then imported from Excel.

About 700 of the records have a partnumber that looks like, Example: 02-25-02323 Total and about 700 have a normal partnumber 03-25-03625.

I did a select Query to pick out all the records that has a partnumber with the word Total at the end. That part was fine.

I wanted to do an Update Query To but I'm stuck.

I didn't know how to tell Access to keep whatever the number but remove the string Total. I have a good idea but don't exactly know the correct expression.

Help?

Thanks

 

[JerryDennison]

Assuming the part number is ALWAYS 11 characters long you can use the following in the update to property:

=Left([partnumber],11)

 

[RickSpr]

If the part number ISN'T always 11 characters, but "TOTAL" is always preceded by a space, you can use this:

=Left([partnumber], InStr([partnumber] & " ", " &quot - 1) Rick Sprague

 

[calian]

If the string " Total" is always the same at the end, and is all you want to clip, this should work, too.

=left([partnumber]), len([partnumber])-6))

 

[scking]

I think what we are trying to say is that you need to determine what about the pn is always stable or mostly stable and write the code to manage it. NEVER trust data to be ALWAYS LIKE but write the code to handle any data which does not or may not conform to what you are assuming. This is particularly important in data from another source when we do not control what is put into the fields. Trust Not Lest Ye Be S[hafted------] is my saying. I guess I'm saying this after having made the mistake a couple times myself but found it in testing (sometimes).

Steve King