Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

min and max from recordsetclone

Posted on 2013-01-24
16
Medium Priority
?
722 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 31

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
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 

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 31

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 31

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

Independent Software Vendors: 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!

Question has a verified solution.

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

AutoNumbers should increment automatically, without duplicates.  But sometimes something goes wrong, and the next AutoNumber value is a duplicate.  This article shows how to recover from this problem.
This article shows how to get a list of available printers for display in a drop-down list, and then to use the selected printer to print an Access report or a Word document filled with Access data, using different syntax as needed for working with …
In Microsoft Access, when working with VBA, learn some techniques for writing readable and easily maintained code.
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …

598 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