Results 1 to 7 of 7
  1. #1
    phbryan is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Mar 2015
    Posts
    6

    Between... and query

    Hello,



    I am new to access and need some help.
    I am trying to use the Between [Enter first number] And [Enter last number] query command. I want access to determine if the two numbers or out of range. For example, if the difference is greater than 10, return an error. So 0-10 would work but not 0-11. Also, if the last number is smaller than the first number return an error (10-0 vs 0-10).

    I am so unfamiliar with access. Any help would be great!

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    your description is not very clear - can you provide some example data and the required outcome from that example

  3. #3
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    If you want to validate the criteria prior to applying it to the query, you would do that at the form level. For example you would have two text boxes on a form where users would enter the beginning and ending values so you could validate it before you run the query and display the results.

  4. #4
    phbryan is offline Novice
    Windows 7 32bit Access 2013
    Join Date
    Mar 2015
    Posts
    6
    I was able to attach a file after deleting alot of information and compressing it.
    The "enter label data" query enters data into "tblProfile Production." The enter first control number prompt would be where i'd like to enter my restrictions
    Attached Files Attached Files

  5. #5
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    still don't understand the issue - the query works, enter 1, enter 10 and you get the relevant records back

    Only comment is the control field is a double (so if a value was 10.2 it won't be returned between 1 and 10) and you don't have a primary key. You also have reserved words as field name (date, control)

  6. #6
    Beetle is offline Unrelatable
    Windows 7 64bit Access 2010 32bit
    Join Date
    May 2011
    Location
    Camp Swampy (Denver, CO)
    Posts
    207
    I'll reiterate my previous post. If you want to be able to validate any criteria the user enters you need to do so in a form. Example code, untested so may have typos;

    Code:
    If Not IsNumeric(txtStart) Or Not IsNumeric(txtEnd) Then
        MsgBox "Both values must be numeric"
    ElseIf txtEnd < txtStart Then
        MsgBox "End value cannot be less than Start value"
    ElseIf txtEnd - txtStart > 10 Then
        MsgBox "Variance cannot be more than 10"
    Else
        'code to open or run your query goes here
    End If

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    If you want to be able to validate any criteria the user enters you need to do so in a form.
    Ah - now I understand

Please reply to this thread with any new information or opinions.

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
Other Forums: Microsoft Office Forums