Pulling out all information after the last space

[dprichard]

I have a table that someone created that for whatever reason they put the text CustID= into the UVAR field instead of just the ID number. The other thing that I noticed is that there several spaces between CustID= and the customer number. So, it may be in one time as CustID= 12345 and the next time as CustID= 23454 with several spaces. I am trying to figure out how to pull out just the numbers after the last space in the field.

I was looking for some information on string functions or expressions for Access 2003, but wasn't finding a complete list with what they do. Any help would be greatly appreciated.

Thank you!

 

[pbaldy]

If the number is consistently 5 digits, you could use the Right() function. You can find the position of the last space with InStrRev, and use that in conjunction with the Mid() function to pull out the data.

 

[dprichard]

It is unfortunately not always 5 digits. It starts with 1 and goes up to 565000 with no leading zeros.

 

[dprichard]

Ah, I see what you are saying Mid(UVAR, InstrRev(UVAR, " "))

 

[Scooterbug]

You can use the InStr() function to find the placement of the space. Then use that to extract the digits to the left of that.

YourNumber = left(YourFieldName,Len([FieldName])-InStr(" ",[FieldName]))

 

[pbaldy]

That should work for you dprichard.

Scooter, you need a break.

 

[Scooterbug]

Scooter, you need a break.

End of the day and it's slow Working on converting our database to Sql Server...and from time to time I need a break from staring at converting the front end

 

[dprichard]

pbaldy, thanks for the info. That worked perfectly. Here is the complete update statement. Got to use this trick twice!!! Thanks again.

UPDATE Users INNER JOIN UserIdInfo on Mid(Users.ID, (InstrRev(Users.ID, " ") + 1)) = UserIdInfo.OldUserId
SET Users.ID = Mid(Users.ID, 1, (InstrRev(Users.Id, " "))) & [UserIdInfo].[NewUserId];

 

[dprichard]

Okay, so I am running into a problem. I ran this in a test environment on my machine and it worked fine with a small sample of data in Access 2007, but when I ran it on the production environment on Access 2003 I got the following error.

Data Type Mismatch in criteria expression

I checked and all the fields involved are text fields. Any ideas?

 

[pbaldy]

Might any be Null, or not contain any spaces?

 

[dprichard]

Yeah, I tried filtering out NULL with no luck. How would I say if it contains a space?


UPDATE Users INNER JOIN UserIdInfo on Mid(Users.ID, (InstrRev(Users.ID, " ") + 1)) = UserIdInfldUserId
SET Users.ID = Mid(Users.ID, 1, (InstrRev(Users.Id, " "))) & [UserIdInfo].[NewUserId] WHERE Users.ID <> NULL;

 

[pbaldy]

This

WHERE Users.ID <> NULL

would have to be

WHERE Users.ID Is Not NULL
or
WHERE Not IsNull(Users.ID)

 

[dprichard]

Sweet, that was it. Worked perfectly. Updated all 65k records. I really really appreciate your help!

 

[pbaldy]

No problemo, glad we got it sorted out.