Filter Results based on input?

[A|ex]

Atm i have a Query which will display all values in a table within a given date range. I was wondering instead of displaying all values how do i filter the results to ONLY show the ones i want them to show.

For example: Filter them by Rep called bill Jones? (user inputts whatever bill jones was an example) i dont want to do = 'bill jones'

SELECT DISTINCTROW Customers!FirstName & ' ' & Customers!LastName AS Expr1, Sales.ContractNumber, Sales.EmployeeID, Sales.DateSold, Sales.DiscountGLPNett, Sales.SurveyNett, Sales.CashCommission, Sales.FinanceCommission, [CashCommission]+[FinanceCommission] AS [Total Commission], [Start Date] AS [Start Date=Expy1], [End Date] AS [End Date=Expy2]
FROM Customers INNER JOIN Sales ON Customers.CustomerID = Sales.CustomerID
GROUP BY Customers!FirstName & ' ' & Customers!LastName, Sales.ContractNumber, Sales.EmployeeID, Sales.DateSold, Sales.DiscountGLPNett, Sales.SurveyNett, Sales.CashCommission, Sales.FinanceCommission, [CashCommission]+[FinanceCommission], [Start Date], [End Date], Sales.CustomerID
HAVING (((Sales.DateSold) Between [Start Date] And [End Date]));

 

[Len Boorman]

You could include a criteria in the query

[Please Enter Rep Name] which will prompt the user for an entry but will rely on the User correctly typing in the rep name (Huh) or you could use a form and have the User select from a combo that had the rep names, That way the suer selects (No Typing) and will get correct spelling of name

Len B

 

[A|ex]

I tried the combo box idea but the query doesnt pick up the value of the combo box. Same goes for start date and end date. is it possible to provide a database example?

 

[EMP]

............. is it possible to provide a database example?

Just post your database with the two tables with some sample data and the form. (Compact the database from the Tools menu before zipping and posting.)

And tell us exactly what you wanted the query to do. You used a Totals query (i.e. Group By) but you used none of the aggregate functions such as Sum(), Avg() etc.

 

[Len Boorman]

The syntax to pick up a combo box (or anything else) from a form is

[Forms]![frm_Ph_2_Add_Task_03]![cbo_Action_Ref]

You can see that naming is important

For dates you could use something like

Between [Forms]![frm_Ph_2_Add_Task_03]![txt_Date_1] and [Forms]![frm_Ph_2_Add_Task_03]![txt_Date_2]

Len B

 

[A|ex]

i will create you a sample table. But atm my two tables are NOT using a relationship, i'm using a query to lookup information, could that be a problem?

 

[A|ex]

here is the test i just want it to create a query based on employee name in the SALES table. You see atm im using full name

 

[EMP]

I have added a form and two queries in the database.

You can open the form, select a Rep, enter a start date and an end date,
and click on the two buttons to run the queries.