MS Access Variable Replace command based on lenght of input

The input for the search criteria of a filter search box can be 5 to 7 characters long.  The field in the source table is 12 characters long starting with "A" and filled with zeroes depending on the length of the number i.e.:

 8808            A00000008808    7 zeroes
157450         A00000157450    5 zeroes

I want to modify the following statement to accomodate this:

Me.Filter = "[AccountNumber] = '" & _
                      Replace(Me!cmbPtInfo.Text, "'", "''") & "'"
    Me.FilterOn = True

What is the syntax?


Thanks

Glen
GPSPOWAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Dale FyeOwner, Developing Solutions LLCCommented:
How about simply:

Me.Filter = "[AccountNumber] Like '*" & me.cmbPtInfo & "'"
me.FilterOn = true
0
GPSPOWAuthor Commented:
I thought of that, but I could have a more than one occurrence of a string less than 6 characters such as:

157645

57645

What would be the syntax for a an IF or Case statement depending on the length of the input and then fill the rest of the string with the correct amount of zeroes and the leading "A"?

Thanks

Glen
0
Dale FyeOwner, Developing Solutions LLCCommented:
Me.Filter ="[AccountNumber] = 'A" & Right(string(11, "0")  & me.cmbPtInfo) & "'"
0
Protecting & Securing Your Critical Data

Considering 93 percent of companies file for bankruptcy within 12 months of a disaster that blocked access to their data for 10 days or more, planning for the worst is just smart business. Learn how Acronis Backup integrates security at every stage

Dale FyeOwner, Developing Solutions LLCCommented:
Basically, this creates a string that looks like:

00000000000#####

and then takes the last 11 characters and appends it to the letter A
0
GPSPOWAuthor Commented:
Dale,

The # of zeros varies depending on the length of the inputted number in the combo box.  If I use this will it not just append 11 zeros after the "A" regardless of the length of the input?

Or is it something like

if len(cboPtInpt.Text) = 6 then
      Me.Filter ="[AccountNumber] = 'A" & Right(string(5, "0")  & me.cmbPtInfo) & "'"
end if



Glen
0
aikimarkCommented:
You could also use the Like operator instead of an equality check:
Me.Filter = "[AccountNumber] Like  'A0*0" & _
            Replace(Trim(Me!cmbPtInfo.Text), "'", "''") & "'"

Open in new window


I added the Trim() function, just in case your Replace() function indicated possible dirty data.  If it is clean, you could use this:
Me.Filter = "[AccountNumber] Like  ""A0*0" & Me!cmbPtInfo.Text & """"

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Dale FyeOwner, Developing Solutions LLCCommented:
No, when you use the String() function, in conjunction with the Right() function you will get the right most 11 characters.  Try this in your immediate window:

strInfo = "1234"
?Right(String(11, "0) & strInfo, 11)
00000001234

strInfo = "12345"
?Right(String(11, "0) & strInfo, 11)
00000012345
0
aikimarkCommented:
You can also create the exact number of zero characters you need.  Use the Len() and the String() functions.
Examples:
n="157450"
x="A" & string(11-len(n),"0") & n
?len(x),x
 12           A00000157450

n="8088"
x="A" & string(11-len(n),"0") & n
?len(x),x
 12           A00000008088

Open in new window

0
plummetCommented:
Dale is correct here, and his first example will give you exactly what you want though his last example is missing a quote character, so:

strInfo = "1234"
?Right(String(11, "0") & strInfo, 11)
00000001234

strInfo = "12345"
?Right(String(11, "0") & strInfo, 11)
00000012345
0
Dale FyeOwner, Developing Solutions LLCCommented:
I just thought of another way?

myNumber = 12345
?Format(myNumber, "00000000000")
0
aikimarkCommented:
Do you one better, Dale.  Include the "A" prefix:
myNumber = 12345
?Format(myNumber, "A00000000000")
A00000012345

Open in new window

0
Dale FyeOwner, Developing Solutions LLCCommented:
so,

Me.Filter ="[AccountNumber] = '" & Format(me.cmbPtInfo, "A00000000000") & "'"
me.FilterOn = true

Open in new window

0
aikimarkCommented:
yep
0
GPSPOWAuthor Commented:
Thanks

GLen
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Access

From novice to tech pro — start learning today.