Results 1 to 11 of 11
  1. #1
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38

    How to use between in DoCmd open form to search for range of numbers

    I have a list box for the age field. The use can type any number, and the appropriate age should be invoked in other form.
    I'm using the following code and don't know if I should use the "between" or "like". I appreciate the help.

    Age Field List box: Under 18
    18-25


    26-35
    Over 35
    If IsNull(Age) = False Then
    DoCmd.OpenForm "Actor Search", , , "[Age] LIKE '*" & Me!Age & "*'"
    End If

  2. #2
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Between is used for a range, a "from" and a "to". Like is used when partial entries are made, not the entire word(s).

    You have a list of available selections for the user, does that match your table exactly? If so, then it will be equal to.

  3. #3
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    You're right! Thanks aytee111.

  4. #4
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    I have the following code and it's not working.
    DoCmd.OpenForm "Actor Search", , , "[Age]=" & Me!cboAge

    cboAge is the combo box in the current form and I'm matching its value with the combo box Age in another form "Actor Search"

  5. #5
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    If those values are what is stored in the table then it is a text field and needs quotes
    "[Age]='" & Me!cboAge & "'"

    The form is being opened with this as a filter, which means that Access will use the record source of the form you are opening and will filter on what you tell it to. It is the same as using criteria in a query. It has nothing to do with any objects on that form.

  6. #6
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    Thank you so much aytee, it works!. I'm totally new to vb programming and trying to figure out the basic syntax.

  7. #7
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    Following is my code. It was working perfectly fine. But some how first and last name text fields won't invoke the result. They will just open an empty form.
    However, the rest of the search fields are working perfectly fine. I don't understand what could be the problem here. I didn't change any names of the text fields.

    Private Sub Command54_Click()
    If IsNull(FirstName) = False Then
    DoCmd.OpenForm "Actor Search", , , "[FirstName] LIKE '*" & Me!FirstName & "*'"
    End If
    If IsNull(LastName) = False Then
    DoCmd.OpenForm "Actor Search", , , "[LastName] LIKE '*" & Me!LastName & "*'"
    End If
    If IsNull(Age) = False Then
    DoCmd.OpenForm "Actor Search", , , "[Age]='" & Me!cboAge & "'"
    End If
    If IsNull(Ethnicity) = False Then
    DoCmd.OpenForm "Actor Search", , , "[Ethnicity]='" & Me!Ethnicity & "'"
    End If
    If IsNull(Gender) = False Then
    DoCmd.OpenForm "Actor Search", , , "[Gender]='" & Me!Gender & "'"
    End If
    If IsNull(UnionStatus) = False Then
    DoCmd.OpenForm "Actor Search", , , "[UnionStatus]='" & Me!UnionStatus & "'"
    End If
    End Sub

  8. #8
    aytee111 is offline Competent At Times
    Windows 10 Access 2013 64bit
    Join Date
    Nov 2011
    Location
    Nomad
    Posts
    3,936
    Change the If statements to use Me! in front of the names. Sometimes Access uses the "Name" of the textbox, sometimes the "Control Source", this may be a case of the incorrect name being used.

    If this doesn't change anything, immediately prior to the open form statement, add this line:
    Debug.Print "[FirstName] LIKE '*" & Me!FirstName & "*'"

    This will show you what the WHERE statement actually looks like (it will be displayed in the immediate window below, go to View if it isn't displaying). Open the record source of the Actor Search form (if it is a table, create a query based on it). Then in the criteria in the query copy and paste from the LIKE under the Firstname field.

    This is testing the criteria statement that you have in your open form statement. If this test query works fine then the problem is somewhere else.

    Note on naming conventions - don't use spaces anywhere, or any other special character except for underscore. Give names to your command buttons, don't ever use Access defaults for names anywhere.

  9. #9
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    Please use code tags around more than a few lines of code. It is easier to read then.
    Are you sure this will do what you want?
    If FirstName is not Null, then open the [Actor Search] form according to what's in FirstName control. Now process the next line, which is to end the IF.
    Now move on to the next line.
    If LastName is not Null, open the [Actor Search] form (but it's already open, so let's reopen it).
    Set the LastName control to be what's in the LastName control on the first form. This clears what's in the FirstName control of the opened form.
    Rinse and repeat as we go, until finally, the Union data is the only thing showing in the form while we watch the screen flicker each time it closes/opens.
    At least that's what I think will happen.
    The more we hear silence, the more we begin to think about our value in this universe.
    Paraphrase of Professor Brian Cox.

  10. #10
    ssanfu is offline Master of Nothing
    Windows 7 32bit Access 2010 32bit
    Join Date
    Sep 2010
    Location
    Anchorage, Alaska, USA
    Posts
    9,664
    I am also not sure of what you want to do. Do you want multiple options when opening a form or only one??

    For multiple options, see
    How to build a criteria string form the non-blank search boxes
    http://allenbrowne.com/ser-62code.html
    You can read the article on "Search criteria" by clicking on the link at the top of the page..

  11. #11
    Micron is online now Virtually Inert Person
    Windows 7 32bit Access 2007
    Join Date
    Jun 2014
    Location
    Ontario, Canada
    Posts
    12,737
    In case rabia is still monitoring this thread, based on your age combo values, IMHO you need to use a Select Case block with either < somevalue AND > somevalue or Between somevalue and somevalue. I'm not going to write it for this post since the OP hasn't replied in 3 days and needs to address the multiple opening of the form from within the same procedure.

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

Similar Threads

  1. Replies: 1
    Last Post: 02-09-2016, 07:03 AM
  2. Form to search for data by date range
    By andyt_2005 in forum Forms
    Replies: 3
    Last Post: 08-02-2014, 11:32 AM
  3. docmd.open form between dates condition
    By Ruegen in forum Programming
    Replies: 6
    Last Post: 09-22-2013, 11:23 PM
  4. Select / DoCmd to open another form
    By chrisjack001 in forum Access
    Replies: 1
    Last Post: 08-24-2010, 12:31 PM
  5. Search form with a date range
    By mantro174 in forum Forms
    Replies: 1
    Last Post: 02-11-2009, 10:45 PM

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