Results 1 to 4 of 4
  1. #1
    gpierce9 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    NJ
    Posts
    19

    Qry Using BETWEEN for 2 values.

    1 - I have a qry criteria that WORKS using EG Between 700 and 99999



    Also works from a button on my form frmHousekeeping .

    2 - However, rather than hardcode the 2 values, I am want the Qry too look to a Form
    for the 2 values. So I made two unbound fields called 8161StartADT$ and 8161EndADT$.

    I changed the Qry criteria to be:
    EG Between [frmHousekeeping]![8161StartADT$] And [frmHousekeeping]![8161EndADT$]

    3 - When I run the form button, I get a "Enter Parameter Value' message.

    So I cancel it.

    What am I doing wrong?? thanks alot. Glen

  2. #2
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    First, I would NOT use special characters ($) in an object name.
    You don't have the correct syntax for referring to a form and it's controls. You have to start with the Forms! collection and drill down from there, and don't use brackets (unless you start a form name with such a special character or a number, which you also should not do):
    Forms!frmHousekeeping.[8161StartADT$] And Forms!frmHousekeeping![8161EndADT$]

    If there is a chance that the query could run with either control empty, or contain bad data (such as text when it should only be a number) you might want to consider preventing the errors or invalid query results (or none at all) that you may get.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  3. #3
    gpierce9 is offline Novice
    Windows 10 Access 2016
    Join Date
    Jan 2018
    Location
    NJ
    Posts
    19

    Thanks. I used your all your ideas and it works.

    Quote Originally Posted by Micron View Post
    First, I would NOT use special characters ($) in an object name.
    You don't have the correct syntax for referring to a form and it's controls. You have to start with the Forms! collection and drill down from there, and don't use brackets (unless you start a form name with such a special character or a number, which you also should not do):
    Forms!frmHousekeeping.[8161StartADT$] And Forms!frmHousekeeping![8161EndADT$]

    If there is a chance that the query could run with either control empty, or contain bad data (such as text when it should only be a number) you might want to consider preventing the errors or invalid query results (or none at all) that you may get.

    Thanks Micron. I did all you said. It works fine now. Here is the final criteria used.

    Between [Forms]![frmHousekeeping]![StartADT8161] And [Forms]![frmHousekeeping]![EndADT8161]

    thanks alot. Glen

  4. #4
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    You should research naming conventions and adopt one. It will help. Using names that force you to use brackets leads to errors. Glad to have helped.
    Last edited by Micron; 01-15-2018 at 09:34 AM. Reason: Spelin

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

Similar Threads

  1. Replies: 2
    Last Post: 04-17-2017, 01:40 PM
  2. Replies: 3
    Last Post: 10-30-2016, 05:50 AM
  3. Replies: 19
    Last Post: 09-09-2014, 01:36 AM
  4. Replies: 4
    Last Post: 07-10-2014, 02:24 PM
  5. Replies: 1
    Last Post: 03-27-2010, 06:13 AM

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