Filter Query

[DCrake]

I think we are going round in circles here. The frmSummary is the name of the subform datasheet contained on your main form. This is the form that you want to export to Excel. You want to do this after you have picked the columns to show and rows that relate to the data on the main form.

Your code

    Me.frmsummary.Form.RecordSource = "SELECT * FROM qrySummary " & Buildfilter
    Me.frmsummary.Requery

Is this being applied before you call the code

DoCmd.OutputTo acOutputForm, "frmSummary", acFormatXLS, "TblExport.xls", True

I suspect so.

You still have not told me what Buildfilter is saying, this is the crux of the matter. This is what is determining which rows to display.

David

 

[Guirg]

ohh ok heres a quick overview of buildfilter, because its massive at the moment:

Private Function Buildfilter() As Variant
    Dim varWhere As Variant
    Dim varItem As Variant
    Dim intIndex As Integer
    varWhere = Null
 
  ' Check for LIKE Description
    If Me.txtDescription > "" Then
        varWhere = varWhere & "[Description] LIKE ""*" & Me.txtDescription & "*"" And "
    End If
  ' Check for min date
    If Me.txtdatemin > "" Then
        Dim strSeparator
        Dim strTheMonth
        Dim strTheYear
        Dim strTheDay
        Dim strFinalDate
        strSeparator = "/"
        strTheMonth = Month(txtdatemin)
        strTheYear = Year(txtdatemin)
        strTheDay = Day(txtdatemin)
 strFinalDate = strTheMonth & strSeparator & _
                          strTheDay & strSeparator & _
                          strTheYear
        txtdatemin1 = strFinalDate
        varWhere = varWhere & "[DateRun] >= #" & Me.txtdatemin1 & "# And "
    End If
    
  ' Check for max date
    If Me.txtdatemax > "" Then
        Dim strSeparator1
        Dim strTheMonth1
        Dim strTheYear1
        Dim strTheDay1
        Dim strFinalDate1
        strSeparator1 = "/"
        strTheMonth1 = Month(txtdatemax)
        strTheYear1 = Year(txtdatemax)
        strTheDay1 = Day(txtdatemax)
        strFinalDate1 = strTheMonth1 & strSeparator1 & _
                          strTheDay1 & strSeparator1 & _
                          strTheYear1
        txtdatemax1 = strFinalDate1
        varWhere = varWhere & "[DateRun] <= #" & Me.txtdatemax1 & "# And "
    End If
...
...
...
 
 ' Column 1 of the option buttons, connecting them to the text boxes
    If optHumidity = -1 Then
        If Me.txtMin > "" Then
            varWhere = varWhere & "[humidity] >= " & Me.txtMin & " AND "
        End If
        If Me.txtMax > "" Then
            varWhere = varWhere & "[humidity] <= " & Me.txtMax & " AND "
        End If
...
...
...    
End If
If Me.txtMin > "" Then
    If IsNumeric(Me.txtMin) = False Then
        Raise.Err 3075
    End If
End If
If Me.txtMax > "" Then
    If IsNumeric(Me.txtMax) = False Then
        Raise.Err 3075
    End If
End If
If Me.txtMIN_1 > "" Then
    If IsNumeric(Me.txtMIN_1) = False Then
        Raise.Err 3075
    End If
End If
If Me.txtMAX_1 > "" Then
    If IsNumeric(Me.txtMAX_1) = False Then
        Raise.Err 3075
    End If
End If
  ' Check if there is a filter to return...
    If IsNull(varWhere) Then
       varWhere = ""
    Else
       varWhere = "WHERE " & varWhere
        
        ' strip off last "AND" in the filter
        If Right(varWhere, 5) = " AND " Then
            varWhere = Left(varWhere, Len(varWhere) - 5)
        End If
    End If
Buildfilter = varWhere
End Function

 

[DCrake]

Just done a bit of testing for myself and have come up with the following, which I think I mentioned very early on.

It makes no difference what you see on the form by way of a row filter when you perform an output to even if you nominate the subform datasheet it will only export the underlying query / table of that subform. Instead of outputting the form by name output the underlying query name with the appropriate buildfilter string.

To test this create a query based on a table without a filter on it.
Create a new form and add a sub form datasheet type based on this new query. Now look at the main form and the subform will contain all the records in the table.
Next add a textbox to the form and create a master / child link between the main and the sub to induce a filter.

Next add a button that exports the subform by name to xls.
Next enter something in to text box on the main form to filter the sub form and retry. Both results will be the same in Excel even thought the number of records on the subform changes.

What you see on the main/sub form can differ from what is exported using the outputto as the output to is using a different instance of the form than what is being displayed on screen.

David

 

[Guirg]

hmmm dont u love access? so in the SQL for the underlying union query i need to apply the buildfilter... or should i create another query and use

Select * From qrySummary  & Buildfilter

and just reference everything to the new query?

 

[DCrake]

Ok heres what I did

Dim dbsCurrent As Database
Dim qryTest As QueryDef
Dim BuildFilter As String

Dim OldSQL as string

BuildFilter = "Where FKWeekEnd = 1"

Set dbsCurrent = CurrentDb
Set qryTest = dbsCurrent.QueryDefs("Query1")
OldSQL = qryTest.SQL

qryTest.SQL = "SELECT * FROM MstDates " & BuildFilter
DoEvents

    DoCmd.OutputTo acQuery, "Query1", "MicrosoftExcelBiff8(*.xls)", "c:\tempq.xls", True, "", 0

Obviously you buildfilter string and query names need to be used.

Place this code on the export button on your main form. This will change the sql of the underlying query to reflect the filter applied. You may want to reset the sql back to what is was before you revised it

David

 

[Guirg]

damn it keeps messing up the union query qrysummary... ummmm a new approach is needed... is there a vb code to copy data from a form, so simple ctrl + a, ctrl +c and then ctrl + v?

 

[DCrake]

Take a look at this demo that uses the clip board

http://www.access-programmers.co.uk/forums/showthread.php?t=167208

 

[Guirg]

is there any way to have the field headings as a choise because then id be able to straight copy from the subform and can have my filter problems solved?

 

[Guirg]

for future refence of anyone with the same problem, this ended up doing the trick:

 Me.frmSummary1.Form.RecordSource = "SELECT * FROM qrySummary " & BuildFilter
    DoCmd.OutputTo acOutputForm, "frmSummary1", acFormatXLS, , True