Displaying Query results in a Combo/List Box.

[AOMCprog]

Hello,

I have a form where the user inputs a date range and that date range is the filter for a query. The query works fine, but here is the problem. On a form based on the query, I can display the query results in text boxes (one record at a time), but I cannot display the query results in either a combo or list box. I keep get the following error: No value given for one or more required parameters. What is the secret to displaying the query results in a combo/list box? Thanks in advance.

Don

 

[Kryst51]

Can you post your sql for the query here?

 

[AOMCprog]

Sure, here you go:

SELECT DISTINCTROW Orders.Orderer, Orders.[Order Type], Orders.[Order Date], Sum(Orders.[Orders Entered]) AS [Sum Of Orders Entered], Sum(Orders.OrdersVerified) AS [Sum Of OrdersVerified], IIf([Orders]![Order Type]="RPh ",[Orders]![Orders Entered],0) AS [Sum of RPh Entered], IIf([Orders]![Order Type]="Tech ",[Orders]![Orders Entered],0) AS [Sum of Tech Entered], "LINK" AS Expr1
FROM Orders
GROUP BY Orders.Orderer, Orders.[Order Type], Orders.[Order Date], IIf([Orders]![Order Type]="RPh ",[Orders]![Orders Entered],0), IIf([Orders]![Order Type]="Tech ",[Orders]![Orders Entered],0)
HAVING (((Orders.[Order Date]) Between [Forms]![CalendarForm]![BeginDate] And [Forms]![CalendarForm]![EndDate]));

The begin and end dates come from the CalendarForm.

Don

 

[Kryst51]

SELECT DISTINCTROW Orders.Orderer, Orders.[Order Type], Orders.[Order Date],
 Sum(Orders.[Orders Entered]) AS [Sum Of Orders Entered],
 Sum(Orders.OrdersVerified) AS [Sum Of OrdersVerified], 
IIf([Orders]![Order Type]="RPh ",[Orders]![Orders Entered],0) 
AS [Sum of RPh Entered], IIf([Orders]![Order Type]="Tech ",[Orders]![Orders Entered],0) 
AS [Sum of Tech Entered], "LINK" AS [COLOR=red][B][U]Expr1[/U][/B][/COLOR]
FROM Orders
GROUP BY Orders.Orderer, Orders.[Order Type], Orders.[Order Date],
 IIf([Orders]![Order Type]="RPh ",[Orders]![Orders Entered],0),
 IIf([Orders]![Order Type]="Tech ",[Orders]![Orders Entered],0)
HAVING (((Orders.[Order Date]) Between [Forms]![CalendarForm]![BeginDate] And [Forms]![CalendarForm]![EndDate]));

What is this red bolded expression? It might be causing your problem.

 

[AOMCprog]

I don't see any red bolded expression.

Don

 

[Kryst51]

Either refresh as I had to reformat the quote to fit on the avaiable screen or scroll over....

 

[AOMCprog]

Okay, now I see it. That is my attempt to link that query to another query. I'll remove that expression from the query and try again. Thanks.

Don

 

[Kryst51]

Don't just remove it, it says Expr1 because there is a problem... What is it supposed to say?

 

[Kryst51]

Ahh.... Google "joins" and that should help you, I did it quickly and here is a wikipedia site to start with.

 

[AOMCprog]

No, I called it Expr1.

Don

 

[Kryst51]

Ok then what is "Link"

 

[Kryst51]

And just in case you are trying to do a union query

 

[AOMCprog]

As I said, that was just an attemptto link this query with another query that had the same value of "Link" in it. I have removed that expression, but I still get the same error message. Here is the new SQL:

SELECT DISTINCTROW Orders.Orderer, Orders.[Order Type], Orders.[Order Date], Sum(Orders.[Orders Entered]) AS [Sum Of Orders Entered], Sum(Orders.OrdersVerified) AS [Sum Of OrdersVerified], IIf([Orders]![Order Type]="RPh ",[Orders]![Orders Entered],0) AS [Sum of RPh Entered], IIf([Orders]![Order Type]="Tech ",[Orders]![Orders Entered],0) AS [Sum of Tech Entered]
FROM Orders
GROUP BY Orders.Orderer, Orders.[Order Type], Orders.[Order Date], IIf([Orders]![Order Type]="RPh ",[Orders]![Orders Entered],0), IIf([Orders]![Order Type]="Tech ",[Orders]![Orders Entered],0)
HAVING (((Orders.[Order Date]) Between [Forms]![CalendarForm]![BeginDate] And [Forms]![CalendarForm]![EndDate]));

 

[Kryst51]

OK, This is way to complex for me to figure out by looking at SQL. I thought it might be the Expr1 thing because that is what Access uses when there is something that is not accounted for, like a field that doesn't exist in table or an expression that no longer works for some reason. I usually work by trying different things, so if it were me, and I was playing around trying to get it to work, I would try running the query withough all the sums and If/Thens first. Does it work then? Could this be a problem of needing a subquery first? I wish I could be of more help, but alas I can't I hope someone else will jump in for you and sorry for not completely understanding.