?
Solved

min and max from recordsetclone

Posted on 2013-01-24
16
Medium Priority
?
700 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 8
  • 5
  • 3
16 Comments
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 2000 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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
 
LVL 120

Assisted Solution

by:Rey Obrero (Capricorn1)
Rey Obrero (Capricorn1) earned 2000 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 120

Expert Comment

by:Rey Obrero (Capricorn1)
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 120

Accepted Solution

by:
Rey Obrero (Capricorn1) earned 2000 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

Free learning courses: Active Directory Deep Dive

Get a firm grasp on your IT environment when you learn Active Directory best practices with Veeam! Watch all, or choose any amount, of this three-part webinar series to improve your skills. From the basics to virtualization and backup, we got you covered.

Question has a verified solution.

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

Access developers frequently have requirements to interact with Excel (import from or output to) in their applications.  You might be able to accomplish this with the TransferSpreadsheet and OutputTo methods, but in this series of articles I will di…
If you need a simple but flexible process for maintaining an audit trail of who created, edited, or deleted data from a table, or multiple tables, and you can do all of your work from within a form, this simple Audit Log will work for you.
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
With Microsoft Access, learn how to start a database in different ways and produce different start-up actions allowing you to use a single database to perform multiple tasks. Specify a start-up form through options: Specify an Autoexec macro: Us…
Suggested Courses

762 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