Solved

min and max from recordsetclone

Posted on 2013-01-24
16
681 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In the article entitled Working with Objects – Part 1 (http://www.experts-exchange.com/Microsoft/Development/MS_Access/A_4942-Working-with-Objects-Part-1.html), you learned the basics of working with objects, properties, methods, and events. In Work…
Describes a method of obtaining an object variable to an already running instance of Microsoft Access so that it can be controlled via automation.
Familiarize people with the process of utilizing SQL Server stored procedures from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Micr…
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…

895 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

14 Experts available now in Live!

Get 1:1 Help Now