Hi,
I have a field called [Item] that I am pulling from a linked Excel table where multiple people are entering data over multiple years. The Item is a string of numbers of varying lengths and values. ex) 636, 188880921
I call on [Item] in a query --> [ItemNo]:[Item] & "000" and add three 0's because the Item must end with 000 so I can dlookup off of it. The people were not putting the 000 in on the excel spreadsheet.
So this worked out fine, until recently the people inputting in Excel decided to start adding the 000 in. To be a robust system in case they change their minds again, I'd like to be able to handle both scenarios. I do not know how to proceed though to evaluate if a 000 already exists at the end of the number or if it does not add the 000. I assume some sort of Iif statement would be required.
Sorry for the long message, but any help would be much appreciated.
Thanks!!!
I have a field called [Item] that I am pulling from a linked Excel table where multiple people are entering data over multiple years. The Item is a string of numbers of varying lengths and values. ex) 636, 188880921
I call on [Item] in a query --> [ItemNo]:[Item] & "000" and add three 0's because the Item must end with 000 so I can dlookup off of it. The people were not putting the 000 in on the excel spreadsheet.
So this worked out fine, until recently the people inputting in Excel decided to start adding the 000 in. To be a robust system in case they change their minds again, I'd like to be able to handle both scenarios. I do not know how to proceed though to evaluate if a 000 already exists at the end of the number or if it does not add the 000. I assume some sort of Iif statement would be required.
Sorry for the long message, but any help would be much appreciated.
Thanks!!!
