Discussion Locked

Finding similar

0 helpful
3 replies
Closed to new repliesThis discussion is closed. It stays readable, but it takes no new replies.

Hello, I have an Access database with a table that has 2 fields, 1 of the fields holds data in a alfa numerical format such AA12E3. The last figure goes from 0 to 9 then the second last figure escalates by 1 ie AA12E4 and so on.
Some of these records are missing for good reason, now I need to run a query that will find a specified amount of sequential numbers requires. I.E if I ask for 6 sequential number, I need the query to return i.e AH45A0 thru AH45A5, now it does not matter how many results are returned only that it must only be 6 sequential numbers if there are 7 or 5 sequential numbers I do not require them. I am but a bit confused on how to string a query together for this. Any help appreciated.

regards

Donn

3 replies

by awfernald

The easiest way I can think of is as follows:

Build an array containing only the values of this field such that:

n(0)=AH45A0
n(1)=AH45A1
n(2)=AH45A2
...
n(5)=AH45A5
n(6)=AH45A6

Then simply parse loop through the array one time, comparing n(0) vs n(5) and n(6). If n(0)+6=n(5) then the 6 elements are assumed sequential. if n(0)+7=n(6) then there are more than 6 sequential elements.

If the sequential elements = 6, then put the element number (i.e. 0 for n(0)) into another array to store for later pulling out the sequence numbers. The 0 should be the equivalent of your record number.

To be safer, pull out the unique key field identifier for the record that held the first value of the sequence of six records.

by sgt_shultz

you may be able to use the LIKE keyword. there is a great sample of this in the Northwind sample database. In the Contacts phone book. check it out. the northwind sample database can be downloaded from www.microsoft.com/downloads or you can find it and the invaluable Help file that goes with it that cannot be downloaded (sheesh microsoft)
otherwise, sounds like you need wildcards. do you have Access Help installed. can you get into visual basic editor. can ou get into vba help there. search for string manipulation, wildcard,like that. hih

by parier

If you wish to execute this on demand to a report, you can use the following string in a query. When run (and pointed to a report) your results will be displayed in a snap.

We are sinply adding a >= the first value and a <= the second value. This will find the in between, including the values you entered.

In the first column add this:
>=[Enter the value to start from]

In the second column, add this:
<=[Enter the value to end with]

When the query is executed, you will be prompted to enter the starting value, for example, the first prompt you should enter AH45A0 and on the second prompt you should enter AH45A5. Your results will find (if they exist) any record in between the values you entered, including the value your entered. If you do not want to inlcude the values you entered, remove the = (equal sign) from both statements.

Hope this helps. I can email an example DB if you wish.

This discussion is closed to new replies.