Page 2 of 2 FirstFirst 12
Results 16 to 20 of 20
  1. #16
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397

    Also, my table doesn't have an age column. I have a query that calculates the age by obtaining date_of_birth column from the table
    . Use a query and calculate the age there - and your first few posts were referring to an age field.

    TxtAgeRange on form2 is a bound field obtaining the result from a query.
    ...
    How can I compare the results on my unbound combo box in form1 with TxtAgeRange ?
    don't understand what this means

  2. #17
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    Yes, before I was storing age in the table. But then I realized that age column is dynamic and will cause problem in future if I store it in the table. So I created a query which calculates the age.
    In form2 I have TxtAgeRange which has a control source linked with the age query.
    Actor_id Age1 Age Range
    1 70 Over 65
    2 30 26-35
    3 17 Under 18
    4 41 36-45
    5 18 18-25
    6 16 Under 18
    8 38 36-45
    9 52 46-55
    11 15 Under 18
    This is what I have inside the TxtAgeRange text box
    =DLookUp("[Age Range]","[Age Query]","[Age1] =" & Nz([Ages],"0"))

    My entire code is working perfectly fine when I delete the first line where I'm referrring to the TxtAgeRange. I'm getting an error to enter the parameter for TxtAgeRange.



    If IsNull(Ages) = False Then FilterStr = FilterStr & "([TxtAgeRange] =""" & Me!cboAge & """) AND "
    If IsNull(Ethnicity) = False Then FilterStr = FilterStr & "([Ethnicity]=""" & Me!Ethnicity & """)AND "
    If IsNull(Gender) = False Then FilterStr = FilterStr & "([Gender]=""" & Me!Gender & """) AND "
    If IsNull(FirstName) = False Then FilterStr = FilterStr & "([FirstName] LIKE ""*" & Me!FirstName & "*"") AND "
    If IsNull(LastName) = False Then FilterStr = FilterStr & "([LastName] LIKE ""*" & Me!LastName & "*"") AND "
    If IsNull(UnionStatus) = False Then FilterStr = FilterStr & "([UnionStatus]=""" & Me!UnionStatus & """) AND "




    lngLen = Len(FilterStr) - 5
    If lngLen > 0 Then
    FilterStr = Left$(FilterStr, lngLen)
    DoCmd.OpenForm "Actor Search", , , FilterStr
    End If
    End Sub



  3. #18
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    Just to clarify, following is the result of the age query

    Actor_id Age1 Age Range
    1 70 Over 65
    2 30 26-35
    3 17 Under 18
    4 41 36-45
    5 18 18-25
    6 16 Under 18
    8 38 36-45
    9 52 46-55
    11 15 Under 18

  4. #19
    CJ_London is offline VIP
    Windows 10 Access 2010 32bit
    Join Date
    Mar 2015
    Posts
    11,397
    don't know how else I can say this. The age calculation needs to be in the recordsource to your form that you are applying the filter - call it age, txtAgeRange, whatever you like but whatever you call it that what you will use here FilterStr & "([TxtAgeRange]. It sound like you have it in the recordsource of a completely different form

  5. #20
    rabia is offline Advanced Beginner
    Windows 10 Access 2013 64bit
    Join Date
    Aug 2017
    Posts
    38
    Ajax, I cannot thank you enough for sticking with me and solving my problem.
    I included the Age query (which is calculating the age) in the recordsource of FORM2.
    Now I'm not getting any error. Problem is solved.
    Once again I truly appreciate your help.

Page 2 of 2 FirstFirst 12
Please reply to this thread with any new information or opinions.

Similar Threads

  1. Searching for a non-numeric character in a field
    By DubCap01 in forum Programming
    Replies: 6
    Last Post: 02-02-2017, 05:00 AM
  2. Trouble Searching multivalued field
    By Gr0m in forum Access
    Replies: 4
    Last Post: 03-20-2013, 11:35 AM
  3. Searching by field on subform on main form
    By helen21112010 in forum Forms
    Replies: 1
    Last Post: 10-16-2012, 07:55 AM
  4. Searching marks field
    By Wilson in forum Access
    Replies: 3
    Last Post: 07-27-2012, 07:37 AM
  5. Searching a field on a record [HELP]
    By eduardo in forum Queries
    Replies: 1
    Last Post: 06-26-2012, 07:41 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