Solved

min and max from recordsetclone

Posted on 2013-01-24
16
678 Views
Last Modified: 2013-01-28
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
0
Comment
Question by:PeterBaileyUk
  • 8
  • 5
  • 3
16 Comments
 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 500 total points
ID: 38815923
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
 
LVL 30

Expert Comment

by:hnasr
ID: 38817201
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
 

Author Comment

by:PeterBaileyUk
ID: 38817691
could it be refined to only set a min and max that are 7 digits long?
0
 

Author Comment

by:PeterBaileyUk
ID: 38817699
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
 
LVL 30

Expert Comment

by:hnasr
ID: 38818208
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
 

Author Comment

by:PeterBaileyUk
ID: 38818337
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38818630
peterBaileyUk

why don't you post the exact values of the sequence field?
0
 

Author Comment

by:PeterBaileyUk
ID: 38818730
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
Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

 
LVL 119

Assisted Solution

by:Rey Obrero
Rey Obrero earned 500 total points
ID: 38818760
so the sequence is already 7 characters.

to filter

Right([Vin],7) between minValue and maxValue
0
 

Author Comment

by:PeterBaileyUk
ID: 38819009
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
 
LVL 119

Expert Comment

by:Rey Obrero
ID: 38819061
how is  this?


where Clng(Right([VIN_Original_DVLA],7)) between minValue and maxValue
0
 

Author Comment

by:PeterBaileyUk
ID: 38825773
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
 

Author Comment

by:PeterBaileyUk
ID: 38826545
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
 
LVL 30

Expert Comment

by:hnasr
ID: 38826715
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
 
LVL 119

Accepted Solution

by:
Rey Obrero earned 500 total points
ID: 38826757
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
 

Author Closing Comment

by:PeterBaileyUk
ID: 38826815
thank you capricorn1
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Suggested Solutions

Regardless of which version on MS Access you are using, one of the harder data-entry forms to create is one where most data from previous entries needs to be appended to new records, especially when there are numerous fields and records involved.  W…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Learn how to number pages in an Access report over each group. Activate two pass printing by referencing the pages property: Add code to the Page Footers OnFormat event to capture the pages as there occur for each group. Use the pages property to …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.

746 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now