Results 1 to 10 of 10
  1. #1
    Join Date
    Jun 2006
    Posts
    9

    Unanswered: Searching by derived field

    hi, im hoping someone can help. I posted this on another site, but no joy.

    i have a form with a derived field. It shows whether my record is
    active/expired based on a date calculation. Active From is a field in the main table.

    Here is the statement attached to the unbound text box, creating the derived field.
    =IIf([ActiveFrom]+365>Date(),"Active","Expired")

    What I want is to be able to search by Active/Expired members. Below is an example of my search code. It runs perfectly except for the active/expired search. section

    If Not IsNull(Me.cboMemberType) Then
    strWhere = strWhere & "([Member Type] Like ""*" & Me.cboMemberType &
    "*"") AND "
    End If

    If Not IsNull(Me.cboMemberStatus) Then
    strWhere = strWhere & "(=IIf([ActiveFrom]+365>Date(),"Active",
    "Expired"") AND "
    End If


    Could someone take pity on me? I think its an easy enough problem, but im not very familiar with the ins-and-outs of vb.

    Thanks

  2. #2
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Hi Plink

    Welcome to the forum

    How about:
    Code:
    If Me.cboMemberStatus = "Active" Then
    strWhere = strWhere & "DATEADD("y", 1, [ActiveFrom]) >=Date() AND "
    ElseIf Me.cboMemberStatus = "Expired" 
    strWhere = strWhere & "DATEADD("y", 1, [ActiveFrom]) <Date() AND "
    End If
    The dateadd handles leap years otherwise it is about the same as your logic

    HTH
    Testimonial:
    pootle flump
    ur codings are working excelent.

  3. #3
    Join Date
    Jun 2004
    Location
    Terrapin Nation
    Posts
    205
    Quote Originally Posted by Plink
    hi, im hoping someone can help. I posted this on another site, but no joy.

    i have a form with a derived field. It shows whether my record is
    active/expired based on a date calculation. Active From is a field in the main table.

    Here is the statement attached to the unbound text box, creating the derived field.
    =IIf([ActiveFrom]+365>Date(),"Active","Expired")

    What I want is to be able to search by Active/Expired members. Below is an example of my search code. It runs perfectly except for the active/expired search. section

    If Not IsNull(Me.cboMemberType) Then
    strWhere = strWhere & "([Member Type] Like ""*" & Me.cboMemberType &
    "*"") AND "
    End If

    If Not IsNull(Me.cboMemberStatus) Then
    strWhere = strWhere & "(=IIf([ActiveFrom]+365>Date(),"Active",
    "Expired"") AND "
    End If


    Could someone take pity on me? I think its an easy enough problem, but im not very familiar with the ins-and-outs of vb.

    Thanks






    I am not sure what you are asking but let me give it a try:

    I think you need to change your code to see which status (active/inactive) they are searching for and then set your where criteria based on that. For example:


    If (Me.cboMemberStatus) = "active" then
    strWhere = strWhere & "[ActiveFrom]+365>Date() AND "
    else
    strWhere = strWhere & "[ActiveFrom]-365>Date() AND "
    End If




    - edit: looks like pootle beat me to it. go with his calcs.

  4. #4
    Join Date
    Jun 2006
    Posts
    9
    Hey guys.. thanks for replying so quickly..

    I look the look of your approach Pootle Flump..

    But when I insert your code, Im getting 2 errors, an expected end of statement on "y" and a general syntax error....

    The other thing, is the user wouldn't necessarily use this search field, so u think maybe I begin the code with an If not IsNull bit.?

    Thanks

  5. #5
    Join Date
    Jun 2006
    Posts
    9
    This is what Im trying now..

    If Not IsNull(Me.cboMemberStatus) Then
    If Me.cboMemberStatus = "Active" Then
    strWhere = strWhere & DateAdd("y", 1, [ActiveFrom]) >= Date And ""
    ElseIf Me.cboMemberStatus = "Expired" Then
    strWhere = strWhere & DateAdd("y", 1, [ActiveFrom]) < Date And ""
    End If

    Weirdly enough the debugger is stopping at the very last End sub now saying compile error block if without end if.

    Does the code look right to u? thoughts....?

    thanks

  6. #6
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Ooops - my error:
    Code:
    If Me.cboMemberStatus = "Active" Then strWhere = strWhere & "DATEADD('y', 1, [ActiveFrom]) >=Date() AND "
    ElseIf Me.cboMemberStatus = "Expired" strWhere = strWhere & "DATEADD('y', 1, [ActiveFrom]) <Date() AND " End If
    And no - there is no need to check for isnull. Look at the code again - if there is no data in the combo then neither of the conditions will be met so strWhere will not be altered.
    Testimonial:
    pootle flump
    ur codings are working excelent.

  7. #7
    Join Date
    Jun 2006
    Posts
    9
    Ahhh!!! Thanks Pootle... I think im within a hairs breadth of this... I have it running, kinda... whats happening now is the first search runs fine.. but... if i try to do anything else...

    The data has been changed, another user edited this record and saved the changes before you attempted to save your changes. re-edit the record.

    But once i hit okay, i can try it again and it does another search...

    I hate that message, its bugging me on another command button, but thats a story for another day.






    ... Eureka?

  8. #8
    Join Date
    Jun 2006
    Posts
    9
    Okay.. maybe ignore that last bit about the error message... havin trouble tho.. its only returning all values as expired, whether they are or not.

  9. #9
    Join Date
    Jun 2006
    Posts
    9
    Hey hey hey.. if anyone is still lookin at this thread, got it sorted... hopefully

    instead of Dateadd 'y'
    tried, Dateadd 'yyyy'

    seems to work... thanks pootle.

  10. #10
    Join Date
    Feb 2004
    Location
    One Flump in One Place
    Posts
    14,912
    Duuuhhhhhh.
    'y' = Day of Year
    'yyyy' = Year

    sorry Plink
    Testimonial:
    pootle flump
    ur codings are working excelent.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •