finding a record with numeric digits in any order

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

hi. i'd like to know how to do a "find" for records in a single column with a
number combination,
example: "123" which could be in any order.

so i'd be finding any record in a column containing: 123, 132, 213, 231,
312, or 321 without having to enter each different combination.

yep, this is for a lottery database!
THANKS for any help!! (^_^)
 
You must sort the digits before making the comparaison. You can use a VBA
function that will return the sorted digits or add a new field with the
digits alreay sorted.

An easy way is to manipulate these numbers as a string.

S. L.
 
Not sure, it's has been a long time since I've used VBA: you convert the
integer value into a string by using the CStr and then you can manipulate
each digit separately as caracter as the value is now a string. You can
either compute it directly and store the result in the database or create a
VBA function and call it from your SQL query statement.

If you want to create such function who may be called from a query
statement, the parameters must be of Variant type. I don't have the time to
write it but it will be something like this:

Public Function strSortedDigits (ByVal Number As Variant) As String

Dim N as string

If (IsNull(Number)) Then
strSortedDigits = ""
Else
N = CStr (Number)

' ---- Do the sorting stuff on N here ---

....

' ---- When the sorting will be finished, return N as the value:

strSortedDigits = N
End If

End Function


S. L.
 
jeez, isn't there a way to just click on the "binoculars", type in "123" or
whichever and & or % or * or something, and have Access find the combination
in any order that way?

Access seems to be very limited when it comes to this sort of thing.

THANKS! (^_^)
 
Maybe yes: it is possible that you can use regular expression but under
Access, the functionality of regular expression is very, very limited. Take
a look at the statement LIKE in the Access' help and see if it can help you.
(Personally, I don't remember exactly the possibilities of this statement
under Access).

We must also take into account the fact that regulare expression are often
slow. Maybe it's not relevant in your case but maybe it is. I cannot judge
on this aspect from your sample.

S. L.
 
hi. i'd like to know how to do a "find" for records in a single column with a
number combination,
example: "123" which could be in any order.

so i'd be finding any record in a column containing: 123, 132, 213, 231,
312, or 321 without having to enter each different combination.

yep, this is for a lottery database!
THANKS for any help!! (^_^)
Click to expand...

Field Like "*1*" AND Field LIKE "*2*" AND Field LIKE "*3*"

will do it.

Access is designed to treat fields as being "atomic" - having only one
value. You're storing three (independently searchable) integers in one
field, violating this principle - that's why the search is difficult.
If these are three discrete integers, consider using three fields.

John W. Vinson[MVP]
 
COOL, but i'm a bit Access stupid. could you be more specific with those
instructions for setting up the columns, please?

THANKS! (^_^)
 
COOL, but i'm a bit Access stupid. could you be more specific with those
instructions for setting up the columns, please?

THANKS! (^_^)
Click to expand...

It would help to know what you're starting with and what you're trying
to accomplish. If (say) you're looking at something like a Lotto
drawing, where there are six numbers, each from 1 to 47 or whatever,
then the best design would be a table with a date field and an Integer
number field; you'ld add six records, one for each number drawn on
that day.

But that appears to have little relationship to your original
question, in which you have a single three-digit number and want to
search the digits individually. I simply don't understand what this
search is intended to accomplish, so I'm at a loss to explain how best
to accomplish it!

John W. Vinson[MVP]
 
thanks for your patience and help. i'll try to explain as best i can.

first, this is about "Cash 3" of the Florida lottery: www.flalottery.com

i'm just trying to find draw-history patterns of the 3-number combinations
which can have same digits and can be any combination/order, 0-9.

so i was hoping to do "look-backs" in the history of the drawings to find
3-number combination frequencies without regard to their order as they're
drawn.

so i need to be able to "look-back" through the draw dates and find
occurences of 3-number combinations.

for example: recently, 479 was drawn. i want to look back and see when the
last time it was that 479 was drawn WITHOUT regard to the order that 479 was
drawn the last time it was drawn. it could be 974 or 497 or whichever.

i wish there was some way i could just click on the binoculars on the menu
above my table/draw-history, and make some instruction for the "FIND" along
with "479" and have Access return the last date or all the dates that 479 was
drawn WITHOUT regard to the order those three numbers were drawn.

i hope this makes things easier to understand. THANKS for any help!! (^_^)
 
Back
Top