Query not combining and filtering values based on a form

[GinaWhipp]

Hmm, let's try...

If Not IsNull(Me.cboSupplier.Column(1)) Then
     strWhere = strWhere & "([Supplier_Name] = """ & Me.cboSupplier.Column(1) & """) AND "
End If

 

[mayestom]

That did it! It works fine, can filter on Supplier or Equipment or both or neither. And the Supplier name even comes through on a Report! I still need to figure out why the name does not show up on a filtered Report on the other form (frmMultiSelectqry). I changed the Column Count to 1 and the Column Widths to 0";1" like you said but that didn't do it. Only the Autonumber shows. Any thoughts on this?

So now I'll also change all the Enabled's and Locked Property's for all the objects in the results so they can look but not touch right?

Also, when I close the form it asks me to save a Module: Module1. What should I save that as?

 

[GinaWhipp]

Glad that finally worked... was getting a little confused myself. Yes, do set the properties for the fields.

No for Report, the Column Count has to be 2, did I post that wrong? sorry!

Where did the save some from, say no because there should be no Module.

 

[mayestom]

Saved the form but no to Module.
Changed the column count to 2 but still no name.

 

[GinaWhipp]

Got distracted, neighbor dropped by! Please post the SQL that is the Record Source for the Report.

 

[mayestom]

Here is the SQL for the Record Source of the Report:

SELECT Equipment_Spec.Picture, Equipment.Unit_Cost, Equipment.Quantity_Purchase, [Unit_Cost]*[Quantity_Purchase] AS Total, Equipment.Project_Name, Equipment.Supplier_Name, Equipment.[Location/Affiliate_Name], Equipment_Spec.Description
FROM Equipment INNER JOIN Equipment_Spec ON Equipment.Equipment_ID = Equipment_Spec.ID;

 

[GinaWhipp]

Okay, on your Report where the Supplier Name is supposed to show, remove that Control then on the Ribbon where is says Add Existing Fields, click that. Then drop the one that says Supplier_Name to the place where you removed the other one.

 

[mayestom]

That worked! Deleted Supplier Name and added Supplier Name in it's place. Which doesn't make sense to me? I'm deleting Supplier Name and adding Supplier Name They're both the same thing. Why would one work and not the other?
But also I or a user would need to do that every time they would run a Report. And I have a feeling most of the users are not going to know enough or should know enough to do something like that. Is there permanent fix to the problem?

In frmSupplierDescriptionCodeqry why did it start to work when we changed this:

If Not IsNull(Me.cboSupplier) Then
     strWhere = strWhere & "([Supplier_Name] = """ & Me.cboSupplier & """) AND "
End If

to this:

If Not IsNull(Me.cboSupplier.Column(1)) Then
     strWhere = strWhere & "([Supplier_Name] = """ & Me.cboSupplier.Column(1) & """) AND "
End If

So when I need to make another form like this I would: Have the Record Source point to the new query and would need to change the values in the Set Filter section of the Event Procedure?

 

[GinaWhipp]

What you did to the report was a permanent fix. The reason I beleive it worked is because the one that was there was a Combo Box (which should never go on a Report) and the one I had you replace it with was a Text Box (which should go on a Report).

frmSpplierDescriptionCodeqry - The first one did not work because it was pointing to the first column in the Combo Box which, in this case, the first column is the SupplierID and not the SoupplierName. In the second variation I told it to look at the second column of the Combo Box which is the SupplierName.

Now, I know you are looking at Me.cboSupplier.Column(1) and thinking that is column 1. Well, no, because Combo Boxes like List Boxes actually start at 0 (zero)... I know a little confusing So when you want to refer to columns in List Boxes and Combo Boxes you always have to minus 1.

So, when you want to make anither Form, yes you will need all the buttons. Best way copy one of the other Forms and then you don't have to copy/paste the code, then make your changes and if you want different filters let me know AND so you can learn what I'm doing I can talk you thru the change if you like

 

[mayestom]

Gina I don't know how to thank you. I don't know what I would have done if you didn't help me with this. You are amazing! Thank you for everything you have done for me. For all your time and effort and answering my basic, and sometimes stupid questions. I really appreciate it. I learned a lot and I'm going to start going through all the links you have provided. I hope I will be able to get in touch with you again through this site.

Thank you.

 

[GinaWhipp]

You're most welcome and thank you for the kind words!

I'll be around and I will forever get notifications from this thread so feel free to post a *shout out* whenever you want. But don't sell this site short there are a lot of great people on this site that will be happy to help should you post a new question.

Good luck going forward with your project!

 

[mayestom]

I'm sorry to be asking this at this time but would you be able to walk me through adding another filter to frmSupplierDescriptionCodeqry. It's a date field and I've already added the field to the table and adding the field to the query and to the subform, I would just need to add the filter to the code. If you could I would appreciate it.

 

[GinaWhipp]

No problem... Dates a little different though so... Do you want...

1. Between or equal to StartDate and EndDate (no worries if you only have one date field in table)
2. Only Date entered to search on
3. All of the above

 

[mayestom]

Not sure. Mainly concerned with the month and year equipment was purchased and associated with a project. But that piece of equipment will be purchased maybe several times over the span of a project. So it's purchased once this year and next and the year after and would like to see the difference between the years. So it will come up several times when filtered by project but the time between the purchase could be a year or two or... so filtering by a month and year would be needed. I'm thinking maybe not on date only (cuz you would need to be specific with the day, month and year correct?). Or maybe that would be ok as well? Would it be terribly hard to have the option to filter on either way?

 

[GinaWhipp]

Well, we could do...

Year
Month/Year
From Date - To Date

Would that work?

 

[mayestom]

Yes, that would be great! I think that would cover everything.

 

[GinaWhipp]

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.

 

[mayestom]

Wait, I'm sorry, I think I didn't explain properly, the date filter is going on the form that you helped me create, frmSupplierDescriptionCodeqry. Which has two filters on it already.

So in the query I'm bring in the table with the date field and i'm adding that to the design grid then I'm formatting it twice, once to show year the other to show month/year? And if so, how do I format them in a query?

 

[GinaWhipp]

To get the date to show its year...

MyYear: Year([YourDateField])

To get the date to show Month/Year

MyMonthYear: Format([YourDateField]."m-yyyy")

Copy/paste the above on the Field line.

 

[mayestom]

So I'm creating a whole new query. But it will be used in the existing form. Correct?

This code is getting error messages:

MyMonthYear: Format([Purchase_Date]."m-yyyy")