Query to Extract Range of Records

[colintis]

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.

[code green]

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

locn  area   district  loc
A1     A      1
B2C    B      2         C
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

[colintis]

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

.....
WHERE locn LIKE 'A*'
OR locn LIKE 'B*'
...

then within the area groups additional AND for district and loc

...locn LIKE 'A*'
AND Mid(locn,2,2) >= 10
AND ASC(Mid(locn,4,1)) >= ASC("G")
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.

[NeoPa]

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.

[colintis]

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?

[NeoPa]

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.

[colintis]

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....

Dim RngF As String  'Location range from
Dim RngT As String  'Location range to
 
'Extract the range from and to
RngF = Left(locn, InStr(1, locn, "-") - 1)
RngT = Right(locn, Len(locn) - InStr(1, locn, "-"))
 
'For range within the same area e.g. A1-A8
If Asc(Left(RngT, 1)) - Asc(Left(RngF, 1)) = 0 Then
 
    'E.g. A1-A18 / A1B-A18C
    'Check if 2nd character also the same
        'Yes, between 'A1' and 'A18C'
 
    'E.g. A1-A8 / A1-A80
    'If number single digit
        'Yes, between 'A1' and 'A8' inclusive within 3 character size
        'No, between 'A1' and 'A80' inclusive
 
    'E.g. A2C-A50G
 
'For range between 2 areas next to each other e.g. A20G-B30F
ElseIf Asc(Left(RngT, 1)) - Asc(Left(RngF, 1)) = 1 Then
 
    'E.g. A-B
    'Simply LIKE A* and B*
 
    'E.g. A1-B2
    'Between A1 to A99Z, then B to B2* inclusive
    'Exclude 4 characters long values in B such as B10F
 
    'E.g. A1A-B2G
    'Between A1A to A99Z, then B to B2G inclusive
    'Exclude 4 characters long values in B such as B10F
 
    'E.g. A20G-B30F
    'Between A20G to A99Z, then B to B30F inclusive
 
'For range between 2 areas apart e.g. A1-Z3F
ElseIf Asc(Left(RngT, 1)) - Asc(Left(RngF, 1)) > 1 Then
 
    'E.g. A-C / A-L
    'Simply BETWEEN A to B and LIKE C* / BETWEEN A to N and LIKE L*
 
    'E.g. A1-C5
    'BETWEEN A1 to B*, then C to C5* inclusive
    'Exclude 4 characters long values in C such as C15D
 
    'E.g. A2G-C40G
    'BETWEEN A1 to B*, then C to C40G inclusive
 
End If

[code green]

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

[NeoPa]

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?

[colintis]

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. =)

[NeoPa]

You're welcome :-)

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