Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2013
    Posts
    81

    Question Unanswered: MS Access 07 VBA find record between two values

    Hope someone can help me with this one.

    As a bit of background I'm building a database to house equipment records. At the moment I'm working on a section for scales. So for each scale I need to record what weights we carry out certain tests at (for e.g. repeatability may be done at 200g) and the allowable errors associated with that weight. The allowable errors are laid out in law and have been draw out in bands, so 0 - 100g may be allowed + or - 9g (not actual figures).

    I thought it would be easier to simply copy the table as it stands (so I've got in tblAvgWtPLEs, AvgWgtFrom - the starting figure, AvgWtTo - the finishing figure and AvgWtPlePercent - where the error value is stored), rather than entering a figure for every single gram (as we will use values anywhere between 1mg and 5 ton - a lot of records).

    I've then got tblBalance which stores the information about the scales, as part of this I have BalanceRepeatability1 where I state the weight the machine is to be tested at (e.g. 200g). I then need Access to go to the AvgWtPLEs table find which record 200g falls in (e.g. the record for 101g - 200g) and find the associated PLE (e.g. 5g). This is where I'm stuck.

    I've thought about DLookup or SELECT queries mixed with a between statement but I'm not even vaguely close to getting them to work. Any ideas or pointers would be greatly appreciated.

    Many thanks.

  2. #2
    Join Date
    Apr 2014
    Location
    Kentucky
    Posts
    495
    Provided Answers: 24
    No dlookup, you use a query.
    You need a form, say frmFind, with text boxes...txtMinWt , txtMaxWt
    your query would look at these as parameters...

    qsFind = select * from [tblAvgWtPLEs]
    where [AvgWgtFrom] => forms!frmFind!txtMinWt
    and
    [AvgWtTo ] =< forms!frmFind!txtMaxWt

    'you can also throw in the calc to incorpoate the percent tolerance too.

  3. #3
    Join Date
    Jun 2013
    Posts
    81

    Question No idea what I'm doing wrong here

    Hi Ranman, thanks for the help, I really appreciate it. Unfortunately I'm still having problems.

    With all my other VBA queries (none of which are select queries) I use DoCmd.Execute to run them, but I understand this doesn't work for a select query so I've switched to DoCmd.RunSQL. However, despite my best efforts I keep getting "Run-time error '2342: A RunSQL action requires an arument consisting of an SQL statement."

    I have no idea where I'm going wrong, I've copied the resulting SQL string (from printing the VBA code to the debug window) into the SQL builder for queries and it works there, it just wont run in the VBA. Any suggestions would be greatly appreciated. The code I've got is:

    Code:
    If Me.BalanceType = "Average Weight" Then
        'run select query to find AvgWtPlePercent (if there - need to add is null) where txtBalanceRepeat1 on the form is between AvgWtFrom and AvgWtTo
        
        strSQLq "SELECT AvgWtPlePercent " & _
                     "FROM tblAvgWeightPLEs " & _
                      "WHERE tblAvgWeightPLEs.[AvgWtFrom] <= Forms!frmBalance!txtBalanceRepeat1 And " & _
                      "tblAvgWeightPLEs.[AvgWtTo] >= Forms!frmBalance!txtBalacnceRepeat1 "
        Debug.Print strSQLq
        DoCmd.RunSQL strSQLq
    The code bugs out on the DoCmd line. I've also tried starting the SQL code with DoCmd (and getting rid of the bit that says strSQLq =) with the same result.

    Any ideas?

  4. #4
    Join Date
    Nov 2004
    Location
    out on a limb
    Posts
    13,692
    Provided Answers: 59
    use the BETWEEN predicate

    Code:
    " WHERE  " & Forms!frmBalance!txtBalanceRepeat1 & "  BETWEEN " & AvgWtFrom & " And " & AvgWtTo
    check that Forms!frmBalance!txtBalanceRepeat1 is a valid number
    also AvgWtFrom & AvgWtTo are numeric
    when using the BETWEEN predicate the first value (in this case AvgWtFrom is less than the second value)

    also it helps if you actually post the sql not the VBA code that create the SQL. you are part way there already by the looks of it by using debug.print
    Code:
    SELECT AvgWtPlePercent FROM tblAvgWeightPLEs
    WHERE
      tblAvgWeightPLEs.[AvgWtFrom] <= Forms!frmBalance!txtBalanceRepeat1
    And 
      tblAvgWeightPLEs.[AvgWtTo] >= Forms!frmBalance!txtBalacnceRepeat1
    ..the proble is tht you need to swtich back and forth when intermixing SQL and VBA variables / form controls
    instead that shoud read soemthign like
    Code:
    SELECT AvgWtPlePercent " & _
                     "FROM tblAvgWeightPLEs " & _
                      "WHERE tblAvgWeightPLEs.[AvgWtFrom] <= " & Forms!frmBalance!txtBalanceRepeat1 & " And " & _
                      "tblAvgWeightPLEs.[AvgWtTo] >= " & Forms!frmBalance!txtBalacnceRepeat1
    I'd rather be riding on the Tiger 800 or the Norton

  5. #5
    Join Date
    Jun 2013
    Posts
    81

    Question Errm

    Hi Healdem, thanks for the very quick reply.

    I've double checked the table (tblAvgWeightPLEs) and all fields are in a number format. txtBalanceRepeat1 is taken from a form field which is in text format (200 g for e.g.) I then use VBA code to split the 200 from the g, perform a calculation to ensure the figure is shown in grams (so if a figure of 2 kg is entered the programme should say 2 * 1000 = 2000 and then the resulting figure is displayed in the text box (it doesn't need to go into the text box, but using it as lQuantity (this is where the calculation result should end up) within the SQL didn't seem to work so I thought I'd try a text box). I've gone into the text box properties and set it to a general number, I've also got Dim lQuantity (the value of which is used in the text box earlier in the code) as Long.

    I've tried your suggested code for using Between, so the actual code reads:

    Code:
    If Me.BalanceType = "Average Weight" Then
        'run select query to find AvgWtPlePercent (if there - need is null) where lquantity is between AvgWtFrom and AvgWtTo
        
        strSQLq = "SELECT AvgWtPlePercent " & _
                        "FROM tblAvgWeightPLEs " & _
                        "WHERE " & Forms!frmBalance!txtBalanceRepeat1 & "  BETWEEN " & "tblAvgWeightPLEs.AvgWtFrom  And tblAvgWeightPLEs.AvgWtTo"
        Debug.Print strSQLq
    
        DoCmd.RunSQL strSQLq
    This prints out to give:

    Code:
    SELECT AvgWtPlePercent FROM tblAvgWeightPLEs WHERE 199  BETWEEN tblAvgWeightPLEs.AvgWtFrom  And tblAvgWeightPLEs.AvgWtTo
    This bugs out to the same run time error as before (2342: A RunSQL action requires an arument consisting of an SQL statement).

    I've also tried simply altering my stupid mistake (thanks for pointing that one out, blinking idiot me), which gives the code of:

    Code:
    If Me.BalanceType = "Average Weight" Then
        'run select query to find AvgWtPlePercent (if there - need is null) where lquantity is between AvgWtFrom and AvgWtTo
        
        strSQLq = "SELECT AvgWtPlePercent " & _
                        "FROM tblAvgWeightPLEs " & _
                        "WHERE tblAvgWeightPLEs.[AvgWtFrom] <= " & Forms!frmBalance!txtBalanceRepeat1 & " And " & _
                       "tblAvgWeightPLEs.[AvgWtTo] >= " & Forms!frmBalance!txtBalanceRepeat1
        Debug.Print strSQLq
    
        DoCmd.RunSQL strSQLq
    This prints out to:

    Code:
    SELECT AvgWtPlePercent FROM tblAvgWeightPLEs WHERE tblAvgWeightPLEs.[AvgWtFrom] <= 200 And tblAvgWeightPLEs.[AvgWtTo] >= 200
    Again I get the same run time error (2342: A RunSQL action requires an arument consisting of an SQL statement).

    I've taken both Print SQL statements (from the debugging window) and copied them (seperately) into the query design and both seem to work fine. So I can't understand why Access is telling me I need a valid SQL statement, surely I have one. Any ideas?

    P.S. sorry I forgot to add the SQL print out to my last post - memory like a seive at the moment.

  6. #6
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183

    MS Access 07 VBA find record between two values

    Docmd.RunSQL Command is for running action queries (Make Table, Append, Delete, Update) only. You cannot run SELECT Query. SELECT Query SQL is used for opening Recordset for VBA based processing. Usage is as given below:
    Code:
    Dim db As Database, rst As Recordset
    Dim strSQL As String
    
    strSQL = strSQLq "SELECT AvgWtPlePercent " & _
                     "FROM tblAvgWeightPLEs " & _
                      "WHERE tblAvgWeightPLEs.[AvgWtFrom] <= Forms!frmBalance!txtBalanceRepeat1 And " & _
                      "tblAvgWeightPLEs.[AvgWtTo] >= Forms!frmBalance!txtBalacnceRepeat1 "
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    
    Do while not rst.eof
    .
    .
    .
    Loop
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  7. #7
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183

    MS Access 07 VBA find record between two values

    Correction in Code:
    Code:
    Dim db As Database, rst As Recordset
    Dim strSQL As String
    
    'Validity of SQL not verified
    strSQL = "SELECT AvgWtPlePercent " & _
                     "FROM tblAvgWeightPLEs " & _
                      "WHERE tblAvgWeightPLEs.[AvgWtFrom] <= Forms!frmBalance!txtBalanceRepeat1 And " & _
                      "tblAvgWeightPLEs.[AvgWtTo] >= Forms!frmBalance!txtBalacnceRepeat1 "
    
    Set db = CurrentDb
    Set rst = db.OpenRecordset(strSQL, dbOpenDynaset)
    
    Do while not rst.eof
    .
    .
    .
    Loop
    
    rst.Close
    Set rst = Nothing
    Set db = Nothing
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  8. #8
    Join Date
    Jun 2013
    Posts
    81

    Question Crashed

    Hi apr pillai thanks for your input. Unfortunately when I've tried your suggestion its just crashed the database. I think its the loop it gets stuck in.

    The table its trying to find the data from only has 9 records, so it seems strange it would crash out like this, but then I've never used a loop before. Oooh would it get stuck because the column I'm taking the result from only has data in every other record? I've attached a print screen of the table so you can see what I mean.Click image for larger version. 

Name:	tblAverageWeightPLEs.png 
Views:	2 
Size:	14.8 KB 
ID:	16467

    I did have a bit of a stupid thought the other night. Once I've located the information from the table, if I'm using a select query how do you use the data. If I was using a DLookup I'd be able to save the resulting data into a hidden text box (or even as a variable in the code e.g. lResult) and then use it in a later If statement or calculation. I'm not sure how this would work in a select query.

  9. #9
    Join Date
    Jan 2009
    Location
    Kerala, India
    Posts
    183
    Post your code here, you have inserted within the Do While Not rst.EOF ........ Loop code segment, so that we can take a look at it and make corrections, if necessary to avoid crashes.

    If you have Access version 2007 or later you can assign chosen value into Temporary Variables and retrieve it in Form controls, or in queries as criteria and so on.
    Last edited by apr pillai; 07-24-15 at 13:30.
    www.MsAccessTips.com (Learn MS-Access Tips & Tricks)
    Learn Advanced MS-Access Programming with sample VBA Code.

    All responses are based on Access 2003/2007

  10. #10
    Join Date
    Jun 2013
    Posts
    81
    Hi apr pillai the code I've got is as follows:

    Code:
    Private Sub BalanceRepeatability1_AfterUpdate()
    
    'sub to find the PLE for each test point.  Need to check if average weight or comparator as different errors apply.
    
    Dim strIn As String
    Dim lMaxStep As Long
    Dim lStep As Long
    Dim strThisChar As String
    Dim strFindChar As String
    Dim lFstFoundChar As Long
    Dim strThisWord As String
    Dim lQuantity As Double
    Dim strUnit As String
    Dim lTestPoint As Double
    Dim lPLEPercent As Double
    Dim SQLq As String
    
    'first convert test point to g
    'extract numbers from the balance repeatability 1 string and calculate test point in g
    
    strIn = Me.BalanceRepeatability1 & " "
    lMaxStep = Len(strIn)
    
    strFindChar = Chr(32)
    lFstFoundChar = 1
    
    If lMaxStep > 0 Then
        For lStep = 1 To lMaxStep
            strThisChar = Mid(strIn, lStep, 1)
            If strThisChar = strFindChar Then
                strThisWord = Mid(strIn, lFstFoundChar, lStep - lFstFoundChar)
                If IsNumeric(strThisWord) = True Then
                    lQuantity = strThisWord
                    
                Else
                    strUnit = strThisWord
                End If
                lFstFoundChar = lStep + 1
            End If
        Next lStep
    End If
    
    'calculate the min quantity in g and save it in the BalanceMinG box
    If strUnit = "g" Then
        lTestPoint = lQuantity
        
    ElseIf strUnit = "kg" Then
        lTestPoint = lQuantity * 1000
    ElseIf strUnit = "mg" Then
        lTestPoint = lQuantity / 1000
    End If
    txtBalanceRepeat1 = lQuantity
    
    If Me.BalanceType = "Average Weight" Then
        'run select query to find AvgWtPlePercent (if there - need is null) where lquantity is between AvgWtFrom and AvgWtTo
        
        strSQLq = "SELECT AvgWtPlePercent " & _
                  "FROM tblAvgWeightPLEs " & _
                  "WHERE tblAvgWeightPLEs.[AvgWtFrom] <= " & Forms!frmBalance!txtBalanceRepeat1 & " And " & _
                  "tblAvgWeightPLEs.[AvgWtTo] >= " & Forms!frmBalance!txtBalanceRepeat1
        Debug.Print strSQLq
        
        
        DoCmd.RunSQL strSQLq ' note cannot use excute to run a select query 
     End If
        
    End Sub
    Thats about as far as I've got with it. Next step (once this bit works) is to detect if a null value is being identified and if it is carry out a second query, which does the same thing but looks for a value in AvgWtPLEg. Then depending on which query detects the value do either use the value as is, or carry out a calculation, with the final result being displayed in the frmBalance.

    Ahhh excellent I'll go look up how to asign temp variables now. So very glad we got the upgrade now :-).

    Thanks for your help.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •