Solved Open a filtered query from a Cmd Button

[asteropi]

Hey I have a button that opens a query with a filter.

It's supposed to show me the customer discounts. I have the following conundrum.

I could possibly have multiple orders of the same item with the same discount. I don't want to see all of them.
However, this discount might change at some point for that specific item.
How should I deal with this?

In this example, you can see that 1 customer made 3 orders and 2 of them she got the exact same thing, with the exact same discount. I would rather avoid seeing the same thing over and over as I only need it for reference.

 

[asteropi]

I figured out I need a SELECT DISTINCT command to get the unique values, but I can't make it work. I keep getting an error saying "Case" or Syntax Error

Also, I need it to use a combination of rules, so to select SKU+Discount Rate. What is the AND rule for this command? because I tried AND and it didn't work

 

[theDBguy]

I figured out I need a SELECT DISTINCT command to get the unique values, but I can't make it work. I keep getting an error saying "Case" or Syntax Error

Also, I need it to use a combination of rules, so to select SKU+Discount Rate. What is the AND rule for this command? because I tried AND and it didn't work

View attachment 115990

Have you tried adding the DISTINCT part in the design view of query itself?

 

[asteropi]

Have you tried adding the DISTINCT part in the design view of query itself?

I tried adding it in the criteria but I can't figure it out

 

[GaP42]

To use the DISTINCT keyword on multiple columns simply list the column names separated by commas after the DISTINCT keyword.
cf your use of AND

 

[Gasman]

Create you query as you had in post one.
Then go into SQl view and insert DISTINCT after the Select.

NB: DISTINCT works on all fields.

 

[asteropi]

Create you query as you had in post one.
Then go into SQl view and insert DISTINCT after the Select.

NB: DISTINCT works on all fields.

I still get the same problem. It won't even let me finish the line

 

[cheekybuddha]

You can't just type your SQL freely in the VBA editor.

Are you trying to build an SQL string in VBA?

If so, you need to declare a variable to assign it to, and use quotes to show you are assigning a string. eg

' ...
  Dim strSQL As String
 
  strSQL = "SELECT DISTINCT SKU, DiscountPercent ..."
' ...

 

[arnelgp]

you create new query using the Given syntax.

 

[asteropi]

You can't just type your SQL freely in the VBA editor.

Are you trying to build an SQL string in VBA?

If so, you need to declare a variable to assign it to, and use quotes to show you are assigning a string. eg

' ...
  Dim strSQL As String
 
  strSQL = "SELECT DISTINCT SKU, DiscountPercent ..."
' ...

I copied this as you wrote it but it didn't do anything

you create new query using the Given syntax.

I'm afraid I will need more explanation. I don't know this command

 

[cheekybuddha]

I copied this as you wrote it but it didn't do anything

Well, you then have to do something with the string variable.

I'm not sure what it is you want to do. I was just trying to explain why you had red error in the VBA editor.

I think you need to dial back a bit first.

What is the SQL of your query CustomerDiscountQ ?

 

[MarkK]

SQL is a language. VBA is a different language. You can't execute SQL in VBA. The specific error you are getting is that VBA is expecting a Select Case statement, because VBA knows nothing about SQL syntax.

You probably need to open your query named CustomerDiscountQ in design view, and edit the SQL in that object to include the DISTINCT keyword. A QueryDef is a context in which you can execute SQL, and then in VBA you execute the QueryDef, and the QueryDef executes the SQL.

 

[asteropi]

I'm a long way away from all that
I don't even know how to edit sql or even where to look for it

 

[cheekybuddha]

I don't even know how to edit sql or even where to look for it

Right click your query in the object window on the left of Access

Choose 'Open in SQL view'

Copy and paste the SQL here

 

[Gasman]

https://m.youtube.com/results?sp=mAEA&search_query=Access+query+vba

 

[CarlettoFed]

Attach the file, maybe it will be quicker.

 

[Gasman]

Attach the file, maybe it will be quicker.

More than likely, but then they will not learn anything?

 

[asteropi]

Attach the file, maybe it will be quicker.

 

[asteropi]

More than likely, but then they will not learn anything?

you are wrong, I always study what you tell me

 

[asteropi]

SELECT ProductDetailsT.ProductDetailID, OrdersT.CustomerID, ProductDetailsT.SKU, ProductDetailsT.ProductName, OrderDetailsT.DiscountPercent
FROM ProductDetailsT INNER JOIN (OrdersT INNER JOIN OrderDetailsT ON OrdersT.OrderID = OrderDetailsT.OrderID) ON ProductDetailsT.ProductDetailID = OrderDetailsT.ProductDetailsID;

Right click your query in the object window on the left of Access

Choose 'Open in SQL view'

Copy and paste the SQL here