CODEWITHSUNDEEP
cms-accessvba
David Kasabji
David Kasabji

Reputation: 1069

MS Access: Search through Table for the same part of the String

I am trying to loop through Column Values inside my Table. I have a register form, which provides the user with UNIQUE ID, based uppon his information. For example: Country = Austria
Each user that selects country Austria will get some sort of Unique Value for that match (lets say 00).

Account ID look like this:

XXXX00UNIQUECODE

Each country has it´s own unique value: (AT = 00, DE = 01, etc)

Now, I want to generate a UNIQUE CODE for each user, that will be just an increment (+1) value of the previous UC value stored in the table, for the same country!

In order to do that, I need to somehow loop through the Column, where the Account IDs are stored and search for the match. The thing is, when a user tries to generate the UNIQUE CODE, he does not have it yet, so he has only:

XXXX00

Now I need to find all the XXXX00 strings in my AccountID Column, and store them in an Array - then find the Max Value of those and increment it.

BUT I dont know how to search for a part of the string inside a Column of the Table ? Just the XXXX00 part, not the entire Account ID XXXX00UNIQUECODE.

Agh, I hope you can understand me. It´s quite complicated I know, but I´m really stuck here. Hopefully, someone will know what I mean and maybe even find a smoother solutions for this.

Thanks in advance!

Upvotes: 0

Views: 49

Answers (1)

Johnny Bones
Johnny Bones

Reputation: 8414

You're pounding a square peg into a round hole. Why not just create a new column called UserID and then you can do:

SELECT Max(UserID) FROM MyTable WHERE Mid(AccountID, 5, 2) = "00"

and increment it by 1.

Better yet, store CountryCode, UserID and the XXXX part in separate fields, and index them. It'll save time when you search or filter, which I'm assume you're going to be doing.

Upvotes: 1

Related Questions