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

    How to use Like command in DoCmd.OpenForm

    I'm reading information from Last Name text box and invoking related information in other form. I would like to use "Like" with my code. How can I do it.
    I appreciate any help. Thank you in advance.



    Following is my code;
    If IsNull(LastName) = False Then
    DoCmd.OpenForm "Actor Search", , , "[LastName]='" & Me!LastName & "'"
    End If

  2. #2
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you would use something like

    "[LastName] LIKE '*" & Me!LastName & "'*"

  3. #3
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    Thanks Ajax, but I'm getting compilation error
    DoCmd.OpenForm "Actor Search", , , "[FirstName] LIKE '*" & Me!FirstName & "'*"

  4. #4
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    sorry, typo on my part

    & "'*"

    should be

    & "*'"

  5. #5
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    It works! Thank you so much!

  6. #6
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    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.

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

  7. #7
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    you shouldn't be storing an age in your database - it will change every year. Better to store date or year or month/year of birth and calculate the age as and when required.
    don't know if I should use the "between" or "like".
    neither - use =

  8. #8
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    I didn't even think about that. You're right!
    So in the table I've created a date_of_birth field and have a calculated age field. Age field is a list box with following values
    Under 18
    18-25
    26-35
    Over 35
    Now how can I use between or like with docmd open form to read the user input from the textfield to invoke appropriate result?
    I appreciated the help.

  9. #9
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    Suggest modify your listbox to have 3 columns and hide the second two

    Desc.........Lower...Upper
    Under 18...0........17
    18-25.......18.......25
    26-35.......26.......35
    Over 35....36.......1000

    then you would use the between

    DoCmd.OpenForm "Actor Search", , , "[Age] Between " & Me!Age.column(1) & " AND " & Me!Age.column(2)

  10. #10
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    Ajax, I'm utilizing this technique for my other project. Everything is working fine, but for some reason when I choose the last option of the combo box which is over 65, the result retrieves all the rows. In my table, I don't have anyone over 65.
    What could be the problem?
    This is what I have in my combo;

    col 1.........col2...col3
    Under 18...0........17

    18-25.......18.......25
    26-35.......26.......35
    36-45 ......36.......45
    45- 55......46.......55
    56-65.......56.........65
    Over 65....66.........110

    If IsNull(cboAges) = False Then Str = Str & "([Age] BETWEEN """ & Me!cboAges.Column(1) & """ And """ & Me!cboAges.Column(2) & """) AND "

  11. #11
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    I tried doing the following, but it's not working;

    If IsNull(Combo34) = False Then Str = Str & "(([Age] BETWEEN """ & Me!Combo34.Column(1) & """ And """ & Me!Combo34.Column(2) & """) OR([Age] > 65)) AND "

  12. #12
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    I solved the problem.
    Thanks

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

Similar Threads

  1. DoCmd.OpenForm with multiple macros
    By Ekhart in forum Access
    Replies: 2
    Last Post: 03-02-2017, 01:38 PM
  2. DoCmd.OpenForm with 2 Where Conditions
    By CharissaBelle in forum Programming
    Replies: 3
    Last Post: 10-19-2016, 09:32 AM
  3. DoCmd.OpenForm Syntax Error
    By alsoto in forum Forms
    Replies: 3
    Last Post: 02-29-2012, 01:14 PM
  4. What actually happens at docmd.openform
    By Beorn in forum Programming
    Replies: 4
    Last Post: 01-05-2011, 02:19 PM
  5. DoCmd.OpenForm Modification
    By alsoto in forum Forms
    Replies: 6
    Last Post: 05-01-2009, 07:28 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