Filter number or interval in an interval

[Dream.Van]

Hi
I've been searching a lot on the web for this problem, but I think it's rather uncommon... I'm fairly new to access and programing in general, but I'm a quick learner.

I have have a multi criteria search form in which I filter maps. I use a VBA code to create my search string and then apply it to the form's filter.

I have a township field and the townships are divided in ranges and lots (Like a grid). I have 2 lot field (number fields), one is the begining of the interval and the other is the end (because maps cover more than one lot and/or range).

My problem is how to create a string to search an a single lot or an interval of lots in those fields.

Example: I want all the maps that show lots 7 to 9 in x township and it gives me the perfect matches, the ones that have a larger range (like 2 to 10) and those who only have part of the criterias (like 5 to 8)

I also have the same problem for the ranges but the're in a text field because there are exceptionally a few letters. So I don't think anything can be done about it. Ranges are in roman numbers (but I don't thinks that this is a problem). There a no combination on interval between the roman numbers and the occasional letters.


I figured it would be the same for dates but I didn't find anything on that ether...

Hope I'm clear enough
Thank's for helping

 

[Jeanette Cunningham]

If Not IsNull(Me.txtStartLot) Then
strWhere = strWhere & "([Lot] >= " & Me.txtStartLot & ") AND "
End If


If Not IsNull(Me.txtEndLot) Then
strWhere = strWhere & "([Lot] < " & Me.txtEndLot & ") AND "
End If

txtStartLot and txtEndLot are textboxes where the start and end lot numbers are entered.

 

[Dream.Van]

That part I get perfectly, but how do I apply it to my 2 Lot fields (1st and last lot) ???