min and max from recordsetclone

i have a form with continuous rows a field value returns a number sequence.
[sequence]
123454
123456
123459
123460
123470
123480



the rows will be filtered down.

what i would like to do is  get the min and max of the sequence and then filter on a value that i choose so ex.

i could go min 123460 to max 123480
or
min 123470 to 123480

or min 12354 to 123470

i am not sure how to create that it would have to show on the form the min and max values it found so the user can choose the filter between values.

I guess it would get the recordset close to determine min and max each time a filter was applied.

i am in vba access 2010

The sequence function was created at ID: 38815240
PeterBaileyUkAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

 
Rey Obrero (Capricorn1)Commented:
try this codes

Dim minVal As Long, maxVal As Long
With Me.RecordsetClone
     .MoveFirst
               maxVal = !sequence

     Do Until .EOF
               If maxVal < !sequence Then
                    maxVal = !sequence
               End If
                   
     
          .MoveNext
     Loop
     .MoveFirst
          minVal = !sequence
       Do Until .EOF
               If minVal > !sequence Then
                    minVal = !sequence
               End If
          .MoveNext
     Loop
   
     
End With


MsgBox "Minimum value is " & minVal & " and  Maximum Value is  " & maxVal
0
 
hnasrCommented:
Try this: No need for recordset, you can use the form's record source, unless you are incrementing the filter process.
Form
Record Source: a (aID, ...)
Command Button: cmdMinMax
TextBox:                txtMin
TextBox:                txtMax

Private Sub cmdMinMax_Click()
    txtMin = DMin("aID", "a")
    txtMax = DMax("aID", "a")
End Sub

Open in new window

0
 
PeterBaileyUkAuthor Commented:
could it be refined to only set a min and max that are 7 digits long?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
PeterBaileyUkAuthor Commented:
i tried   If minVal > !sequence and len(!sequence)=7 but didnt work just so my min and max txt boxes get set. i dont know if its possible but could a slider be set to the mi and max?
0
 
hnasrCommented:
txtMin = Right ("0000000" & minValue, 7)
txtMax = Right ("0000000" & maxValue, 7)

or:

txtMin = Right (String(7,"0") & minValue ,7)

txtMax = Right (String(7,"0") & maxValue ,7)
0
 
PeterBaileyUkAuthor Commented:
i will be incrementing the filter so went for the code option, i wasnt sure how to use the other option although i did add the sub.
0
 
Rey Obrero (Capricorn1)Commented:
peterBaileyUk

why don't you post the exact values of the sequence field?
0
 
PeterBaileyUkAuthor Commented:
the sequence is part of the alphanumeric vin so is extracted as the right 7 and i hoped i would be able to filter between ranges of the sequence numbers buried in the vins. that will allow me to identify the vehicles better.
0
 
Rey Obrero (Capricorn1)Commented:
so the sequence is already 7 characters.

to filter

Right([Vin],7) between minValue and maxValue
0
 
PeterBaileyUkAuthor Commented:
no the vins are supposed to be 17 characters in length with normally the right 7 being the numerical part, it isnt always the case as the government departments make errors and the data gets truncated or typos can be present but on the whole its like that

The data i have filtered down too looks like this:
VIN_Original_DVLA
CG125BR8113246
CG125BR8110939
CG125BR8101445
CG125BR8100196
CG125BR8106642
CG125BR8110847
CG125BR8108479
CG125BR8112138

I used standard filters i created to get to these (10000 in all) , they are allocated to other codes that denote the year and to allocate properly i need to get those right hand 7 and filter between numeric values. in this example 8100001 onwards.

rather than get the min and max values from the reference book i have it was easier to ascertain the min and max of that right 7 and hopefully create a control on the form to let me create the filter quickly and filter those records down.

hope thats better explanation
0
 
Rey Obrero (Capricorn1)Commented:
how is  this?


where Clng(Right([VIN_Original_DVLA],7)) between minValue and maxValue
0
 
PeterBaileyUkAuthor Commented:
Ok I have attached a cut down db. I have created a control under the heading sequence length that allows me to change the length of the sequence number ([Sequence7]) rather than lock it at right 7.

I need to see the form sequence7 min and max figures first which leans me to the code solution to grab that. It has one issue that the initial unfiltered records are huge and the sequence filtering doesnt need to be done at that stage. (the cut down has only 6000 ish records so no issue on this version). The sequence button on the top right was there to control that allowing me to filter down first and then to do the sequence filtering.

I like the filter method as suggested by capricorn.

I wonder if its possible to populate the min max sequence boxes on demand via button. Then when those text box values are set either manually or electronically use that and filter from those values with a capricorn1 filter method.

Their is a global counter keeping track of the sequence length or the label gives it too.

Apologies for not being so precise I started off wanting just right 7 but saw after that that would need to be variable.
Decode-EE.zip
0
 
PeterBaileyUkAuthor Commented:
Ok i have got all done the only part left is how can i stop the code failing on !sequence 7 null:

With Me.RecordsetClone
     .MoveFirst

               maxVal = !Sequence7

     Do Until .EOF
               If maxVal < !Sequence7 Then
                    maxVal = !Sequence7
               End If


          .MoveNext
     Loop
     .MoveFirst
          minVal = !Sequence7

       Do Until .EOF
               If minVal > !Sequence7 Then
                    minVal = !Sequence7
               End If
          .MoveNext
     Loop


End With

Open in new window

0
 
hnasrCommented:
If looking for an issue, then upload a database with just the objects that recreates the issue.

Explain what to do from running the form, and what to do, and what is the current output, and what to expect.
0
 
Rey Obrero (Capricorn1)Commented:
use the nz() function
With Me.RecordsetClone
     .MoveFirst

               maxVal = nz(!Sequence7,0)

     Do Until .EOF
               If maxVal <  nz(!Sequence7,0) Then
                    maxVal =  nz(!Sequence7,0)
               End If


          .MoveNext
     Loop
     .MoveFirst
          minVal =  nz(!Sequence7,0)

       Do Until .EOF
               If minVal >  nz(!Sequence7,0) Then
                    minVal =  nz(!Sequence7,0)
               End If
          .MoveNext
     Loop


End With

Open in new window

0

Experts Exchange Solution brought to you by ConnectWise

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
 
PeterBaileyUkAuthor Commented:
thank you capricorn1
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.