469,091 Members | 1,148 Online

# Query to Extract Range of Records

255 100+
Hi all,

I'm currently struggling on a method of how to extracting a list of records through SQL query.

The criteria is simply looking on location code called locn, this code contains a structure of 3~4 characters. The 1st character is alphabet, 2nd is numeric, and 3rd is another alphabet. The 2nd numeric can be 2 digit, which makes the code to 4 characters long.

Its not that difficult to extract the record with simple LIKE functions such as A*, A1*, A12*, or A12A, within the locn only with up to 4 characters input. But the range I written on the topic means if the user enters a range value like this: A1-C20G

The hard part here is, how can I extract the records with this range of input? Do anyone have an idea of how such algorithm be structured? Any help would be greatly appreciated.
Dec 3 '10 #1

If only the question were expressed more clearly this would be so much simpler to deal with.

When you say A1-C20G, do you mean from "A1*" up to and including "C20G*"?
If so, then I should explain that while there are constructs to handle both a range of values ([X] Between 'A1' And 'C20G') and pattern matching of values (Like 'A1*') there is nothing to handle a mixture of the two.

> and < also can be used quite sensibly with strings in both SQL and VBA.

As 'A1' is the least value of any string starting 'A1', this can be used as the lower value in a Between construct (Between 'A1' And 'C20G' includes all of 'A1*'). Unfortunately, this doesn't hold true for the upper value. All values between, and including, 'A1*' to 'C2*' would need to be written as `Between 'A1' And 'C2ZZ'`.

If that isn't what you mean, then I can't help without further clarification of what you do mean.

10 2761
code green
1,726 Expert 1GB
I don't suppose greater than > less than < would work very well either, but it is a bit better.
I would be tempted adding a lookup table to the database of the format
Expand|Select|Wrap|Line Numbers
1. locn  area   district  loc
2. A1     A      1
3. B2C    B      2         C
4. C20G   C      20        G
OK not pretty but would greatly simply the query.
And if new locn codes are generated, populating this table could be automated
Dec 3 '10 #2
colintis
255 100+
I agreed with you code green, since greater and less than only works with numbers and dates. Althought there's a work around with using character's number form, but this only works in VBA, not in query as I wanted to.

How would the query be if I'm using a lookup table code green?

I did thinked of generating a long SQL query in VBA with all sorts of criterias.
E.g. A10C-B, using the OR to group each area
Expand|Select|Wrap|Line Numbers
1. .....
2. WHERE locn LIKE 'A*'
3. OR locn LIKE 'B*'
4. ...
then within the area groups additional AND for district and loc
Expand|Select|Wrap|Line Numbers
1. ...locn LIKE 'A*'
2. AND Mid(locn,2,2) >= 10
3. AND ASC(Mid(locn,4,1)) >= ASC("G")
4. OR locn LIKE 'B*'....
But in the end if the user is hitting a search range of A-Z2G, then the query generated would be too long as I afraid it may come over the size limit in Access.
Dec 5 '10 #3
NeoPa
32,159 Expert Mod 16PB
If only the question were expressed more clearly this would be so much simpler to deal with.

When you say A1-C20G, do you mean from "A1*" up to and including "C20G*"?
If so, then I should explain that while there are constructs to handle both a range of values ([X] Between 'A1' And 'C20G') and pattern matching of values (Like 'A1*') there is nothing to handle a mixture of the two.

> and < also can be used quite sensibly with strings in both SQL and VBA.

As 'A1' is the least value of any string starting 'A1', this can be used as the lower value in a Between construct (Between 'A1' And 'C20G' includes all of 'A1*'). Unfortunately, this doesn't hold true for the upper value. All values between, and including, 'A1*' to 'C2*' would need to be written as `Between 'A1' And 'C2ZZ'`.

If that isn't what you mean, then I can't help without further clarification of what you do mean.
Dec 5 '10 #4
colintis
255 100+
Yes that is correct NeoPa, I'll try to improve my typo problem, and thanks for correcting a clearer title for me.

I tried the BETWEEN 'A1' AND 'C20G' and surprisingly it worked. But then I also found some locn values that are not in the format I mentioned earlier (e.g. BACK0, BRXXD). which is something I need to filter out as well. Is there some ways to check if they are with valid locn values?
Dec 7 '10 #5
NeoPa
32,159 Expert Mod 16PB
ColinTis:
I tried the BETWEEN 'A1' AND 'C20G' and surprisingly it worked.
Everyone always seems to be surprised when my suggestions work. You'd think they'd get used to it :-D

ColinTis:
Is there some ways to check if they are with valid locn values?
That's a question for another thread. If you'd like to post a link to the new thread in here then I'll happily have a look at it for you. I expect it'll require a function written in VBA though.
Dec 7 '10 #6
colintis
255 100+
No need for capital C and T in my name NeoPa. :)

I have another question which is related.
If I have an input of A2G-C2A, the result of this will also include those with 2 digit numbers in Area C such as C13F, and C23D. How will this be overcome?

Also, if the input is BETWEEN 'A1' AND 'A18', the result will then exclude values such as A1A, A1B, A18C, and A18D. But if I change the input to BETWEEN 'A1' AND 'A2', all those A1 and A18 values will be included.

Using VBA to generate the query, I had come up with this nested IF so far, but I'm having trouble with nesting the deeper parts....
Expand|Select|Wrap|Line Numbers
1. Dim RngF As String  'Location range from
2. Dim RngT As String  'Location range to
3.
4. 'Extract the range from and to
5. RngF = Left(locn, InStr(1, locn, "-") - 1)
6. RngT = Right(locn, Len(locn) - InStr(1, locn, "-"))
7.
8. 'For range within the same area e.g. A1-A8
9. If Asc(Left(RngT, 1)) - Asc(Left(RngF, 1)) = 0 Then
10.
11.     'E.g. A1-A18 / A1B-A18C
12.     'Check if 2nd character also the same
13.         'Yes, between 'A1' and 'A18C'
14.
15.     'E.g. A1-A8 / A1-A80
16.     'If number single digit
17.         'Yes, between 'A1' and 'A8' inclusive within 3 character size
18.         'No, between 'A1' and 'A80' inclusive
19.
20.     'E.g. A2C-A50G
21.
22. 'For range between 2 areas next to each other e.g. A20G-B30F
23. ElseIf Asc(Left(RngT, 1)) - Asc(Left(RngF, 1)) = 1 Then
24.
25.     'E.g. A-B
26.     'Simply LIKE A* and B*
27.
28.     'E.g. A1-B2
29.     'Between A1 to A99Z, then B to B2* inclusive
30.     'Exclude 4 characters long values in B such as B10F
31.
32.     'E.g. A1A-B2G
33.     'Between A1A to A99Z, then B to B2G inclusive
34.     'Exclude 4 characters long values in B such as B10F
35.
36.     'E.g. A20G-B30F
37.     'Between A20G to A99Z, then B to B30F inclusive
38.
39. 'For range between 2 areas apart e.g. A1-Z3F
40. ElseIf Asc(Left(RngT, 1)) - Asc(Left(RngF, 1)) > 1 Then
41.
42.     'E.g. A-C / A-L
43.     'Simply BETWEEN A to B and LIKE C* / BETWEEN A to N and LIKE L*
44.
45.     'E.g. A1-C5
46.     'BETWEEN A1 to B*, then C to C5* inclusive
47.     'Exclude 4 characters long values in C such as C15D
48.
49.     'E.g. A2G-C40G
50.     'BETWEEN A1 to B*, then C to C40G inclusive
51.
52. End If
Dec 7 '10 #7
code green
1,726 Expert 1GB
I tried the BETWEEN 'A1' AND 'C20G'
Is say C110G greater or less then C20G?.
Because this is where BETWEEN may not work as expected
Dec 7 '10 #8
NeoPa
32,159 Expert Mod 16PB
Colintis:
No need for capital C and T in my name NeoPa :)
The 'T' may well have been guesswork on my part, but I will always capitalise the first letter of a name, as not to do so shows a lack of respect. It would be as unusual as not capitalising the first letter of a sentence. If you are telling me that you prefer that I don't capitalising even the 'C', then I will try to remember and add this to my mental list of exceptions.

On to the technical part of the question.

The proper answer of course, is to treat three separate fields as just that. Three separate fields. Joining separate values together is more straightforward than separating three values already converted to text in a specific way. That's the proper database answer.

An alternative might be to convert the data to text in a more text-consistent manner. Code Green highlights the main issue here, which is basically that numbers are typically interpreted from the right, while text is interpreted from he left. If the number part were always converted to text with the same specific number of digits (in this case two) then textual comparisons would be equivalent and a single text value could be used. It's a workaround of course, but should work nevertheless.

Colintis:
Also, if the input is BETWEEN 'A1' AND 'A18', the result will then exclude values such as A1A, A1B, A18C, and A18D. But if I change the input to BETWEEN 'A1' AND 'A2', all those A1 and A18 values will be included.
This would then be `BETWEEN 'A01' AND 'A18'`, but if you go back to my paragraph #4 of post #4 then you'll see this wasn't quite what I was suggesting for the TO part. This needs to be padded with 'Z's to ensure all of the 'A18's are captured.

Does that all make sense?
Dec 7 '10 #9
colintis
255 100+
Thanks NeoPa, I might have missed that part of your reply. The T part of my username just keep it lower case, as that part really just a made-up when I think of my username. :P

In the end I used the method Code Green suggested, making a lookup table with all those date split into individual column, much easier although more duplicating codes but it does the job correctly. Thanks guys. =)
Dec 14 '10 #10
NeoPa
32,159 Expert Mod 16PB
You're welcome :-)

Ultimately, use whichever method suits you best, but I'm glad you got something that's good for you.
Dec 14 '10 #11