Results 1 to 3 of 3

Thread: List Form

  1. #1
    Join Date
    Jun 2004
    Posts
    26

    Unanswered: List Form

    Hello,

    I have a list form in my database, which when opened displays certain information from my records (name, date from, date to etc).

    I also have a few text boxes which when text is entered into them, the list box on the form is filtered accordingly (so if I type my name, only the records I have entered are shown in the list box.

    However, I have a problem with the date boxes, as I want to use a between statement, so if the record has dates between a range, then it should show in the list, all others should be excluded.

    The code I am using is below (however, I have used this code for another DB, and just ripped it from there):

    Private Sub TxtDateTo_AfterUpdate()
    Dim strsql As String
    strsql = "SELECT ID,Name,Event,Member,DateFrom,DateTo FROM TblMain WHERE 1 = 1"
    If Len(TxtDateFrom) > 0 Then
    strsql = strsql & " AND DateFrom like '*" & TxtDateFrom & "*'"
    End If
    If Len(TxtName) > 0 Then
    strsql = strsql & " AND Technician like '*" & TxtTechnician & "*'"
    End If
    If Len(TxtMember) > 0 Then
    strsql = strsql & " AND ProductSupport like '*" & TxtProductSupport & "*'"
    End If
    If Len(TxtDateTo) > 0 Then
    strsql = strsql & " AND DateTo like '*" & TxtDateTo & "*'"
    End If
    strsql = strsql & " Order by Name;"
    Lstcustomers.RowSource = strsql
    Call Countrecords
    If Lstcustomers.ListCount = 0 Then
    MsgBox "No Records Were Found, Please Click on Clear and Redefine Your Search", vbOKOnly, "No Records Found"
    End If
    End Sub

    However, I can't seem to get it to work for the dates, and am not sure of the syntax I need in order to get the between statement to work.

    Can anyone please help ?

    If none of the above makes sense, please let me know, either on here, or by mailing jonnymenthol@yahoo.com

    Thanks in advance

    Jonny.

  2. #2
    Join Date
    Oct 2003
    Location
    St Helens UK
    Posts
    13
    Have you tried putting the # character before your date starts and at the end of your date?

    e.g. #08/06/2004#

    Just a wild shot.

  3. #3
    Join Date
    Feb 2003
    Location
    Wichita,KS
    Posts
    44
    Try it this way.
    Code:
    If Len(TxtDateFrom) > 0 Then
    strsql = strsql & " AND DateFrom >= #" & TxtDateFrom & "#"
    End If
    If Len(TxtName) > 0 Then
    strsql = strsql & " AND Technician like '*" & TxtTechnician & "*'"
    End If
    If Len(TxtMember) > 0 Then
    strsql = strsql & " AND ProductSupport like '*" & TxtProductSupport & "*'"
    End If
    If Len(TxtDateTo) > 0 Then
    strsql = strsql & " AND DateTo <= #" & TxtDateTo & "#"
    End If

Posting Permissions

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