Open a query with a filter - Microsoft Access Forum


	Back to Access Forum Comments List

Upload Images @Reply Bookmark Link Email Next Unseen

Open a query with a filter

Asteropi Papadopoulou

2 years ago

I need to have a button to open a query (I did that) but I need it to automatically apply a filter on customer ID. How can I do that?

Also, every customer has many same records (orders-discounts). I need it to show me just one time each based on product/discount number. I've run out of ideas on how to apply this.

	Asteropi Papadopoulou OP @Reply 2 years ago

	Asteropi Papadopoulou OP @Reply 2 years ago
For example this customer doesn't have any discounted order, and yet I see all the other customers too

	Kevin Robertson @Reply 2 years ago
Query Criteria

	Asteropi Papadopoulou OP @Reply 2 years ago
I managed to apply a filter to get what I want in the VBA. However now I'm trying to get the unique values. I tried SELECT DISTINCT SKU AND DiscountPercent from CustomerDiscountQ but it keeps coming back with an error

	Asteropi Papadopoulou OP @Reply 2 years ago

	Kevin Robertson @Reply 2 years ago
Just add to the where condition of your filter. For example: `DoCmd.OpenQuery "OrderListQ" DoCmd.ApplyFilter , "CustomerID=" & CustomerID & " And IsPaid=True"`

	Asteropi Papadopoulou OP @Reply 2 years ago
but each customer has many products with discounts and multiple orders. Sometimes the SKU is the same across various orders, sometimes the discount is the same I need a condition to choose all the unique combinations SKU+discount A where condition will only give me one SKU, and that's not what I want

	Kevin Yip @Reply 2 years ago
It's better to use a subform rather than a query in this situation. A subform (or form) has a Filter property, which lets you change filter "dynamically," which seems like what you need. A query can only change its filter by having its entire SQL statement changed.

	Asteropi Papadopoulou OP @Reply 2 years ago
I just selected the unique values property and it worked. But thank you for the input

This thread is now CLOSED. If you wish to comment, start a NEW discussion in Access Forum.

Next Unseen

New Feature: Comment Live View