Missing something

[Comic1]

I have this coding for a user form that allows them to select a date range & a status, Open or Closed. This is the coding I have but I think there is something missing.

Private Sub OK_Click()
' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
' Get the database and stored query
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("Range")
' Loop through the selected items in the list box and build a text string
     If Me!Status1.ItemsSelected.Count > 0 Then
        For Each varItem In Me!Status1.ItemsSelected
            strCriteria = strCriteria & "[Vendor Hotline Log].Status = " & Chr(34) _
                          & Me!Status1.ItemData(varItem) & Chr(34) & " OR "
        Next varItem
        strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    End If
' Date Range criteria
    strCriteria = strCriteria & "StartDate = " & "EndDate"
' Build the new SQL statement incorporating the string
    strSQL = "SELECT * FROM [Vendor Hotline Log] " & _
             "WHERE " & strCriteria & ";"
' Apply the new SQL statement to the query
     Debug.Print strSQL
     qdf.SQL = strSQL
' Open the query
    DoCmd.OpenReport "Date Range", acViewPreview
    DoCmd.Close acForm, "Search Criteria Form", acSaveNo
End Sub

 

[MarkK]

I don't see what's missing. Maybe it's hard to see because it's missing.
What makes you think something is missing?
Cheers,

 

[boblarson]

One problem I see is this:

strCriteria = strCriteria & "StartDate = " & "EndDate"


What is EndDate and where should it be coming from? Right now it is coded to use the explicit value of "EndDate" as a string and not a date, which I'm assuming you want. For a date field on the same form as this code is on, it would be something like this:

strCriteria = strCriteria & "StartDate = #" & Me.EndDate & "#"

But also I don't think you have a space in between your criteria (nor an And as you have stripped it off) so you would need

strCriteria = strCriteria & " AND StartDate = #" & Me.EndDate & "#"

 

[Comic1]

That's seems to only give me able to choose a start date but I need to choose both a start date & end date

 

[Comic1]

You are a comedian! You're right it would be hard to see

 

[Comic1]

Here is my new coding & what is in the immediate window. It's not giving me any date even though it should be.

Date Range criteria
    Debug.Print strSQL
    strCriteria = strCriteria & " AND StartDate = #" & Me.StartDate & "#" & " AND EndDate = #" & Me.endDate & "#"

Immediate Window
SELECT * FROM [Vendor Hotline Log] WHERE [Vendor Hotline Log].Status = "Closed" AND StartDate = #1/10/2011# AND EndDate = #1/21/2011#;

 

[boblarson]

So you have two fields where you are storing dates? A starting date and an ending date? If you want the range between the two you would need something like this (otherwise your current is looking for a specific date for each and it would have to have the exact dates in each field to yield data.

strCriteria = strCriteria & " AND StartDate >= #" & Me.StartDate & "#" & " AND EndDate <= #" & Me.endDate & "#"

 

[Comic1]

The dates are stored in vendor table

 

[Comic1]

That worked but my query now only has the dates I selected & the status I selected. It's like it only works once & you have to select the fields in the query again. Why would it do that?

 

[boblarson]

That worked but my query now only has the dates I selected & the status I selected. It's like it only works once & you have to select the fields in the query again. Why would it do that?

Oh, I think I understand your question. I didn't for a moment, but now I do. When you modify the SQL in a query using a QueryDef, you need to set it back to the original if you don't want the criteria to stay as was put in by the code. When you modify a query through a QueryDef it unless you reset it, it becomes a permanent change when you close the querydef.

So, you should use this at the beginning of your code:

Dim strOldSQL As String 
 
strOldSQL = qdf.SQL

(before you make any changes)

and then when you are done with it all use

qdf.SQL = strOldSQL 
qdf.Close
Set qdf = Nothing

at the end.

 

[Comic1]

That makes sense. thanks.
I ran it now but for some reason it is using the date range selected but not the status selected. It gives all data. It doesn't make sense because in my immediate window it shows that the status was selected.

Immediate window
SELECT * FROM [Vendor Hotline Log] WHERE [Vendor Hotline Log].Status = "Closed" AND StartDate >= #1/10/2011# AND EndDate <= #1/14/2011#;

Private Sub OK_Click()
' Declare variables
    Dim db As DAO.Database
    Dim qdf As DAO.QueryDef
    Dim varItem As Variant
    Dim strCriteria As String
    Dim strSQL As String
    Dim strOldSQL As String
' Get the database and stored query
    Debug.Print strSQL
    Set db = CurrentDb()
    Set qdf = db.QueryDefs("Range")
    strOldSQL = qdf.SQL
' Loop through the selected items in the list box and build a text string
     If Me!Status1.ItemsSelected.Count > 0 Then
        For Each varItem In Me!Status1.ItemsSelected
            strCriteria = strCriteria & "[Vendor Hotline Log].Status = " & Chr(34) _
                          & Me!Status1.ItemData(varItem) & Chr(34) & " OR "
        Next varItem
        strCriteria = Left(strCriteria, Len(strCriteria) - 3)
    End If
' Date Range criteria
    Debug.Print strSQL
    strCriteria = strCriteria & " AND StartDate >= #" & Me.StartDate & "#" & " AND EndDate <= #" & Me.endDate & "#"

' Build the new SQL statement incorporating the string
    strSQL = "SELECT * FROM [Vendor Hotline Log] " & _
             "WHERE " & strCriteria & ";"
' Apply the new SQL statement to the query
     Debug.Print strSQL
     qdf.SQL = strSQL
     qdf.SQL = strOldSQL
     qdf.Close
     Set qdf = Nothing
' Open the query
    DoCmd.OpenReport "Date Range", acViewPreview
    DoCmd.Close acForm, "Search Criteria Form", acSaveNo

End Sub

 

[boblarson]

Umm, because you are setting the SQL back too soon. You have:

Debug.Print strSQL
qdf.SQL = strSQL
qdf.SQL = strOldSQL

which means you are changing the qdf's SQL string and then immediately setting it back to the old. That part has to go AFTER you open the report.

 

[Comic1]

Oh, so something like this?

 Build the new SQL statement incorporating the string
    strSQL = "SELECT * FROM [Vendor Hotline Log] " & _
             "WHERE " & strCriteria & ";"
 
' Open the report
    DoCmd.OpenReport "Date Range", acViewPreview
' Apply the new SQL statement to the query
    Debug.Print strSQL
    qdf.SQL = strSQL
    qdf.SQL = strOldSQL
    qdf.Close
    Set qdf = Nothing
    DoCmd.Close acForm, "Search Criteria Form", acSaveNo
    
End Sub

 

[boblarson]

Oh, so something like this?

 Build the new SQL statement incorporating the string
    strSQL = "SELECT * FROM [Vendor Hotline Log] " & _
             "WHERE " & strCriteria & ";"
 
' Open the report
    DoCmd.OpenReport "Date Range", acViewPreview
' Apply the new SQL statement to the query
    Debug.Print strSQL
    qdf.SQL = strSQL
    qdf.SQL = strOldSQL
    qdf.Close
    Set qdf = Nothing
    DoCmd.Close acForm, "Search Criteria Form", acSaveNo
 
End Sub

No, come on think about it. Why would you set qdf.SQL = strSQL and then qdf.SQL = strOldSQL right after? That makes no sense. I guess I'll have to spell it out for you:

[COLOR=red]' save the original[/COLOR]
[COLOR=red]strOldSQL = qdf.SQL[/COLOR]
 
    strSQL = "SELECT * FROM [Vendor Hotline Log] " & _
             "WHERE " & strCriteria & ";"
 
[COLOR=red]' Set the new SQL[/COLOR]
[COLOR=red]qdf.SQL = strSQL[/COLOR] 
 
' Open the report
    DoCmd.OpenReport "Date Range", acViewPreview
 
    qdf.SQL = strOldSQL
    qdf.Close
    Set qdf = Nothing
 
    DoCmd.Close acForm, "Search Criteria Form", acSaveNo
 
End Sub

 

[Comic1]

Thanks , I'm new to this & just starting to learn, but I'm sure you knew that. My goal is to become as good as the Great boblarson.