List Form

[jonnymenthol]

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.

 

[SQL_Hell]

hi, assuming you have 'date from' text box on your form


Try this:

If Len(TxtDateTo) > 0 And Len(TxtDateFrom)
Then

strsql = strsql & " AND DateTo Between '" & TxtDateFrom & "' And '" & TxtDateTo & "'"
End If

 

[Mile-O]

strsql = strsql & " AND DateTo Between '" & TxtDateFrom & "' And '" & TxtDateTo & "'"

Remember that text doesn't work the same way on betweens:

i.e. Consider this month

1-Jun-2004
10-Jun-2004
11-Jun-2004
12-Jun-2004
13-Jun-2004
14-Jun-2004
15-Jun-2004
16-Jun-2004
17-Jun-2004
18-Jun-2004
19-Jun-2004
2-Jun-2004
20-Jun-2004
21-Jun-2004
22-Jun-2004
23-Jun-2004
24-Jun-2004
25-Jun-2004
26-Jun-2004
27-Jun-2004
28-Jun-2004
29-Jun-2004
3-Jun-2004
30-Jun-2004
4-Jun-2004
5-Jun-2004
6-Jun-2004
7-Jun-2004
8-Jun-2004
9-Jun-2004



If you wanted all records with a date for the first week in June (let's say 1st to 7th) then all records with these dates will be returned.

1-Jun-2004
10-Jun-2004
11-Jun-2004
12-Jun-2004
13-Jun-2004
14-Jun-2004
15-Jun-2004
16-Jun-2004
17-Jun-2004
18-Jun-2004
19-Jun-2004
2-Jun-2004
20-Jun-2004
21-Jun-2004
22-Jun-2004
23-Jun-2004
24-Jun-2004
25-Jun-2004
26-Jun-2004
27-Jun-2004
28-Jun-2004
29-Jun-2004
3-Jun-2004
30-Jun-2004
4-Jun-2004
5-Jun-2004
6-Jun-2004
7-Jun-2004

Only the 8th and 9th won't be returned.

Instead, use the CDate() function:

strsql = strsql & " AND DateTo Between #" & CDate(TxtDateFrom) & "# And #" & CDate(TxtDateTo) & "#"

 

[SQL_Hell]

Is that true?

I have never had this problem, so long as you have the format of the text box set to date/time then it has always worked ok for me.

No saying you're wrong just never come across this problem before.

 

[Mile-O]

Saying that, I do Format my textboxes to dd-mmm-yyyy format which, essentially makes it a string.

I just think the CDate() function is a more robust method.

 

[Rich]

You'll have this problem if you return/compare dates in strings, and sql

 

[SQL_Hell]

Saying that, I do Format my textboxes to dd-mmm-yyyy format which, essentially makes it a string.

I just think the CDate() function is a more robust method.

Cool, cheers man. Always good to learn extra stuff.


Just out of interest what do you guys do for a living? I am a SQL Server DBA.

 

[Mile-O]

Just out of interest what do you guys do for a living? I am a SQL Server DBA.

Unemployed. Nobody would have me.

Only kidding - data analyst.

 

[jonnymenthol]

Thanks....

Thanks for that fellas, I'll have a look at that.

J.