Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2011
    Posts
    30

    Unanswered: Filter form based on field contents

    Below is a piece of the code I am using for a search command button (cmdsearch) but I want to filter the results on my form so that records without contents in the couponcode field of my table (Contacttable.couponcode) will not be listed in the search results. I can't figure out what code to insert here with the current code I have. Thanks ahead of time.

    Dim strwhere As String
    Dim lnglen As Long

    If Not IsNull(Me.srchtxtlastname) Then
    strwhere = strwhere & "([Lastname] = """ & Me!srchtxtlastname & """) AND"
    End If
    ...

  2. #2
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Is this what you're after?

    strwhere = strwhere & "([Lastname] = """ & Me!srchtxtlastname & """) AND couponcode Is Not Null"
    Paul

  3. #3
    Join Date
    Apr 2011
    Posts
    30
    nope didn't work. Here is what my code looks like after typing in your suggestion:

    Dim strwhere As String
    Dim lnglen As Long

    If Not IsNull(Me.srchtxtlastname) Then
    strwhere = strwhere & "([Lastname] = """ & Me!srchtxtlastname & """) AND Couponcode Is Not Null AND "
    End If

    If Not IsNull(Me.srchtxtfirstname) Then
    strwhere = strwhere & "([Firstname] = """ & Me!srchtxtfirstname & """) AND Couponcode Is Not Null AND "
    End If

    If Not IsNull(Me.srchtxtemail) Then
    strwhere = strwhere & "([Email] = """ & Me!srchtxtemail & """) AND Couponcode Is Not Null AND "
    End If

    If Not IsNull(Me.srchtxtphone) Then
    strwhere = strwhere & "([Phone] = """ & Me!srchtxtphone & """) AND Couponcode Is Not Null AND "
    End If

    lnglen = Len(strwhere) - 5
    If lnglen <= 0 Then
    MsgBox "No criteria", vbInformation, "Nothing to do."
    Me.cmdregister.Enabled = False
    Else
    strwhere = Left$(strwhere, lnglen)
    Me.cmdregister.Enabled = True
    End If

  4. #4
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    What exactly does "didn't work" mean? You'd only want it once in the criteria, so I'd add it after the others. Also, you don't actually use the SQL for anything in that code.
    Paul

  5. #5
    Join Date
    Apr 2011
    Posts
    30
    I found this code on the internet and am using it so I can use a button to search records bases on what is typed into each textbox [Lastname] [Firstname] [Email] [Phone]. I would like for the search to only return customers who have not used a coupon so I want to exclude them in the search results. When I used the code you gave me those customers who have used a coupon still show up in my search results.

  6. #6
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Do you ever use strwhere? Based on the code posted, you build a string but never do anything with it.
    Paul

  7. #7
    Join Date
    Apr 2011
    Posts
    30
    Here the full fledged code. I use the string as a filter toward the bottom of the code here:

    Private Sub cmdsearch_Click()

    Dim strwhere As String
    Dim lnglen As Long

    If Not IsNull(Me.srchtxtlastname) Then
    strwhere = strwhere & "([Lastname] = """ & Me!srchtxtlastname & """) AND Grouponcode Is Not Null AND "
    End If

    If Not IsNull(Me.srchtxtfirstname) Then
    strwhere = strwhere & "([Firstname] = """ & Me!srchtxtfirstname & """) AND Grouponcode Is Not Null AND "
    End If

    If Not IsNull(Me.srchtxtemail) Then
    strwhere = strwhere & "([Email] = """ & Me!srchtxtemail & """) AND Grouponcode Is Not Null AND "
    End If

    If Not IsNull(Me.srchtxtphone) Then
    strwhere = strwhere & "([Phone] = """ & Me!srchtxtphone & """) AND Grouponcode Is Not Null AND "
    End If

    lnglen = Len(strwhere) - 5
    If lnglen <= 0 Then
    MsgBox "No criteria", vbInformation, "Nothing to do."
    Me.cmdregister.Enabled = False
    Else
    strwhere = Left$(strwhere, lnglen)
    Me.cmdregister.Enabled = True
    End If

    Me.Filter = strwhere
    Me.FilterOn = True

  8. #8
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Again, I wouldn't have it in every line, I'd just add it at the end. Also, I'm confused because you said "records without contents in the couponcode field...will not be listed in the search results", which would be the Is Not Null. Recently you said "I would like for the search to only return customers who have not used a coupon", which would imply you want records where that field is null. Take the coupon field out of all the other lines and see if this works:

    strwhere = Left$(strwhere, lnglen) & " AND Couponcode Is Null"

    Also just noticed that you originally said "Couponcode" but your last code has "Grouponcode". Which is it?
    Paul

  9. #9
    Join Date
    Apr 2011
    Posts
    30
    Oops my mistake it is supposed to be coupon code. I will try this out and get back to you. thanks in the meantime

  10. #10
    Join Date
    Apr 2011
    Posts
    30
    OK i took the code out of each line and inserted it where you suggested and the search is still showing customers who have a coupon code. The table I am using has the following fields: Lastname, Firstname, Email, Phone, Couponcode.

    If a customer's records is Null for the Couponcode then I do want those customers to show up in the search results. If the Record is Not Null (aka. has a coupon code in the field) then I do not want them to show up in the results.

  11. #11
    Join Date
    May 2005
    Location
    Nevada, USA
    Posts
    2,888
    Provided Answers: 6
    Can you post the db, or a representative sample?
    Paul

Tags for this Thread

Posting Permissions

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