Problem Querying a Date Range 1

[Massinova]

I've modified a search/filter form (

http://www.tek-tips.com/faqs.cfm?fid=3702)

to accomodate my ADP project. Basically this code passes the search results to a list box. For the most part everything works well except for searching a date range. Basically, I want my users to be able to enter a begining date and an ending date to search for the needed agreement. My current code for date searching (see below code) will not pass any results.

- My form has two unbounded search boxes (txtStart_Date) and (txtEnd_date)

- The data in my date field is formated "short" (mm/dd/yyyy)

- Iv'e used #" and '%" as wild cards with no luck

Any help or suggestions would be greatly apprecitated.

Option Compare Database

Private Sub cmdSearch_Click()
'Set the Dimensions of the Module
Dim strSQL As String, strOrder As String, strWhere As String
Dim dbNm As Database
Dim qryDef As QueryDef
Set dbNm = CurrentDb()

'Constant Select statement for the RowSource
strSQL = "SELECT dbo.Location.Recno_Location, dbo.Location.Agreement_Number, dbo.Location.County, dbo.Location.State, dbo.Header.Owner, dbo.Header.Product, dbo.Header.Status, dbo.Header.Approved_Date " & _
"FROM (dbo.Header INNER JOIN dbo.Location ON dbo.Header.Agreement_Number =dbo.Location.Agreement_Number)"

strWhere = "WHERE"

strOrder = "ORDER BY dbo.Location.Recno_Location;"


'Set the WHERE clause for the Listbox RowSource if information has been entered into a field on the form
If Not IsNull(Me.txtAgrmnt) Then '<--If the textbox txtAgrmnt contains no data THEN do nothing
strWhere = strWhere & " (dbo.Location.Agreement_Number) Like '%" & Me.txtAgrmnt & "%'  AND" '<--otherwise, apply the LIKE statment to the QueryDef
End If

If Not IsNull(Me.txtCounty) Then
strWhere = strWhere & " (dbo.Location.County) Like '%" & Me.txtCounty & "%'  AND"
End If

If Not IsNull(Me.txtState) Then
strWhere = strWhere & " (dbo.Location.State) Like '%" & Me.txtState & "%'  AND"
End If

If Not IsNull(Me.txtOwner) Then
strWhere = strWhere & " (dbo.Header.Owner) Like '%" & Me.txtOwner& "%'  AND"
End If

If Not IsNull(Me.txtProduct) Then
strWhere = strWhere & " (dbo.Header.Product) Like '%" & Me.txtProduct & "%'  AND"
End If

If Not IsNull(Me.txtStatus) Then
strWhere = strWhere & " (dbo.Header.Status) Like '%" & Me.txtStatus & "%'  AND"
End If

If Not IsNull(Me.txtStart_Date) Or Not IsNull(Me.txtEnd_Date) Then
strWhere = strWhere & " (dbo.Header.Approved_Date) Between '%" & Format(Me.txtStart_Date, "mm/dd/yyyy") & "%' AND '%" & Format(Me.txtEnd_Date, "mm/dd/yyyy") & "%'));"
End If


'Remove the last AND from the SQL statment
strWhere = Mid(strWhere, 1, Len(strWhere) - 5)

'Pass the SQL to the RowSource of the listbox

Me.lstSearchInfo.RowSource = strSQL & " " & strWhere & "" & strOrder

End Sub

Private Sub lstSearchInfo_DblClick(Cancel As Integer)
'Open form seamless based on the Recno_Location from lstSearchInfo listbox

DoCmd.OpenForm "seamless", , , "[Recno_Location] = " & Me.lstSearchInfo, , acDialog

End Sub

Private Sub Close1_Click()

On Error GoTo Close1_Click_Err
    
    DoCmd.OpenForm "Agreements Switchboard", acNormal, "", "", , acNormal
    DoCmd.Close acForm, "frmSearchLocation"


close1_Click_Exit:
    Exit Sub

Close1_Click_Err:
    MsgBox Error$
    Resume close1_Click_Exit

End Sub

 

[lupins46]

Your code is written as if you are using an mdb file, not an adp.
Things like Database and currentdb() do not exist in an adp.
You need to look at Connections in help.

 

[cmmrfrds]

I am surprised that you did not get an error when passing this as part of the SQL String to sql server.

If Not IsNull(Me.txtStart_Date) Or Not IsNull(Me.txtEnd_Date) Then
strWhere = strWhere & " (dbo.Header.Approved_Date) Between '%" & Format(Me.txtStart_Date, "mm/dd/yyyy") & "%' AND '%" & Format(Me.txtEnd_Date, "mm/dd/yyyy") & "%'));"
End If

First wildcards are used with character data types in any of the sql databases. I assume dbo.Header.Approved_Date is a data type of datetime. The "Convert" function is used for date formatting in sql server.

For example.
Convert(varchar(10),dbo.Header.Approved_Date,101)
This is comparable to short date format. The 3rd parameter is the style and style of 101 is short date. Also, be careful if your date field contains "time" since the Between keyword does not take time into account on the ending date. Better to use >= and <= to cover the date range. In your case, it looks like you are masking the time out so it shouldn't make any difference.

 

[Massinova]

Thanks cmmrfrds, I'll insert the convert function today to see if that works. As for using "between" versus ">= & <=". I'll use what ever works. The only thing I'm looking at is having the user search records by a either a date range or other search parameter that works in conjunction with each other to filter out the desired records that meets the users search criteria. I’m operating under the assumption that the user will either enter both of the (txtStart_Date) and (txtEnd_date) fields or leave them both blank and use one of the other search criteria.

Lupins45, I'll be the first to admit that I'm not a programmer. This form was originally intended for MDB's and was found in Tek-Tips "MS-Access forms" section, All I did was manipulate it till I got it to work for my ADP project. As to why it works, for me, I have no clue …like I said, I’m not a programmer. Anyway, I'll look into the connections code and look for an appropriate substitute.

 

[Massinova]

Ok, I changed the following statement to read as follows:

If Not IsNull(Me.txtStart_Date) Or Not IsNull(Me.txtEnd_Date) Then
strWhere = strWhere & " Convert(varchar(10),dbo.Header.Approved_Date,101) Between '%" & Format(Me.txtStart_Date) & "%' AND '%" & Format(Me.txtEnd_Date) & "%'));"
End If

It still is not working. I'm sure it's something simple or something that I may have omitted.

Cmmrfrds, you said that wildcards are used with character data types in any of the sql databases, so does that mean that I'm ok using %?

 

[cmmrfrds]

Wildcard does not apply to datetime fields. You should be okay with the short date format.

If Not IsNull(Me.txtStart_Date) Or Not IsNull(Me.txtEnd_Date) Then
strWhere = strWhere & " Convert(varchar(10),dbo.Header.Approved_Date,101) Between '" & Format(Me.txtStart_Date) & "' AND '" & Format(Me.txtEnd_Date) & "'));"
End If

Debug.Print "strWhere = "; strWhere

Paste in the resulting string so that it can be reviewed.

 

[Massinova]

Well, I included the above line and still got no returns

If Not IsNull(Me.txtStart_Date) Or Not IsNull(Me.txtEnd_Date) Then
strWhere = strWhere & " Convert(varchar(10),dbo.Header.Approved_Date,101) Between '" & Format(Me.txtStart_Date) & "' AND '" & Format(Me.txtEnd_Date) & "'));"
End If

I modified the statement (see below code) to be in line with the rest of the code, by removing ));" and replacing it with AND", whereby I finally got some results. However, the results where not exactly what I wanted. Let me explain; If I queried a date range of lets say 10/10/1920 to 10/10/2003, I would get only get a return of only those records that met the criteria of 10/10 between 1920 and 2003 ...all other records between these date would be omitted?

If Not IsNull(Me.txtStart_Date) Or Not IsNull(Me.txtEnd_Date) Then
strWhere = strWhere & " Convert(varchar(10),dbo.Header.Approved_Date,101) Between '" & Format(Me.txtStart_Date) & "' AND '" & Format(Me.txtEnd_Date)  & "'  AND"
End If

Again, I'm not a programmer but am willing to learn and will often do things by trial and error to make them work.
Anyway, I did finally get it to work by replacing "Varchar(10)" with "Datetime"

If Not IsNull(Me.txtStart_Date) Or Not IsNull(Me.txtEnd_Date) Then
strWhere = strWhere & " Convert(Datetime,dbo.Header.Approved_Date,101) Between '" & Format(Me.txtStart_Date) & "' AND '" & Format(Me.txtEnd_Date) & "'  AND"
End If

So, what is the difference between "Datetime" and "Varchar(10)"? ...and why would I get such odd results using "Varchar(10)"?

 

[cmmrfrds]

What you did is good, that is, putting in the datetime. That is what it should be, my mistake by converting in to a varchar which would be okay for display purposes, but not in the case of searching. The varchar did a character search not a number range search as is necessary for a date field.

FYI. The datetime data type stores the date as a fractional number with the integer part being the number of days from a starting point. In the case of sql server that is 01/01/1900. The fractional part is the time of day as a fraction of 24 hours.

 

[Massinova]

Thanks for all your great help cmmrfds!

Your help and support to myself as well as to other's here at tek-tips has been greatly appreciated.

Have a great day =)