Searching field from right to left

  • Thread starter Thread starter Brenna
  • Start date Start date
B

Brenna

I have a field where the data is a text based 8 character ID.

For Instance: 0000003A

I want to have a query that will prompt for a value but
will search starting at the right side.

So if one enters: 3A it will output 0000003A rather than
00000013A.

I've tried the following criteria expression, but it's not
quite right: Like "*" & [Enter Volume Number]

Any ideas?
 
Well, if your Volume Number was all numbers (no text) you could do this:
Format([Enter Volume Number],"00000000")
in the criteria. So if you entered 34, it would find 00000034 and not
00000134. However, this will not work with letters in the parameter.

However, you can write your own function to pad zeros:

Function PadZeros(TheValue As String, TotalLength As Integer) As String
Dim NumZeros As Integer
Dim i As Integer

NumZeros = TotalLength - Len(TheValue)
For i = 1 To NumZeros
TheValue = "0" & TheValue
Next i
PadZeros = TheValue
End Function

Then you can call it like this in the criteria of a query:
PadZeros([enter number],8)
 
Hi,

I would give a try to:


WHERE field LIKE '*' & [Enter Volume Number]
AND val(field) = val([Enter Volume Number] )


that means you accept 0 or more "0" starting the sequence in front of the
real pattern you look for.


If you really meant "end by the pattern I supply", but start by "0", then,
faster (at execution time):

WHERE field = STRING( len(field)-len([Enter Volume Number] ), "0" )
& [Enter Volume Number]


if not, then the first solution is probably to be modified to:

WHERE field LIKE '*' & [Enter Volume Number] & "*"
AND val(field) = val([Enter Volume Number] )


and there are probably cases where it won't be appropriate either...


Hoping it may help,
Vanderghast, Access MVP
 
Brenna said:
I have a field where the data is a text based 8 character ID.

For Instance: 0000003A

I want to have a query that will prompt for a value but
will search starting at the right side.

So if one enters: 3A it will output 0000003A rather than
00000013A.

I've tried the following criteria expression, but it's not
quite right: Like "*" & [Enter Volume Number]
Click to expand...
Hi Brenna,

One more variation on the theme:

WHERE field = RIGHT("00000000" & [Enter Volume Number], 8)
 
-----Original Message-----

Brenna said:
I have a field where the data is a text based 8 character ID.

For Instance: 0000003A

I want to have a query that will prompt for a value but
will search starting at the right side.

So if one enters: 3A it will output 0000003A rather than
00000013A.

I've tried the following criteria expression, but it's not
quite right: Like "*" & [Enter Volume Number]
Click to expand...
Hi Brenna,

One more variation on the theme:

WHERE field = RIGHT("00000000" & [Enter Volume Number], 8)


.
Click to expand...

That worked great, exactly what I was looking for. Thanks!
 
Back
Top