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?
 
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
 
Dale FyeCommented:
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
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Dale FyeCommented:
Me.Filter ="[AccountNumber] = 'A" & Right(string(11, "0")  & me.cmbPtInfo) & "'"
0
 
Dale FyeCommented:
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
 
Dale FyeCommented:
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 FyeCommented:
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 FyeCommented:
so,

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

Open in new window

0
 
aikimarkCommented:
yep
0
 
GPSPOWAuthor Commented:
Thanks

GLen
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.