Query not combining and filtering values based on a form

[mayestom]

Also added the two combo boxes and text boxes to the form.

 

[GinaWhipp]

Oops, replace that period with a comma, sorry my bad!

 

[mayestom]

Okay, SQL for the query (qryDate):

SELECT Year([Purchase_Date]) AS MyYear, Format([Purchase_Date],"m-yyyy") AS MyMonthYear
FROM Equipment;

And added the 2 combo and 2 text fields on the form (frmSupplierDescriptionCodeqry)

 

[GinaWhipp]

Where is the rest of the query? Remember the query has to have the fields you want to display in the detail section of the Form or are we doing something else here?

 

[mayestom]

Ok I wasn't sure if I needed to create a new query or add the formatted date fields to the existing query that the form runs off of. So this is the SQL of the existing query with the formatted date fields added. I also added the actual date field in there, should it be?

SELECT Equipment.Project_Name, Equipment.Unit_Cost, Equipment.Quantity_Purchase, [unit_cost]*[quantity_purchase] AS Total, Equipment.[Location/Affiliate_Name], Equipment_Spec.Description, [Test code Query].[Madeup code], Supplier.Supplier_Name, Equipment.Purchase_Date, Year([Purchase_Date]) AS MyYear, Format([Purchase_Date],"m-yyyy") AS MyMonthYear
FROM Supplier INNER JOIN (Project INNER JOIN ((Equipment_Spec INNER JOIN [Test code Query] ON Equipment_Spec.ID = [Test code Query].ID) INNER JOIN Equipment ON Equipment_Spec.ID = Equipment.Equipment_ID) ON Project.Project_Name = Equipment.Project_Name) ON Supplier.Supplier_ID = Equipment.Supplier_Name;

 

[GinaWhipp]

Now I am confused... is this the one you are working on by yourself or do you still need help?

 

[mayestom]

No, I still need help. This was adding the date filter to the existing form named frmSupplierDescriptionCodeqry, that you helped me create.

Okay, then when you create the query for the Form you will need to add tow fields one that Formats the Date to show Year and the other to Format the Date to show Month/Year. Once you do that put two Combo Boxes and two Text Boxes on your Form, see below...

1. cboYear

2. cboMonthYear

3. txtFromDate

4. txtToDate

Then put the same buttons as before and finally, post the SQL of your query.

I've added the two combo boxes and two text boxes to the form and this is the SQL of the query:

SELECT Equipment.Project_Name, Equipment.Unit_Cost, Equipment.Quantity_Purchase, [unit_cost]*[quantity_purchase] AS Total, Equipment.[Location/Affiliate_Name], Equipment_Spec.Description, [Test code Query].[Madeup code], Supplier.Supplier_Name, Equipment.Purchase_Date, Year([Purchase_Date]) AS MyYear, Format([Purchase_Date],"m-yyyy") AS MyMonthYear
FROM Supplier INNER JOIN (Project INNER JOIN ((Equipment_Spec INNER JOIN [Test code Query] ON Equipment_Spec.ID = [Test code Query].ID) INNER JOIN Equipment ON Equipment_Spec.ID = Equipment.Equipment_ID) ON Project.Project_Name = Equipment.Project_Name) ON Supplier.Supplier_ID = Equipment.Supplier_Name;

But I'm not sure on how to continue?

 

[GinaWhipp]

Okay, long day grandkids ran me ragged... will look at tomorrow!

 

[mayestom]

I totally understand.

 

[GinaWhipp]

Sorry did not get a chance to look earlier, here you go, place behind Filter button...

    'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
 
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.cboYear) Then
        strWhere = strWhere & "(Year([Purchase_Date]) = """ & Me.cboYear & """) AND "
    End If
 
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.cboMonthYear) Then
        strWhere = strWhere & "(Format([Purchase_Date],'m-yyyy') = """ & Me.cboMonthYear & """) AND "
    End If
 
    'Date field example. Use the format string to add the # delimiters and get the right international format.
    If Not IsNull(Me.txtFromDate) Then
        strWhere = strWhere & "([Purchase_Date] >= " & Format(Me.txtFromDate, conJetDate) & ") AND "
    End If
    'Another date field example. Use "less than the next day" since this field has times as well as dates.
    If Not IsNull(Me.txtToDate) Then   'Less than the next day.
        strWhere = strWhere & "([Purchase_Date] <= " & Format(Me.txtToDate + 1, conJetDate) & ") AND "
    End If
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
 
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If

 

[mayestom]

So I'm replacing what's behind the filter button now with what you just posted?
This is what's there now:

'Purpose:   This module illustrates how to create a search form, _
            where the user can enter as many or few criteria as they wish, _
            and results are shown one per line.
'Note:      Only records matching ALL of the criteria are returned.
'Author:    Allen Browne (allen@allenbrowne.com), June 2006.
Option Compare Database
Option Explicit
Private Sub cmdFilter_Click()
    'Purpose:   Build up the criteria string form the non-blank search boxes, and apply to the form's Filter.
    'Notes:     1. We tack " AND " on the end of each condition so you can easily add more search boxes; _
                        we remove the trailing " AND " at the end.
    '           2. The date range works like this: _
                        Both dates      = only dates between (both inclusive. _
                        Start date only = all dates from this one onwards; _
                        End date only   = all dates up to (and including this one).
    Dim strWhere As String                  'The criteria string.
    Dim lngLen As Long                      'Length of the criteria string to append to.
    Const conJetDate = "\#mm\/dd\/yyyy\#"   'The format expected for dates in a JET query string.
 
    '***********************************************************************
    'Look at each search box, and build up the criteria string from the non-blank ones.
    '***********************************************************************
    'Text field example. Use quotes around the value in the string.
    If Not IsNull(Me.cboEquipment) Then
        strWhere = strWhere & "([Madeup Code] = """ & Me.cboEquipment & """) AND "
    End If
 
    'Number field example. Do not add the extra quotes.
    If Not IsNull(Me.cboSupplier.Column(1)) Then
     strWhere = strWhere & "([Supplier_Name] = """ & Me.cboSupplier.Column(1) & """) AND "
    End If

 
    '***********************************************************************
    'Chop off the trailing " AND ", and use the string as the form's Filter.
    '***********************************************************************
    'See if the string has more than 5 characters (a trailng " AND ") to remove.
    lngLen = Len(strWhere) - 5
    If lngLen <= 0 Then     'Nah: there was nothing in the string.
        MsgBox "No criteria", vbInformation, "Nothing to do."
    Else                    'Yep: there is something there, so remove the " AND " at the end.
        strWhere = Left$(strWhere, lngLen)
        'For debugging, remove the leading quote on the next line. Prints to Immediate Window (Ctrl+G).
        'Debug.Print strWhere
 
        'Finally, apply the string as the form's Filter.
        Me.Filter = strWhere
        Me.FilterOn = True
    End If
End Sub
Private Sub cmdReset_Click()
    'Purpose:   Clear all the search boxes in the Form Header, and show all records again.
    Dim ctl As Control
    
    'Clear all the controls in the Form Header section.
    For Each ctl In Me.Section(acHeader).Controls
        Select Case ctl.ControlType
        Case acTextBox, acComboBox
            ctl.Value = Null
        Case acCheckBox
            ctl.Value = False
        End Select
    Next
    
    'Remove the form's filter.
    Me.FilterOn = False
End Sub
Private Sub Form_BeforeInsert(Cancel As Integer)
    'To avoid problems if the filter returns no records, we did not set its AllowAdditions to No.
    'We prevent new records by cancelling the form's BeforeInsert event instead.
    'The problems are explained at http://allenbrowne.com/bug-06.html
    Cancel = True
    MsgBox "You cannot add new clients to the search form.", vbInformation, "Permission denied."
End Sub
Private Sub Form_Close()
    Me.Filter = ""
    Me.FilterOn = False
End Sub
Private Sub Form_Open(Cancel As Integer)
    'Remove the single quote from these lines if you want to initially show no records.
    'Me.Filter = "(False)"
    'Me.FilterOn = True
End Sub

 

[GinaWhipp]

Yes, replace what I posted with what you posted (and already have).

 

[mayestom]

Wait, I'm confused. I don't understand what I'm supposed to do? I keep the code that I have already? The code now does not have a date filter on it.

 

[mayestom]

Hi Gina,
I’ve taken it upon myself to try and incorporate the code you supplied for the date filter into the existing code in the Event Procedure under the filter button, but it does not seem to work. The year filter seems to work but not the other date filters. Would you mind taking a look at it to see if I applied the date code correctly? I’ve attached a copy of the db. The form is called frmSupplierDescriptionCodeqry
Thank you.

 

[mayestom]

Hi Gina,
I hope everything is okay and that I didn't offend you in anyway.
Would you still be able to help me incorporate the code to add the date filters?

 

[GinaWhipp]

Not offended...grandchildren were visiting last week and have been playing catch up ever since. I will be looking at your database tomorrow...

 

[mayestom]

Glad to see you back.
I think I got the code correctly in there. Have been playing with it and it returns all the correct dates. But I'm still having a problem with the supplier name not coming through when I create a report off of a filtered search. On the form frmMultiSelectqry if you create a report off of the unfiltered results the Supplier name comes through. But if you filter the results in any way and create a report the autonumber comes through and not the name. Can you see if it does that for you? And if so, do you have any idea's as to how I can fix that?
Also, is there any way to export the results of the querries? Or at least to copy and paste the results into an Excel file?

 

[GinaWhipp]

I will be looking shortly... have a couple more things to get done and then I open your sample database!

 

[GinaWhipp]

Re: Post 114

Fixed Month/Year by adding Row Source. Add this to the Row Source...

SELECT Format([Purchase_Date],"m-yyyy") AS MyMonthYear FROM Equipment GROUP BY Format([Purchase_Date],"m-yyyy") HAVING (((Format([Purchase_Date],"m-yyyy"))<>" "));

And then re-upload your database as I have no reports in mine.

 

[mayestom]

I’m not quite following. I add this:

 SELECT Format([Purchase_Date],"m-yyyy") AS MyMonthYear FROM Equipment GROUP BY Format([Purchase_Date],"m-yyyy") HAVING (((Format([Purchase_Date],"m-yyyy"))<>" "));

to the Row Source of cboMonthYear?

I don’t have any reports, I was just creating one from a filtered search. Guess I’m not sure how that's supposed to work? Do you normally save a report? If so, won’t that be old data when you open up again?